mysql判断是否存在索引并删除_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();