如何编写含SELECT和JOIN的MySQL AFTER INSERT触发器仅同步新插入数据

问题根因 #

你当前触发器同步全量数据的核心原因是SELECT语句没有限定仅读取刚插入的行，每次触发都会遍历bib_timing全表做关联查询，导致全量数据重复插入目标表。之前使用NEW关键字写入失败，是因为你用了NEW.tl.tag_id这类跨表引用的错误写法，NEW仅能引用触发器挂载表（也就是bib_timing）自身的字段。

修正后的触发器代码（最优方案） #

AFTER INSERT ON `bib_timing`
FOR EACH ROW
INSERT INTO witchfindertrail.comments (name, time_stamp)
SELECT 
  tl.tag_id,
  NEW.time_stamp
FROM zd4k_rfid_tag_list tl 
WHERE tl.id = NEW.name;

方案说明 #

• 移除了对bib_timing表的多余全表查询，直接使用触发器内置的NEW关键字获取当前刚插入行的所有字段值，执行效率更高
• 仅关联查询和当前新插入行的name（选手号码布编号）匹配的RFID标签记录，单次触发仅返回1条对应数据，不会产生全量同步
• 时间戳直接取新插入行的NEW.time_stamp，确保和工作人员录入的时间完全一致

之前添加NEW.id未生效的原因及可行写法 #

如果你需要保留对bib_timing表的查询逻辑，只要补充正确的WHERE条件筛选即可生效，你之前未生效大概率是漏加了WHERE条件或者条件写错：

-- 该写法也可正常运行，仅效率略低于上述最优方案
AFTER INSERT ON `bib_timing`
FOR EACH ROW
INSERT INTO witchfindertrail.comments (name, time_stamp)
SELECT
tl.tag_id,
bt.time_stamp
FROM bib_timing bt
JOIN zd4k_rfid_tag_list tl ON tl.id = bt.name
WHERE bt.id = NEW.id;

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