存储过程--添加索引、删除索引

 

/*存在则删除存储过程*/
drop procedure if EXISTS add_index_while_procure;
/*查看存储过程*/
show procedure status;
show create procedure add_index_while_procure;

 

delimiter $$
/*创建带参存储过程 dbName库名称；tablePrefixName表名称前缀；minTableNumber表最小序号；maxTableNumber表最大序号;indexName索引名称；indexValue索引列*/
create PROCEDURE add_index_while_procure(IN dbPrefixName VARCHAR(50),IN tablePrefixName VARCHAR(50),IN minTableNumber INT,IN maxTableNumber INT,IN indexName varchar(50),IN indexValue varchar(200))
BEGIN
declare table_name_number int;

set table_name_number=minTableNumber;
while(table_name_number<=maxTableNumber) DO 
/*添加索引语句*/ 
set @STMT :=CONCAT("alter table ",dbPrefixName,".",tablePrefixName,table_name_number," ADD index " ,indexName," ",indexValue,";");
/*输出执行语句*/ 
select @STMT;
prepare STMT FROM @STMT;
EXECUTE STMT; 

set table_name_number=table_name_number+1;
end while;
END 
$$
delimiter;

 

/*调用存储过程 所有子表及主表
ALTER TABLE manager.tableName ADD INDEX idx_station_time (STATION_CODE, TIME) USING BTREE ;
*/
call add_index_while_procure("manager","tableName_",1906,1912,"idx_code_time","(CODE, TIME)");

---------------------------

delimiter $$
/*创建带参存储过程 dbName库名称；tablePrefixName表名称前缀；minTableNumber表最小序号；maxTableNumber表最大序号;indexName索引名称；indexValue索引列*/
create PROCEDURE drop_index_while_procure(IN dbPrefixName VARCHAR(50),IN tablePrefixName VARCHAR(50),IN minTableNumber INT,IN maxTableNumber INT,IN indexName varchar(50))
BEGIN
declare table_name_number int;

set table_name_number=minTableNumber;
while(table_name_number<=maxTableNumber) DO 
/*添加索引语句*/ 
set @STMT :=CONCAT("alter table ",dbPrefixName,".",tablePrefixName,table_name_number," DROP index " ,indexName,";");
/*输出执行语句*/ 
select @STMT;
prepare STMT FROM @STMT;
EXECUTE STMT; 

set table_name_number=table_name_number+1;
end while;
END 
$$
delimiter;

 

/*调用存储过程 所有子表及主表
ALTER TABLE manager.tableName DROP INDEX idx_station_time;
*/
call drop_index_while_procure("manager","tableName_",1906,1912,"idx_station_time");

 

 参考：

https://blog.csdn.net/qq_23132561/article/details/100888454