DELIMITER $$
CREATE PROCEDURE `sp_add_Index`(
IN p_tablename VARCHAR (100),
IN p_indexType VARCHAR(100),
IN p_indexcolumnList VARCHAR (200),
IN p_idxname VARCHAR(70)
)
BEGIN
-- CALL `sp_add_Index`('sys_resource','unique','code','');
-- CALL `sp_add_Index`('sys_resource','index','parent_code','');
DECLARE p_dbname VARCHAR(50);
SET @p_dbname = DATABASE();
SET @p_idxname = p_idxname; -- 索引名不能超过64字节
CASE p_indexType
WHEN 'primary' THEN
SET @p_idxname = 'primary';
SET @add_index = CONCAT('ALTER TABLE ', p_tablename, ' ADD ', 'primary key(', p_indexcolumnList ,');');
WHEN 'unique' THEN
IF LENGTH(@p_idxname) = 0 THEN
SET @p_idxname = CONCAT('uni_idx_',p_tablename,'_',REPLACE(p_indexcolumnList,',','_'));
END IF;
SET @add_index = CONCAT('ALTER TABLE ', p_tablename, ' ADD ', p_indexType,' ',@p_idxname ,'(', p_indexcolumnList ,');');
ELSE
IF LENGTH(@p_idxname) = 0 THEN
SET @p_idxname = CONCAT('idx_',p_tablename,'_',REPLACE(p_indexcolumnList,',','_'));
END IF;
SET @add_index = CONCAT('ALTER TABLE ', p_tablename, ' ADD ', p_indexType,' ',@p_idxname ,'(', p_indexcolumnList ,');');
END CASE;
SELECT
COUNT(*) INTO @cnt
FROM
information_schema.statistics
WHERE
TABLE_SCHEMA = @p_dbname
AND table_name = p_tablename
AND index_name = @p_idxname;
IF @cnt = 0 THEN
PREPARE stmt FROM @add_index; EXECUTE stmt;
END IF;
END$$
DELIMITER ;