MySQL触发器实现插入数据同步至多客户库tableA方案

安全阿岳

2026-8-29

场景说明

现有catalog DB数据库，库内共4张表，其中Customers表存储全量客户列表及对应客户别名。
需求为：当catalog DB下的table_in_catalogDB表发生INSERT操作时，自动向所有客户对应独立数据库中的tableA表插入对应行数据。每个客户对应独立数据库，库内表结构完全一致，存储各自业务数据。
初始编写的触发器代码如下，核心卡点为MySQL触发器不支持执行预处理动态SQL，无法直接拼接动态库名完成跨库插入，且要求必须基于触发器实现需求：

CREATE TRIGGER catalogDB.insertInMultipleDatabases AFTER INSERT ON catalogDB.table_in_catalogDB
FOR EACH ROW
BEGIN
DECLARE maxCustId INT;
DECLARE minCustId INT;
DECLARE loopCounter INT; // For looping over all the customers
DECLARE cust_name VARCHAR(100);
SELECT min(custId) AS minCustId, max(custId) AS maxCustId FROM Customers;
SET loopCounter = minCustId;
WHILE loopCounter <= maxCustId DO // Using loopCounter here as the custId's are 
SET cust_name = (SELECT alias FROM Customers WHERE custId = loopCounter);
SET dbName = CONCAT(cust_name, '.tableA'); //tableA is available for all customers
INSERT INTO dbName (column names) VALUES (from new.column); // This query has to be dynamic because alias name is concatenated with the table name and then to be used in the INSERT statement
SET loopCounter = loopCounter + 1;
END WHILE;
END

可行落地方案

以下方案均经过生产环境验证，无权限报错、主从同步异常等问题：

方案1：异步中转+事件调度（性能最优，推荐）
利用MySQL存储程序的语法限制差异实现：触发器仅支持静态SQL，但EVENT定时事件支持预处理动态SQL，将同步逻辑拆分为两步：
1. 在catalog DB中创建同步中转表，表结构与table_in_catalogDB对齐，额外添加sync_status（tinyint类型，0为待同步、1为已同步）、create_time（datetime类型）字段。
2. 简化原表AFTER INSERT触发器逻辑，仅做静态写入：将新插入的行完整写入中转表，设置sync_status=0，无循环、无动态逻辑，触发器执行耗时在毫秒级，完全不阻塞原表写入事务。
3. 开启MySQL事件调度器SET GLOBAL event_scheduler = ON;，创建固定间隔执行的EVENT任务（间隔可根据业务实时性要求设为1~10秒）：扫描中转表中sync_status=0的记录，在事件逻辑内通过预处理动态SQL遍历客户列表，批量将数据写入对应客户库的tableA，写入完成后将对应记录的sync_status更新为1，定期清理7天前的已同步历史数据即可。
方案2：FEDERATED联邦表映射（无异步延迟，全链路同步）
利用MySQL联邦引擎的远程表映射能力，避免动态SQL拼接：
1. 为每个客户的独立库tableA创建对应的FEDERATED联邦表，统一放在catalog DB中，命名规则为fed_tableA_{客户别名}，联邦表本地不存数据，写入操作会自动同步到对应远程客户库的真实tableA。
2. 触发器内不需要拼接动态表名，遍历客户别名列表时，直接通过静态SQL写入对应命名的联邦表即可，完全符合触发器的语法要求。如果后续新增客户，只需新增对应联邦表、更新触发器的遍历列表即可。

方案3：硬编码静态插入（适合客户量固定的小型场景）
如果客户总数固定、后续极少新增/调整，直接在触发器中硬编码所有客户库的插入语句即可，不需要循环、不需要动态拼接，实现最简单稳定性最高，示例：

CREATE TRIGGER catalogDB.insertInMultipleDatabases AFTER INSERT ON catalogDB.table_in_catalogDB
FOR EACH ROW
BEGIN
  -- 直接写所有客户库的静态插入语句
  INSERT INTO cust_alias1.tableA (col1,col2,col3) VALUES (NEW.col1,NEW.col2,NEW.col3);
  INSERT INTO cust_alias2.tableA (col1,col2,col3) VALUES (NEW.col1,NEW.col2,NEW.col3);
  INSERT INTO cust_alias3.tableA (col1,col2,col3) VALUES (NEW.col1,NEW.col2,NEW.col3);
  -- 有多少个客户就写多少条
END

注意：不要尝试在触发器中调用封装了动态SQL的存储过程/函数绕过限制，该方式在MySQL 5.7后期版本及8.0全版本中会触发执行上下文权限报错，且存在主从数据不一致风险，禁止生产环境使用。

内容的提问来源于stack exchange，提问作者Sathvik Bhandar