MySQL插入/更新当前日期触发器报错问题排查

这个错误的核心原因很直白：你在AFTER INSERT触发器里尝试更新触发它的同一个表，MySQL不允许这种操作——因为此时插入语句还在占用表资源，触发器里的更新会引发锁冲突，所以抛出了1442错误。

其实根本不需要用UPDATE语句来修改字段，我们可以利用BEFORE触发器直接修改待插入/更新的行数据，这是更高效也符合规则的做法。

修正后的完整代码 #

drop table if exists test;
CREATE TABLE IF NOT EXISTS test (
 id int primary key, -- 建议给id加主键，避免重复插入问题
 mydate datetime not null default now(), -- 创建时间，默认取当前时间
 ts datetime not null default now() -- 最后更新时间，默认取当前时间
);

DELIMITER $$
-- 插入时初始化更新时间戳
DROP TRIGGER IF EXISTS before_test_insert;
CREATE TRIGGER `before_test_insert` BEFORE INSERT ON `test` FOR EACH ROW
BEGIN
 SET NEW.ts = CURRENT_TIMESTAMP;
END $$

-- 更新时刷新更新时间戳（保护创建时间不被修改）
DROP TRIGGER IF EXISTS before_test_update;
CREATE TRIGGER `before_test_update` BEFORE UPDATE ON `test` FOR EACH ROW
BEGIN
 SET NEW.ts = CURRENT_TIMESTAMP;
 -- 可选：锁定创建时间，防止被意外修改
 -- SET NEW.mydate = OLD.mydate;
END $$
DELIMITER ;

-- 测试插入操作
INSERT INTO test (id) VALUES (1);
-- 测试更新操作（可以给表加个name字段测试，比如UPDATE test SET name = 'test' WHERE id = 1;）
UPDATE test SET id = 1 WHERE id = 1;

关键说明 #

• 使用BEFORE INSERT触发器：在数据写入表之前，直接给NEW.ts赋值，插入的行自带正确的更新时间戳，不需要事后再更新表。
• 使用BEFORE UPDATE触发器：更新时直接修改NEW.ts，同时可以选择锁定mydate字段（比如SET NEW.mydate = OLD.mydate），确保创建时间只在插入时被设置。
• 给id加主键：避免重复插入的问题，也让触发器逻辑更可靠。

这样修改后，插入和更新操作都会自动维护ts字段，而且不会触发1442错误。

内容的提问来源于stack exchange，提问作者Rüdiger