SQL自动更新运行总计表：触发器与Merge选型及多触发器合并

嘿，我来帮你理清这两个方案的差异，再把你现有的触发器代码给捋顺～

方案对比：触发器 vs PHP+MERGE #

触发器方案 #

• 优势：完全在数据库层面自动执行，只要pc12_status有新数据插入，总计表就会实时更新，不需要应用层做额外操作，数据一致性有保障，适合所有修改都直接走数据库的场景。
• 劣势：逻辑耦合在数据库中，调试和后续维护不如应用层直观；复杂的触发器逻辑（比如连锁触发）可能会在高并发场景下影响性能，而且如果触发器写得有问题，容易出现死循环或者数据重复的情况。

PHP+MERGE方案 #

• 优势：逻辑放在应用层，你可以灵活添加业务校验、日志记录、异常处理等逻辑，调试和维护更方便；如果后续有业务规则变更，修改PHP代码比修改数据库触发器更简单。
• 劣势：依赖应用层的调用，如果有其他渠道直接操作数据库（比如手动执行SQL、其他服务直接写库），就会导致总计表数据不一致，必须确保所有对pc12_status的修改都走这个PHP流程。

你的触发器代码问题与修复 #

你现有的两个触发器存在几个明显的问题：

1. 第一个触发器关联了疑似拼写错误的表（total_hours应该是total_hrs？），而且直接INSERT会导致同一飞机同一日期的重复记录；
2. 第二个触发器是AFTER INSERT ON total_hrs后又UPDATE total_hrs，会引发循环触发，而且SQL语法不完整（SUM(s.flight_hrs后面未闭合）。

修正后的触发器实现 #

首先，我们需要给total_hrs加一个唯一约束，确保同一飞机（acft_tail）同一日期（date_flight）只有一条记录：

ALTER TABLE total_hrs ADD UNIQUE KEY idx_acft_date (acft_tail, date_flight);

然后，把两个触发器的逻辑合并成一个，实现插入/更新pc12_status时，同步更新total_hrs的acft_hrs和累计的prop_hrs：

CREATE TRIGGER upd_totals AFTER INSERT ON pc12_status 
FOR EACH ROW
INSERT INTO total_hrs (acft_hrs, date_flight, acft_tail, prop_hrs)
VALUES (
    NEW.hrs_end,
    NEW.flight_date,
    NEW.tail_no,
    -- 计算该飞机截至当前日期的累计飞行时长
    (SELECT SUM(s.flight_hrs) FROM acft_status s WHERE s.tail_no = NEW.tail_no AND s.flight_date <= NEW.flight_date)
)
-- 如果该记录已存在，就更新时长字段
ON DUPLICATE KEY UPDATE 
    acft_hrs = NEW.hrs_end,
    prop_hrs = (SELECT SUM(s.flight_hrs) FROM acft_status s WHERE s.tail_no = NEW.tail_no AND s.flight_date <= NEW.flight_date);

额外优化建议 #

• 给acft_status表创建tail_no + flight_date的联合索引，提升累计时长查询的性能：

  CREATE INDEX idx_acft_flight_date ON acft_status (tail_no, flight_date);
  

• 如果你的数据库支持计算列（比如MySQL 8.0+的生成列、PostgreSQL的计算字段），可以把prop_hrs设置为自动计算的字段，这样就不需要在触发器里写子查询了，比如MySQL的写法：

  ALTER TABLE total_hrs ADD COLUMN prop_hrs INT GENERATED ALWAYS AS (
      (SELECT SUM(s.flight_hrs) FROM acft_status s WHERE s.tail_no = acft_tail AND s.flight_date <= date_flight)
  ) STORED;
  

最终建议 #

• 如果你的系统中所有对pc12_status的修改都通过PHP应用层处理，优先选PHP+MERGE方案，扩展性更强；
• 如果存在直接操作数据库的场景（比如后台手动改数据），或者需要强实时的数据一致性，选触发器方案，但要确保触发器逻辑经过充分测试，避免循环触发和性能问题。

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