SQL触发器实现主从表同步计数

在触发器内部直接使用 COUNT(*) 会触发报错：ERROR 1442。这并非语法错误，而是 MySQL 的并发安全机制在起作用——它禁止在触发器中对当前正在修改的表执行聚合查询。本质上，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 更新这个分支即可。