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;
查看mysql索引
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();

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。