mysql给表字段增加索引,使用存储过程进行,达到可以重复执行索引添加的效果
背景
为运维提供sql文档,运维要求sql内容,可以cv并且能重复执行。本次执行的sql内容有给字段增加索引,查询过mysql并不能直接判断,并执行类似的sql: CREATE INDEX … IF NOT EXISTS 语法是不可行的,因为 MySQL 不支持这种语法。找到一个可以使用存储过程来重复执行的方案,因此做个记录。
执行过程
原始sql:
ALTER TABLE t_my_table ADD INDEX idx_data_department (data_department)
修改后sql:
DROP PROCEDURE IF EXISTS addIndexDataDepartment;
CREATE PROCEDURE `addIndexDataDepartment`( )
BEGIN
DECLARE index_exists INT DEFAULT 0;
-- Check if the index already exists
SELECT COUNT(1) INTO index_exists
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = DATABASE()
AND table_name = 't_my_table'
AND index_name = 'idx_data_department';
-- If the index does not exist, create it
IF index_exists = 0 THEN
SET @sql = 'ALTER TABLE t_my_table ADD INDEX idx_data_department (data_department)';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;
CALL addIndexDataDepartment();
收工~~~
当然,如果有的限制了不能使用存储过程,还有方法:
也是在sql中直接执行:
注意,这个sql要放到最后面,因为会阻断后续的执行。
这个要写的时候一定要提前验证一下。
SET @indexExists = (
SELECT COUNT(*)
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = '数据库名'
AND TABLE_NAME = 't_my_table'
AND COLUMN_NAME = '(top,ordinal,time_published)'
AND INDEX_NAME = 'idx_h5_default_sort'
);
SET @sql = IF(
@indexExists = 0,
'CREATE INDEX idx_h5_default_sort ON t_my_table(top,ordinal,time_published);',
'SELECT "Index already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
再次收工~~~