遇到的问题:分库分表情况下可能好几百张表,可以使用存储过程来新建索引。实现方式如下
/*存在则删除存储过程*/
drop procedure if EXISTS add_index_while_procure;
/*新建存储过程:给库.表的字段添加索引*/
delimiter $$
/*创建带参存储过程 dbPrefixName库名称前缀;minBdNumber库最小序号;maxBdNumber库最大序号;tablePrefixName表名称前缀;minTableNumber表最小序号;maxTableNumber表最大序号;indexName索引名称;indexValue索引列*/
create PROCEDURE add_index_while_procure(IN dbPrefixName VARCHAR(50),IN minBdNumber INT,IN maxBdNumber INT,IN tablePrefixName VARCHAR(50),IN minTableNumber INT,IN maxTableNumber INT,IN indexName varchar(50),IN indexValue varchar(200))
BEGIN
declare db_name_number,table_name_number int;
set db_name_number=minBdNumber;
while(db_name_number<=maxBdNumber) DO
set table_name_number=minTableNumber;
while(table_name_number<=maxTableNumber) DO
/*添加索引语句*/
set @STMT :=CONCAT("alter table ",dbPrefixName,db_name_number,".",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;
set db_name_number=db_name_number+1;
end while;
END
$$
delimiter;
/*调用存储过程*/
call add_index_while_procure("dbName_",0,2,"tableName_",0,99,"indexName","(`column1`)");