mysql快速在不同库中执行相同的sql

目录

背景

解决方案

方式一：利用变量拼接好sql，复制出来执行（简单，推荐）

方式二：使用存储过程和游标实现（比较复杂，脚本需要拼接一个完整的，也比较麻烦，不过好处是自动执行的）

---

背景

最近在做sass，一家客户（企业）对应一个数据库（同一个链接不同的数据库隔离），遇到的问题是通用的功能模块建表，修改数据库字段等等脚本很不方便，之前的执行方式都是将sql脚本手动拼上schema名

如：

alter table test1.example_table add column age varchar(20) COMMENT '年龄';
alter table test2.example_table add column age varchar(20) COMMENT '年龄';

这种方式在租户很少的情况下觉得没什么工作量，但随着客户越来越多，执行脚本起来就越来越麻烦，不胜其烦，所以想利用脚本生成完整的可执行的sql,这样可以省去很多时间。

解决方案

方式一：利用变量拼接好sql，复制出来执行（简单，推荐）

-- 自定义变量是属于会话级别，无需显示删除，会话结束（关闭链接）自动删除，或者SET @my_var = NULL; 删除-- 根据需要修改过滤条件，主要是查找要执行的目标数据库
SET @schemas = (SELECT GROUP_CONCAT(CONCAT('`', SCHEMA_NAME, '`'))FROM INFORMATION_SCHEMA.SCHEMATAWHERE SCHEMA_NAME LIKE 'test%'  
);-- 你要执行的SQL
SET @sql_to_execute = 'CREATE TABLE example_table (id INT PRIMARY KEY, name VARCHAR(100))';  -- sql拼接
SET @combined_sql = (SELECT GROUP_CONCAT(CONCAT('USE ', schema_name, '; ', @sql_to_execute, ';')SEPARATOR ' ') FROM (SELECT SCHEMA_NAME as schema_nameFROM INFORMATION_SCHEMA.SCHEMATAWHERE SCHEMA_NAME LIKE 'test%') t
);-- 查询变量，结果就是可执行的sql,复制出来执行执行
SELECT @combined_sql;-- 例如：上述打印出来的sqlUSE test;
CREATE TABLE example_table (id INT PRIMARY KEY,
NAME VARCHAR ( 100 ));
USE test2;
CREATE TABLE example_table (id INT PRIMARY KEY,
NAME VARCHAR ( 100 ));
USE test3;
CREATE TABLE example_table (id INT PRIMARY KEY,
NAME VARCHAR ( 100 ));
USE test4;
CREATE TABLE example_table (id INT PRIMARY KEY,
NAME VARCHAR ( 100 ));
USE test5;
CREATE TABLE example_table (
id INT PRIMARY KEY,
NAME VARCHAR ( 100 ));-- 直接打开一个控制台执行即可，非常简单

如果执行的不是一条语句，是一个脚本也是适应的，如：

-- 这里面就包含两个sql,每条必须是可以执行的
SET @sql_to_execute = 'alter table example_table add column age varchar(20) COMMENT ''年龄'';
alter table example_table1 add column age varchar(20) COMMENT ''年龄'';

方式二：使用存储过程和游标实现（比较复杂，脚本需要拼接一个完整的，也比较麻烦，不过好处是自动执行的）

-- 1. 更改分隔符，因为存储过程中包含多个语句，需要使用不同的分隔符
DELIMITER $$-- 2. 创建名为 operate_database 的存储过程
CREATE PROCEDURE operate_database()
BEGIN-- 3. 声明变量 done 用于控制循环，默认为 FALSEDECLARE done INT DEFAULT FALSE;-- 4. 声明变量 db_name 用于存储数据库名称DECLARE db_name VARCHAR(100);-- 5. 声明游标，用于遍历所有以 'test' 开头的数据库DECLARE cur CURSOR FOR SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE 'test%';-- 6. 声明异常处理，当没有更多记录时设置 done 为 TRUEDECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;-- 7. 打开游标OPEN cur;-- 8. 开始循环处理每个数据库read_loop: LOOP-- 9. 从游标中获取下一个数据库名称FETCH cur INTO db_name;-- 10. 如果没有更多数据库，退出循环IF done THENLEAVE read_loop;END IF;-- 11. 构造创建表的 SQL 语句-- 使用反引号(`)包围数据库名和表名，防止特殊字符和保留字的问题SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS `', db_name, '`.`example_table` (id INT PRIMARY KEY,name VARCHAR(100))');-- 12. 预处理 SQL 语句PREPARE stmt FROM @sql;-- 13. 执行预处理的语句EXECUTE stmt;-- 14. 释放预处理的语句DEALLOCATE PREPARE stmt;END LOOP;-- 15. 关闭游标CLOSE cur;
-- 16. 结束存储过程
END $$-- 17. 将分隔符改回分号
DELIMITER ;-- 18. 调用存储过程
CALL operate_database();-- 19. 验证表是否创建成功
-- 查询 INFORMATION_SCHEMA.TABLES 视图，显示所有创建的表
SELECT TABLE_SCHEMA as database_name,  -- 数据库名TABLE_NAME as table_name,       -- 表名'Table exists' as status        -- 状态信息
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA LIKE 'test%'     -- 筛选以 test 开头的数据库AND TABLE_NAME = 'example_table'; -- 筛选表名为 example_table 的表-- 20. 删除存储过程，清理环境
DROP PROCEDURE IF EXISTS operate_database;

关键概念解释：

1. DELIMITER $$ - 更改分隔符，使存储过程内的分号不会结束整个语句
2. DECLARE - 用于声明变量和游标
3. CURSOR - 用于遍历结果集的数据库对象
4. HANDLER - 异常处理机制
5. PREPARE， EXECUTE， DEALLOCATE - 动态SQL执行的三个步骤
6. CONCAT - 字符串拼接函数
7. INFORMATION_SCHEMA - MySQL的信息数据库，包含数据库和表的元数据

这种方式如果本身有个sql脚本包含多个sql，拼接起来也比较繁琐