推荐订阅源

-- 定义函数，其索引名称为 tbl_name_col_name
CREATE DEFINER=`root`@`%` PROCEDURE `create_index_if_not_exists`(in tbl_name varchar(64), in col_name varchar(64))
begin
    set @idx_name =concat(tbl_name, '_', col_name);
    set @index_count = (
        select count(*)
        from information_schema.statistics
        where table_schema = database()
        and table_name = tbl_name
        and index_name = @idx_name
    );
    if @index_count = 0 then
        set @create_index_sql = concat('create index ', @idx_name, ' on ', tbl_name, ' (', col_name, ')');
        prepare stmt from @create_index_sql;
        execute stmt;
        deallocate prepare stmt;
    end if;
end

-- 使用函数
CALL create_index_if_not_exists('fsys_message', 'MESSAGE_TYPE');