如何借助SQL触发器实现主从表同步计数？

不能在触发器中直接写COUNT(*)查询自身表，会因事务隔离机制报错ERROR 1442；正确做法是用NEW/OLD字段原子更新独立计数字段或计数表，覆盖INSERT、UPDATE（含main_id变更）、DELETE全部路径，确保事务一致性。

触发器里怎么写 COUNT(*) 才不报错？

直接在触发器里写 COUNT(*) 会报错，因为 MySQL 不允许在触发器中对当前正在修改的表执行聚合查询（ERROR 1442: Can't update table 'xxx' in stored function/trigger because it is being used by statement which invoked this stored function/trigger）。这不是语法问题，而是 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 更新的分支。