MySQL如何在删除发票成功时条件更新主表INVOICESCOUNT字段

方案1：AFTER DELETE触发器（最推荐） #

该方案可以完全实现删除发票后自动更新客户发票计数的需求，所有逻辑封装在数据库层，业务侧不需要做任何代码改造，只需执行原删除语句即可。

创建触发器代码：

DELIMITER //
CREATE TRIGGER trg_invoices_after_delete
AFTER DELETE ON Invoices
FOR EACH ROW
BEGIN
  -- 避免计数出现负数，仅当计数大于0时执行减1操作
  UPDATE Customers 
  SET INVOICESCOUNT = INVOICESCOUNT - 1 
  WHERE Customer = OLD.Customer 
    AND INVOICESCOUNT > 0;
END //
DELIMITER ;

触发器创建完成后，业务侧仅需执行原始删除语句，计数更新逻辑会自动触发：

DELETE FROM Invoices WHERE INVOICE = 500;

版本要求 #

触发器功能从MySQL 5.0.2版本开始支持，目前主流使用的MySQL 5.7、8.0版本均完全兼容该方案。

方案2：事务+ROW_COUNT()实现单段SQL逻辑 #

如果你不想使用触发器，可以用事务包裹逻辑，整段SQL一次性执行即可，不需要业务侧分两次调用：

START TRANSACTION;
DELETE FROM Invoices WHERE INVOICE = 500;
-- 仅当删除操作实际影响了行记录时才执行计数更新
IF ROW_COUNT() > 0 THEN
  UPDATE Customers 
  SET INVOICESCOUNT = INVOICESCOUNT - 1 
  WHERE Customer = 1 
    AND INVOICESCOUNT > 0;
END IF;
COMMIT;

版本要求 #

ROW_COUNT()函数从MySQL 5.0.1版本开始支持，事务块分支逻辑的最低兼容版本同样为MySQL 5.0.2。

结合你提到的INVOICESCOUNT高频查询、筛选维度多无法缓存的场景，更推荐使用触发器方案，优势如下：

• 所有删除发票的操作都会自动触发计数更新，不会出现业务侧漏写更新语句导致的计数不一致问题
• 操作原子性由数据库原生保证，不会出现并发操作导致的计数误差
• 业务侧无感知，不需要改造原有删除逻辑

补充注意：建议每天低峰期执行一次全量计数校准，避免手动修改数据、异常事务回滚等极端场景导致的计数偏差：

UPDATE Customers c
SET INVOICESCOUNT = (SELECT COUNT(*) FROM Invoices i WHERE i.Customer = c.Customer);

内容的提问来源于stack exchange，提问作者Peter Jones