MySQL使用存储过程对当前库所有表批量操作

生产环境下有一个库中所有表的格式都是一样的，在一次新增需求后，我们在技术实现上需要对所有表为某个字段添加一个索引，库里有几百张表，不可能一条一条sql去执行，特地写一段java代码进行处理也很麻烦，数据库在线上只能内网访问。经过一番考虑之后，我们决定使用存储过程完成这个操作。

流程大概是这样：

①查出本库中所有符合修改条件的表

②取出查询结果并打开游标

③遍历查询结果，使用表名作为变量拼装出添加索引的SQL语句后编译并执行SQL

④关闭游标，输出操作成功的标识

以下是存储过程的具体定义：

BEGIN
    DECLARE Done INT DEFAULT 0;
    DECLARE temp_table_name VARCHAR(32);
    #查出当前所有新闻表并声明为游标
    DECLARE rs CURSOR FOR SELECT CONCAT('`',TABLE_SCHEMA,'`.`',TABLE_NAME,'`') FROM information_schema.tables WHERE TABLE_SCHEMA LIKE 'news-%' AND TABLE_NAME NOT LIKE '%_content';

    #游标结束时的变量赋值
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;

    #异常处理，删除索引时报索引不存在的错误的话继续执行其后语句
    #DECLARE CONTINUE HANDLER FOR 1091 SET @a = 0;

    #异常处理，添加索引时报索引已存在的错误的话继续执行其后语句
    DECLARE CONTINUE HANDLER FOR 1061 SET @a = 0;

    #打开游标
    OPEN rs;
    
    #逐个取出当前记录字段的值
    FETCH NEXT FROM rs INTO temp_table_name;
    WHILE (NOT Done) DO
    
        #删除索引
        #SET @sqlStr = CONCAT('ALTER TABLE ',temp_table_name,' DROP INDEX `create_time_index` ');
        #编译SQL语句
        #PREPARE stmt FROM @sqlStr;
        #执行语句
        #EXECUTE stmt;

        #添加索引
        SET @sqlStr = CONCAT('ALTER TABLE ',temp_table_name,' ADD INDEX `create_time_index` (`createTime`) USING BTREE ');
        #编译SQL语句
        PREPARE stmt FROM @sqlStr;
        #执行语句
        EXECUTE stmt;

        #继续循环
        FETCH NEXT FROM rs INTO temp_table_name;
    
    END WHILE;
    #关闭游标
    CLOSE rs;
    
    SELECT "ojbk";
END

看到这里，存储过程的知识是否又提升了呢？

存储过程的优点就是快，缺点就是高耦合，很难对涉及存储过程的表进行拆分，在业务系统中一定要慎用。特别是需求不定的业务系统，更是不应该优先使用存储过程。