SQL触发器实现主从表同步计数的完整方案

在 MySQL 触发器中直接使用 COUNT(*) 会触发 ERROR 1442 报错，这并非语法问题，而是 MySQL 为防止并发操作导致数据不一致而设计的保护机制：禁止触发器对当前正在修改的表执行聚合查询。简而言之，这一限制是为了避免在修改数据的同时查询总数，从而保证数据完整性。

正确的做法是通过变量累计增减计数，而非实时查询总数。例如，主表每插入一行，计数字段便加 1；删除一行则减 1。具体实现方式如下：

• INSERT 触发器：利用 NEW 获取新插入的行，对从表关联字段执行 UPDATE main_table SET count_col = count_col + 1 WHERE id = NEW.main_id 以实现计数递增
• DELETE 触发器：通过 OLD 获取被删除的行，执行减 1 操作
• UPDATE 触发器：需分情况处理——如果 main_id 发生变化，旧值对应的主表行减 1，新值对应的行加 1

UPDATE 触发器中 main_id 变更如何处理？

这一逻辑最容易遗漏。仅监听 INSERT 和 DELETE 事件并不足够，当从表记录的外键 main_id 被更新时，主表中两条不同记录的计数必须同步调整：原 main_id 对应行减 1，新 main_id 对应行加 1。

关键判断条件是 OLD.main_id != NEW.main_id。仅使用 IF NEW.main_id IS NOT NULL 这类宽松条件会遗漏“从一个非空值改成另一个非空值”的场景。

• 先执行 UPDATE main_table SET count_col = count_col - 1 WHERE id = OLD.main_id
• 再执行 UPDATE main_table SET count_col = count_col + 1 WHERE id = NEW.main_id
• 注意添加 AND OLD.main_id IS NOT NULL 和 AND NEW.main_id IS NOT NULL 条件，避免空值干扰

为什么不应使用视图或应用层来计算计数？

视图每次查询都需执行 JOIN + COUNT(*)，当从表数据量达到百万级时性能显著下降。而通过应用层手动维护计数，则易因异常退出、网络中断导致计数漂移，多个服务实例并发写入时更易出错。

触发器的优势在于原子性：它与主 DML 操作同处一个事务，失败时一起回滚。代价是每次修改从表都需额外更新主表的计数字段，写入性能会降低。

• 该方案适用于读取远多于写入的场景，例如展示订单中的商品数量
• 应避免在高频写入的从表上使用，如日志明细表
• 主表的计数字段必须建立索引，否则 UPDATE ... WHERE id = ? 可能引发全表扫描

MySQL 8.0+ 的替代方案：生成列 + 索引是否可行？

不行。GENERATED COLUMN 不支持子查询或聚合函数，也无法引用其他表的字段，因此无法自动计算关联行数。虽然有人尝试用 JSON_TABLE 或窗口函数绕过限制，但这些均不能用于生成列定义，更无法用于索引构建。

真正可行的替代方案只有物化视图（MySQL 目前尚未原生支持）或定期任务——例如每分钟执行一次 UPDATE main_table t JOIN (SELECT main_id, COUNT(*) c FROM detail GROUP BY main_id) d ON t.id = d.main_id SET t.count_col = d.c。但这并非实时同步，存在延迟。

因此，触发器依然是目前唯一能保证强一致、实时、无需外部调度的方案——只要确保覆盖所有变更路径（INSERT、UPDATE、DELETE），尤其不要遗漏 main_id 更新这一分支即可。