mysql 判断索引是否存在，存在则删除再创建索引（分表） 存储过程
1.分表5数据量大，执行所有分表修改，不包括5
CREATE PROCEDURE deleteIndex()
BEGIN
DECLARE corpId CHAR (16);
DECLARE flag INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM gpsbuzdb.gps_corp where id <> '5';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO corpId;
IF flag THEN
LEAVE read_loop;
END IF;
BEGIN
SET @str = concat('DROP INDEX index_1 ON gps_eqpt_alarm_',corpId);
SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE TABLE_NAME=CONCAT('gps_eqpt_alarm_', corpId) AND INDEX_NAME='index_1';
IF @cnt > 0 THEN
PREPARE stmt FROM @str;
EXECUTE stmt;
END IF;
END;
BEGIN
SET @sqlNew = CONCAT('ALTER TABLE ', CONCAT('gps_eqpt_alarm','_',corpId), ' ADD INDEX `index_alarmType` (`alarm_type`) USING BTREE ;');
PREPARE astmt FROM @sqlNew;
EXECUTE astmt;
END;
BEGIN
SET @sqlNew2 = CONCAT('ALTER TABLE ', CONCAT('gps_eqpt_alarm','_',corpId), ' ADD INDEX `index_alarmTime` (`alarm_time`) USING BTREE ;');
PREPARE bstmt FROM @sqlNew2;
EXECUTE bstmt;
END;
BEGIN
SET @sqlNew3 = CONCAT('ALTER TABLE ', CONCAT('gps_eqpt_alarm','_',corpId), ' ADD INDEX `index_groupId` (`group_id`) USING BTREE ;');
PREPARE cstmt FROM @sqlNew3;
EXECUTE cstmt;
END;
END LOOP;
CLOSE cur;
commit;
END;
CALL deleteIndex();
2.单独执行分表5
CREATE PROCEDURE deleteIndex5()
BEGIN
DECLARE corpId CHAR (16);
DECLARE flag INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT id FROM gpsbuzdb.gps_corp where id = '5';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO corpId;
IF flag THEN
LEAVE read_loop;
END IF;
BEGIN
SET @str = concat('DROP INDEX index_1 ON gps_eqpt_alarm_',corpId);
SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE TABLE_NAME=CONCAT('gps_eqpt_alarm_', corpId) AND INDEX_NAME='index_1';
IF @cnt > 0 THEN
PREPARE stmt FROM @str;
EXECUTE stmt;
END IF;
END;
BEGIN
SET @sqlNew = CONCAT('ALTER TABLE ', CONCAT('gps_eqpt_alarm','_',corpId), ' ADD INDEX `index_alarmType` (`alarm_type`) USING BTREE ;');
PREPARE astmt FROM @sqlNew;
EXECUTE astmt;
END;
BEGIN
SET @sqlNew2 = CONCAT('ALTER TABLE ', CONCAT('gps_eqpt_alarm','_',corpId), ' ADD INDEX `index_alarmTime` (`alarm_time`) USING BTREE ;');
PREPARE bstmt FROM @sqlNew2;
EXECUTE bstmt;
END;
BEGIN
SET @sqlNew3 = CONCAT('ALTER TABLE ', CONCAT('gps_eqpt_alarm','_',corpId), ' ADD INDEX `index_groupId` (`group_id`) USING BTREE ;');
PREPARE cstmt FROM @sqlNew3;
EXECUTE cstmt;
END;
END LOOP;
CLOSE cur;
commit;
END;
CALL deleteIndex5();
3.修改总表
CREATE PROCEDURE del_idxByTableAll()
BEGIN
BEGIN
SET @str = 'DROP INDEX index_1 ON gps_eqpt_alarm';
SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE TABLE_NAME='gps_eqpt_alarm' AND INDEX_NAME='idx1';
IF @cnt > 0 THEN
PREPARE stmt FROM @str;
EXECUTE stmt;
END IF;
END;
BEGIN
SET @sqlNew='ALTER TABLE gps_eqpt_alarm ADD INDEX `index_alarmType` (`alarm_type`) USING BTREE';
PREPARE astmt FROM @sqlNew;
EXECUTE astmt;
END;
BEGIN
SET @sqlNew2='ALTER TABLE gps_eqpt_alarm ADD INDEX `index_alarmTime` (`alarm_time`) USING BTREE';
PREPARE bstmt FROM @sqlNew2;
EXECUTE bstmt;
END;
BEGIN
SET @sqlNew3='ALTER TABLE gps_eqpt_alarm ADD INDEX `index_groupId` (`group_id`) USING BTREE';
PREPARE cstmt FROM @sqlNew3;
EXECUTE cstmt;
END;
END;
CALL del_idxByTableAll();
4.修改模板表
CREATE PROCEDURE del_idxByTemplateTable()
BEGIN
BEGIN
SET @str = 'DROP INDEX index_1 ON gps_eqpt_alarm_template';
SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE TABLE_NAME='gps_eqpt_alarm_template' AND INDEX_NAME='index_1';
IF @cnt > 0 THEN
PREPARE stmt FROM @str;
EXECUTE stmt;
END IF;
END;
BEGIN
SET @sqlNew='ALTER TABLE gps_eqpt_alarm_template ADD INDEX `index_alarmType` (`alarm_type`) USING BTREE';
PREPARE astmt FROM @sqlNew;
EXECUTE astmt;
END;
BEGIN
SET @sqlNew2='ALTER TABLE gps_eqpt_alarm_template ADD INDEX `index_alarmTime` (`alarm_time`) USING BTREE';
PREPARE bstmt FROM @sqlNew2;
EXECUTE bstmt;
END;
BEGIN
SET @sqlNew3='ALTER TABLE gps_eqpt_alarm_template ADD INDEX `index_groupId` (`group_id`) USING BTREE';
PREPARE cstmt FROM @sqlNew3;
EXECUTE cstmt;
END;
END;
CALL del_idxByTemplateTable();
