|
S t2 on t1.TABLE_ID=T2.TABLE_ID
where t1.NAME=@db_table_name and t2.NAME=index_name;
set index_exists_action=index_exists;
IF index_action = 'add' THEN
-- 添加索引
IF index_exists < 1 THEN
SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action;
ELSE
SELECT 'Index already exists.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
END IF;
ELSEIF index_action = 'modify' THEN
-- 修改索引（先删除后添加）
IF index_exists > 0 THEN
SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
SELECT 'Index modified successfully.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
ELSE
SELECT 'Index does not exist. create' AS result,database_name,index_exists,@db_table_name,index_exists_action;
SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` ADD INDEX `', index_name, '` (', index_columns, ')');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
SELECT 'Index added successfully.' AS result ,database_name,index_exists,@db_table_name,index_exists_action;
END IF;
ELSEIF index_action = 'delete' THEN
-- 删除索引
IF index_exists > 0 THEN
SET @query = CONCAT('ALTER TABLE `', database_name, '`.`', table_name, '` DROP INDEX `', index_name, '`');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
select count(t2.INDEX_ID) INTO index_exists_action from information_schema.INNODB_TABLES t1 left join information_schema.INNODB_INDEXES t2 on t1.TABLE_ID=T2.TABLE_ID where t1.NAME=@db_table_name and t2.NAME=index_name;
SELECT 'Index deleted successfully.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
ELSE
SELECT 'Index does not exist.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
END IF;
ELSE
SELECT 'Invalid index action.' AS result,database_name,index_exists,@db_table_name,index_exists_action;
END IF;
END //
DELIMITER ;
测试脚本
create table if not exists sys_agent
(
agent_id bigint not null comment '客服唯一id'
primary key,
agent_name varchar(64) null comment '客服名称',
agent_type varchar(30) null comment '客服类型(场地客服、直聘客
|