添加/删除 MySQL索引存储过程

添加/删除 MySQL索引存储过程

• 创建索引时引用该存储过程，先检查索引是否存在，如果不存在再创建索引
• 创建存储过程代码

-- 创建添加索引的存储过程 
-- t 表名；i 索引名称；v 创建索引执行的sql ddl语句
DROP PROCEDURE IF EXISTS add_index;
DELIMITER $
CREATE PROCEDURE add_index(IN t VARCHAR(100),IN i VARCHAR(100),IN v VARCHAR(255))
BEGIN
DECLARE  target_database VARCHAR(100);
DECLARE  target_table_name VARCHAR(100);
DECLARE  target_column_name VARCHAR(100);
DECLARE  target_index_name VARCHAR(100);
set target_table_name = t;
set target_index_name = i;
SELECT DATABASE() INTO target_database;
IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = target_database AND table_name = target_table_name AND index_name = target_index_name) THEN
    set @statement = v;
    PREPARE STMT FROM @statement;
    EXECUTE STMT;
END IF;
END;
$ 
DELIMITER ;



-- 创建删除索引的存储过程 
DROP PROCEDURE IF EXISTS del_index;
DELIMITER $
CREATE PROCEDURE del_index(IN t VARCHAR(100),IN i VARCHAR(100),IN v VARCHAR(255))
BEGIN
DECLARE  target_database VARCHAR(100);
DECLARE  target_table_name VARCHAR(100);
DECLARE  target_column_name VARCHAR(100);
DECLARE  target_index_name VARCHAR(100);
set target_table_name = t;
set target_index_name = i;
SELECT DATABASE() INTO target_database;
IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema = target_database AND table_name = target_table_name AND index_name = target_index_name) THEN
    set @statement = v;
    PREPARE STMT FROM @statement;
    EXECUTE STMT;
END IF;
END;
$ 
DELIMITER ;

• 调用，sql脚本中调用即可，主意参数对应

-- 创建主键唯一索引
call add_index('tbl_config_country_specification','PRIMARY',"ALTER TABLE tbl_config_country_specification ADD PRIMARY KEY (pkid)");

-- 创建唯一索引
call add_index('tbl_config_country_specification','country_specification_index',"alter table tbl_config_country_specification add unique key country_specification_index (country_id_fk,specification_id_fk)");

-- 删除索引
call del_index('tbl_project_specification','index_1',"alter table tbl_project_specification drop key index_1");