mysql批量操作多数据库存储过程

先从jpa_datasource中读取所有数据库 然后进行遍历执行sql 并记录成功及失败信息日志。

批量增加字段

CREATE DEFINER=`root`@`localhost` PROCEDURE `batchAddField`(IN `tableName` varchar(100),IN `fieldName` varchar(100),IN `fieldType` varchar(100),IN `defaultValue` varchar(100), IN `colComment` varchar(100))
BEGIN

    #数据库名称
    DECLARE schemaName VARCHAR(100); 

    #声明结束标识
    DECLARE end_flag int DEFAULT 0;
		#声明错误代码
    DECLARE code CHAR(5) DEFAULT '00000';
		#声明错误信息
    DECLARE msg TEXT;
		#声明错误结果
		DECLARE result TEXT;
    #声明游标 curosr ,查找所有“MyDB_”开头的数据库
    DECLARE curosr CURSOR FOR SELECT name FROM jpa_datasource;
    #设置终止标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

    #打开游标
    OPEN curosr;
    
    #遍历游标
    loop_label:  LOOP
        
        FETCH curosr INTO schemaName;#获取当前游标指针记录，取出值赋给自定义的变量
        
        IF end_flag > 0 THEN 
            LEAVE  loop_label;
        END IF;
    
				IF defaultValue IS NULL THEN
						SET @sqlStr=CONCAT('alter table ',schemaName,'.',tableName,' add column `',fieldName,'`  ',fieldType,' DEFAULT NULL COMMENT ''', colComment, '''');
				ELSE
						SET @sqlStr=CONCAT('alter table ',schemaName,'.',tableName,' add column `',fieldName,'`  ',fieldType,' DEFAULT ''',defaultValue,''' COMMENT ''', colComment, '''');
				END IF;
    PREPARE stmt from @sqlStr;
    EXECUTE stmt;
		
				-- 检查code是否改变，如果改变表示插入异常了
				IF code = '00000' THEN
					SET result = CONCAT('succeeded');
				ELSE
					-- 复制异常code，异常信息
					SET result = CONCAT('failed, error = ',code,', message = ',msg);
					-- 重新置为初始值
					SET code = '00000';
				END IF;
				-- 记录日志
				INSERT INTO procedure_process_log(schemaName, type, result, tableName, fieldName, fieldType, defaultValue, 	 colComment, createdAt) VALUES(schemaName, 'add', result, tableName, fieldName, fieldType, defaultValue, colComment, now());
    END LOOP;
    
  #关闭游标
    close curosr;
END

批量修改字段

CREATE DEFINER=`root`@`localhost` PROCEDURE `batchModifyField`(IN `tableName` varchar(100),IN `fieldName` varchar(100),IN `fieldType` varchar(100),IN `defaultValue` varchar(100), IN `colComment` varchar(100))
BEGIN

    #数据库名称
    DECLARE schemaName VARCHAR(100); 

    #声明结束标识
    DECLARE end_flag int DEFAULT 0;
		#声明错误代码
    DECLARE code CHAR(5) DEFAULT '00000';
		#声明错误信息
    DECLARE msg TEXT;
		#声明错误结果
		DECLARE result TEXT;
    #声明游标 curosr ,查找所有“MyDB_”开头的数据库
    DECLARE curosr CURSOR FOR SELECT name FROM jpa_datasource;
    #设置终止标志
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_flag=1;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
      -- 获取异常code,异常信息
      GET DIAGNOSTICS CONDITION 1
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

    #打开游标
    OPEN curosr;
    
    #遍历游标
    loop_label:  LOOP
        
        FETCH curosr INTO schemaName;#获取当前游标指针记录，取出值赋给自定义的变量
        
        IF end_flag > 0 THEN 
            LEAVE  loop_label;
        END IF;
    
				IF defaultValue IS NULL THEN
						SET @sqlStr=CONCAT('alter table ',schemaName,'.',tableName,' modify column `',fieldName,'`  ',fieldType,' DEFAULT NULL COMMENT ''', colComment, '''');
				ELSE
						SET @sqlStr=CONCAT('alter table ',schemaName,'.',tableName,' modify column `',fieldName,'`  ',fieldType,' DEFAULT ''',defaultValue,''' COMMENT ''', colComment, '''');
				END IF;
    PREPARE stmt from @sqlStr;
    EXECUTE stmt;
		
				-- 检查code是否改变，如果改变表示插入异常了
				IF code = '00000' THEN
					SET result = CONCAT('succeeded');
				ELSE
					-- 复制异常code，异常信息
					SET result = CONCAT('failed, error = ',code,', message = ',msg);
					-- 重新置为初始值
					SET code = '00000';
				END IF;
				-- 记录日志
				INSERT INTO procedure_process_log(schemaName, type, result, tableName, fieldName, fieldType, defaultValue, 	 colComment, createdAt) VALUES(schemaName, 'modify', result, tableName, fieldName, fieldType, defaultValue, colComment, now());
    END LOOP;
    
  #关闭游标
    close curosr;
END

实际大体逻辑相同 只是改了执行的sql而已

数据库存储表

CREATE TABLE `jpa_datasource` (
  `name` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

执行日志表

CREATE TABLE `procedure_process_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `schemaName` varchar(100) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `result` text,
  `tableName` varchar(100) DEFAULT NULL,
  `fieldName` varchar(100) DEFAULT NULL,
  `fieldType` varchar(100) DEFAULT NULL,
  `defaultValue` varchar(100) DEFAULT NULL,
  `colComment` varchar(100) DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4;

效果演示：

添加字段

call batchAddField('jpa_items', 'sort', 'int(11)', 0, '排序值')

再次执行

执行修改

call batchModifyField('jpa_items', 'sort', 'int(12)', 0, '排序值')