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();

转载于:https://www.cnblogs.com/yzw23333/p/8534380.html