MySQL插入Table2触发更新Table1报1442错误怎么解决？

报错原因 #

MySQL 有内置的读写冲突限制：在触发器中不允许修改触发该触发器的语句当前正在读取或写入的表，避免出现死锁、递归调用或数据一致性问题，这就是1442错误的触发原因。

解决方案 #

方案1：弃用触发器，插入后批量更新（推荐） #

将插入和更新逻辑放在同一个事务中执行，绕开触发器限制，性能更高：

START TRANSACTION;
-- 插入Table2数据
INSERT INTO Table2 (table1_id, amount)
SELECT 
    table1_id,
    amount
FROM Other_Table;
-- 批量更新Table1的标识字段
UPDATE Table1 
SET inserted_in_table2 = TRUE
WHERE id IN (
    SELECT DISTINCT table1_id 
    FROM Table2
    -- 增量插入场景可加范围条件缩小更新范围，提升执行效率
);
COMMIT;

方案2：保留触发器，用临时表过渡插入数据 #

如果业务必须保留原触发器逻辑，可以先把待插入数据存入临时表，再从临时表插入Table2，避免触发读写冲突：

-- 创建临时表存储待插入数据
CREATE TEMPORARY TABLE temp_table2_data (
    table1_id INT,
    amount DECIMAL(10,2)
);
-- 先把数据导入临时表
INSERT INTO temp_table2_data (table1_id, amount)
SELECT table1_id, amount FROM Other_Table;
-- 从临时表插入到Table2，触发触发器时不会产生读写冲突
INSERT INTO Table2 (table1_id, amount)
SELECT table1_id, amount FROM temp_table2_data;
-- 销毁临时表
DROP TEMPORARY TABLE temp_table2_data;

方案3：移除冗余字段，查询时动态计算 #

如果inserted_in_table2仅用于标识Table1记录是否关联了Table2数据，可以直接删除这个冗余字段，查询时用EXISTS动态判断结果即可，省去字段维护成本：

SELECT 
    t1.*,
    EXISTS(SELECT 1 FROM Table2 t2 WHERE t2.table1_id = t1.id) AS inserted_in_table2
FROM Table1 t1;

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