MySQL执行DELETE删除users表数据触发触发器无法更新表报错求助

MySQL Error: Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger 报错的核心原因是：你当前执行的users表DELETE语句已经锁定了users表，触发的触发器链路中又出现了读写users表的操作，MySQL不允许这种同表嵌套操作避免死循环。

你给出的CLEANUP触发器本身只操作其他关联表，不会触发该报错，问题大概率出在两个地方：

1. 你当前的触发器存在语法错误，创建时逻辑解析异常触发了未知的同表操作
2. account_data等关联子表上绑定的触发器，在被CLEANUP触发器触发执行时，内部存在读写users表的逻辑

• 第一步：修正触发器语法错误
  你提供的触发器代码末尾多写了一个END，且创建时没有修改分隔符会导致内部语句被截断，使用以下正确语法重新创建触发器：

-- 临时修改分隔符为//，避免触发器内部的;被误判为语句结束
DELIMITER //
CREATE TRIGGER `CLEANUP` AFTER DELETE ON `users`
    FOR EACH ROW BEGIN
        DELETE FROM account_data WHERE account_data.owner = OLD.identifier;
        DELETE FROM inventory_items WHERE inventory_items.owner = OLD.identifier;
        DELETE FROM datastore WHERE datastore.owner = OLD.identifier;
        DELETE FROM invest WHERE invest.identifier = OLD.identifier;
        DELETE FROM user_stats WHERE user_stats.identifier = OLD.identifier;
    END //
-- 改回默认分隔符
DELIMITER ;

• 第二步：排查关联子表的触发器
  逐一检查account_data、inventory_items、datastore、invest、user_stats这几个表的所有触发器，确认没有任何触发器逻辑中存在读写users表的代码，如果有需要调整对应逻辑避免嵌套操作。
• 第三步：可替代的无触发器方案
  如果不想处理触发器冲突，可以使用InnoDB引擎支持的外键级联删除实现同样的关联清理逻辑，性能和稳定性都优于触发器，示例如下（其他子表同理修改）：

-- 给account_data表创建级联删除外键
ALTER TABLE account_data
ADD CONSTRAINT fk_account_data_owner 
FOREIGN KEY (owner) REFERENCES users(identifier) 
ON DELETE CASCADE;

注：该方案需要所有关联表都使用InnoDB存储引擎，外键字段的类型、长度、字符集必须和users.identifier完全一致

内容的提问来源于stack exchange，提问作者Staffan Löwgren