如何编写含同表删除操作的MySQL AFTER DELETE触发器及报错解析

• 这个报错真不是你选触发时机选错了，是MySQL对触发器的硬性约束：不管你是BEFORE还是AFTER触发器，只要是当前删除语句触发的触发器，整个执行上下文都没有释放对friend表的访问锁、元数据句柄，根本不允许触发器再对同一张表发起任何增、删、改、查操作，只要碰就会抛出固定错误码1442。
• 很多人说这是无限递归导致的，其实递归是MySQL设计这个规则要防范的风险，不是你现在报错的直接诱因：
  • 你以为AFTER触发器触发的时候，原删除语句已经跑完了，表就释放了？并不是，AFTER的触发时机是原语句的所有行删除完成后、整个事务提交前的阶段，这个阶段的friend表依然被当前调用触发器的原语句独占持有，这时候你哪怕在触发器里写个select * from friend limit 1，都会抛一模一样的“表被调用语句占用”的错误，更别说delete操作了。
  • 为什么MySQL要把规则卡这么严？就是怕真的放你操作同表，形成无限循环：你删(1,2)，原语句跑完触发触发器，触发器删(2,1)，这个删除动作又触发同表的AFTER DELETE触发器，又回头去删对应的反向记录(1,2)，循环往复直接把数据库打挂。所以MySQL干脆从规则层面直接堵死路径，不管你逻辑上会不会真的跑成递归，只要检测到触发器要操作自己的触发源表，就直接报错拦截。

别硬卡同表触发器的限制，按实际场景选方案就行：

• 最省维护成本的方案：从存储结构上消灭双向冗余
  • 修改friend表的存储规则：永远固定让ID值更小的用户放在id1字段，ID值更大的放在id2字段，比如1和2是好友，就永远只存(1,2)这一条记录，根本不存(2,1)的冗余数据。
  • 配套写一个简单的BEFORE INSERT触发器：每次插入好友关系的时候自动交换两个ID，保证小ID在前、大ID在后。这样所有好友关系都是单条存储，压根不需要同步删除反向记录，自然没有同表操作的触发限制问题。
• 一定要保留双向冗余存储的场景：把同步逻辑挪出触发器
  • 别靠触发器做同步删除，写个通用的删除好友存储过程，把两个方向的删除动作放在同一个事务里执行：

  delimiter //
  drop procedure if exists DeleteFriend //
  create procedure DeleteFriend(in p_uid1 int, in p_uid2 int)
  begin
      -- 同事务内顺序执行双向删除，没有嵌套在触发器里，不会触发表占用限制
      delete from friend where id1 = p_uid1 and id2 = p_uid2;
      delete from friend where id1 = p_uid2 and id2 = p_uid1;
  end //
  delimiter ;
  

  • 后续所有删除好友的操作都调用这个存储过程就行，不存在递归、也不会触发同表的操作限制。
• 逻辑冗余不推荐的绕路方案：用中间表解耦
  • 建一个结构和friend表一致的临时同步暂存表，在friend的AFTER DELETE触发器里，只把需要删除的反向ID对写到暂存表，绝对不直接操作friend表。
  • 再给暂存表配一个AFTER INSERT触发器，读暂存表里的待删除记录，去friend表执行对应的反向删除，删完就清空暂存表的对应行。这种方式能绕开MySQL的同表触发器限制，但逻辑绕、维护成本高，生产环境不建议用。

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