mysql索引是否存在判断

很多公司都要求再生产上打得sql脚本允许反复执行（防止某一个sql报错以后要拎出来执行）。

所以就产生了需要先判断索引是否存在，再做添加索引或者删除索引的操作（若索引不存在，添加或删除索引会报错）。实例如下：

drop PROCEDURE if EXISTS add_index;

DELIMITER //

create PROCEDURE add_index()

BEGIN

IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema='Prod.Oms.OmsToSgGateway' AND table_name = 'Oms.OmsToSgGateway.IntermeDiate' AND index_name = 'index_GW_Query') then

ALTER TABLE `Prod.Oms.OmsToSgGateway`.`Oms.OmsToSgGateway.IntermeDiate` ADD INDEX `index_GW_Query`(`ResourceName`, `Category`, `ResourceType`) USING BTREE COMMENT '增加国网数据检索效率';

END IF;

IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema='Prod.Oms.OmsToSgGateway' AND table_name = 'Oms.OmsToSgGateway.IntermeDiate' AND index_name = 'index_ResourceId') then

ALTER TABLE `Prod.Oms.OmsToSgGateway`.`Oms.OmsToSgGateway.IntermeDiate` ADD INDEX `index_ResourceId`(`ResourceId`) USING BTREE COMMENT '源始id';

END IF;

END

//

DELIMITER ;

call add_index();