Question

求教mysql中if exists 的用法

Answer 1

mysql没有drop index index_name if exists on table_name这样的语法，所以写一个存储过程来判断，如果存在就删除后再创建新的索引。存储过程如下：
--
-- procedure of delete index
--
drop procedure if exists Del_idx;
DELIMITER $$
CREATE PROCEDURE Del_idx(IN p_tablename varchar(200), IN p_idxname VARCHAR(200))
begin
DECLARE str VARCHAR(250);
SET @str=concat(' drop index ',p_idxname,' on ',p_tablename);
SELECT COUNT(*) INTO @cnt FROM information_schema.statistics WHERE TABLE_NAME=p_tablename AND INDEX_NAME=p_idxname;
if @cnt >0 then
PREPARE stmt FROM @str;
EXECUTE stmt ;
end if;
end $$
DELIMITER ;
使用时传入表名和索引名即可，如CALL Del_idx('tableA', 'indexA');