普通索引改唯一索引存储过程

-- 新增唯一索引
DROP PROCEDURE if EXISTS proc_add_unique_index;
delimiter $$
CREATE PROCEDURE `proc_add_unique_index`(in var_table_name varchar(64), in var_column_name varchar(1000),
                                         in var_index_name varchar(64))
    top:
begin

    -- 表不存在则直接返回
    set @p_tablenum = '';
    set @sqlstr1 = concat(
            'select count(table_name)into @p_tablenum from information_schema.tables where table_schema=database() and table_name=\'',
            var_table_name, '\' limit 1;');
prepare stmt1 from @sqlstr1;
execute stmt1;
deallocate prepare stmt1;
if (@p_tablenum < 1) then
        leave top;
end if;

    -- 获得数组长度
    SET @arrLength = LENGTH(var_column_name) - LENGTH(REPLACE(var_column_name, ',', ''));
    SET @arrString = var_column_name;

    -- 循环校验字段是否存在
    WHILE @arrLength > 0 DO
    -- 得到分隔符前面的字符串
    set @tmpColumn = substr(@arrString, 1, instr(@arrString, ',') - 1);
    -- 得到分隔符后面的字符串
    set @arrString = substr(@arrString, length(@tmpColumn) + length(',') + 1);
    set @arrLength = @arrLength - 1;

    -- 字段不存在则直接返回
    set @p_columnnum = '';
    set @sqlstr = concat(
            'select count(column_name) into @p_columnnum from information_schema.columns where table_schema=database() and table_name=\'',
            var_table_name, '\'and column_name =\'', @tmpColumn, '\';');
prepare stmt2 from @sqlstr;
execute stmt2;
deallocate prepare stmt2;
if (@p_columnnum <= 0) then
        leave top;
end if;
END WHILE;

    -- 字段且索引存在才创建索引
    set @str = concat(' alter table `', var_table_name, '` add UNIQUE index `', var_index_name, '` (`',
                      REPLACE(var_column_name, ',', '`,`'), '`);');
    set @cnt = '';
select count(*) into @cnt
from information_schema.statistics
where table_schema = database()
  and table_name = var_table_name
  and index_name = var_index_name;
if (@cnt = 0) then
        PREPARE stmt FROM @str;
EXECUTE stmt;
end if;
end $$
delimiter ;

-- 路由path建唯一索引
-- 删除路由path重复数据 保留id最大的一条
DELETE from `sys_route` where `id` not in (select t.max_id from
(select max(id) as max_id from `sys_route` group by `path`) as t);
-- 删除普通索引
call proc_drop_column_index('sys_route','path','path');
-- 新增唯一索引
call proc_add_unique_index('sys_route','path','path');