Mysql添加索引（存储过程，可以重复执行）

最新推荐文章于 2024-12-29 16:16:31 发布

原创最新推荐文章于 2024-12-29 16:16:31 发布 · 2.6k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #存储过程 #索引

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了在MySQL中如何创建存储过程来处理索引的添加和删除。由于SQL语句不支持直接在IF EXISTS条件下操作索引，因此需要借助存储过程和PREPARE语法。存储过程通过接收表名和索引名作为参数，动态拼接SQL语句，实现检查索引存在性并进行相应操作的功能。遇到的问题是表名不能直接作为存储过程的参数，但通过PREPARE可以解决这个问题。

很多公司都要求再生产上打得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的具体使用方法，可以参考网址：https://dev.mysql.com/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 ;  
ELSE
    PREPARE 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
该过程可以实现先判断有无该索引，有则删除然后新增，没有则直接新增。