很多公司都要求再生产上打得sql脚本允许反复执行(防止某一个sql报错以后要拎出来执行)。
所以就产生了需要先判断索引是否存在,再做添加索引或者删除索引的操作(若索引不存在,删除索引会报错)
这时候单独sql已经无法完成这个要求了(添加和删除索引的语句不支持IF EXISTS)
所以需要存储过程出马,这么简单的存储过程很多人都会写,但是这里我踩了一个坑。。。
没错,就是表名不能当参数。。。
第一次写的存储过程,不幸的挂了。
DROP PROCEDURE IF EXISTS add_index; DELIMITER //CREATE PROCEDURE add_index(IN p_db_name VARCHAR (64),IN p_table_name VARCHAR (64),IN p_index_name VARCHAR (64),IN p_index VARCHAR (64)) BEGIN IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = p_db_name AND table_name = p_table_name AND index_name = p_index_name) THEN ALTER TABLE p_table_name ADD INDEX p_index_name ( p_index );END IF; END// DELIMITER ;
这里 ALTER TABLE p_table_name ADD INDEX p_index_name ( p_index ); 这句话是错误的,因为存储过程里表名无法直接作为参数使用。
还好,还可以使用MySQL提供的PREPARE语法
关于PREPARE的具体使用方法,可以参考网址:/doc/refman/5.6/en/sql-syntax-prepared-statements.html
于是修改存储过程为
DROP PROCEDURE IF EXISTS add_index; DELIMITER //CREATE PROCEDURE add_index(IN p_db_name VARCHAR(64),IN p_table_name VARCHAR(64),IN p_index_name VARCHAR(64),IN p_index VARCHAR(64)) BEGIN DECLARE StrAdd VARCHAR(250);DECLARE StrDrop VARCHAR(250);set @StrAdd=concat('ALTER TABLE ',p_table_name,' ADD INDEX ',p_index_name,'(',p_index,')');set @StrDrop=concat(' drop index ',p_index_name,' on ',p_table_name); IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema = p_db_name AND table_name = p_table_name AND index_name = p_index_name) THEN PREPARE stmt FROM @StrAdd; EXECUTE stmt ; ELSEPREPARE stmt FROM @StrDrop; EXECUTE stmt ; PREPARE stmt FROM @StrAdd; EXECUTE stmt ; END IF; END// DELIMITER ;
参数(p_db_name 库名,p_table_name 表名,p_index_name 索引名,p_index 索引字段)
set @StrDrop=concat(’ drop index ‘,p_index_name,’ on ',p_table_name);
这句是拼接一个sql语句,然后 PREPARE stmt FROM @StrDrop; 执行该sql ,最后通过DEALLOCATE释放该Prepared Statements
该过程可以实现先判断有无该索引,有则删除然后新增,没有则直接新增。