DROP PROCEDURE IF EXISTS add_drop_index;
DELIMITER //
CREATE PROCEDURE add_drop_index()
BEGIN
DECLARE CurrentDatabase VARCHAR(100);
SELECT DATABASE() INTO CurrentDatabase;
-- 添加索引前,判断该索引是否存在,避免执行时报错
IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = '表名1' AND index_name = '索引1') THEN
ALTER TABLE 表名1 ADD INDEX 索引1 (索引字段);
END IF;
-- 删除索引前,判断该索引是否存在,避免执行时报错
IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema=CurrentDatabase AND table_name = '表名1' AND index_name = '索引2') THEN
ALTER TABLE 表名1 DROP INDEX 索引2;
END IF;
END
//
DELIMITER ;
CALL add_drop_index();
DROP PROCEDURE IF EXISTS add_drop_index;