仅当行已更改时，MySQL才会在更新后触发

作为一种解决方法，您可以使用时间戳（旧的和新的）来检查，如果该行没有更改，则不会更新该时间戳。（可能是造成混乱的根源？因为那也被称为“更新时”，但是在没有任何变化的情况下不会执行）一秒钟之内的变化将不会执行触发器的那部分，但是在某些情况下可能会很好（例如，当您的应用程序仍然拒绝快速更改时。）

例如，而不是

IF NEW.a <> OLD.a or NEW.b <> OLD.b /* etc, all the way to NEW.z <> OLD.z */ THEN
INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ; END IF 你可以用

IF NEW.ts <> OLD.ts THEN
INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b) ; END IF 这样，您就不必在每次更新方案时都更改触发器（问题中提到的问题）。

编辑：添加了完整的示例

create table foo (a INT, b INT, ts TIMESTAMP); create table bar (a INT, b INT);

INSERT INTO foo (a,b) VALUES(1,1); INSERT INTO foo (a,b) VALUES(2,2); INSERT INTO foo (a,b) VALUES(3,3);

DELIMITER ///

CREATE TRIGGER ins_sum AFTER UPDATE ON foo FOR EACH ROW BEGIN IF NEW.ts <> OLD.ts THEN
INSERT INTO bar (a, b) VALUES(NEW.a, NEW.b); END IF; END; ///

DELIMITER ;

select * from foo; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 1 | 1 | 2011-06-14 09:29:46 | | 2 | 2 | 2011-06-14 09:29:46 | | 3 | 3 | 2011-06-14 09:29:46 | +------+------+---------------------+ 3 rows in set (0.00 sec)

-- UPDATE without change UPDATE foo SET b = 3 WHERE a = 3; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

-- the timestamo didnt change select * from foo WHERE a = 3; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 3 | 3 | 2011-06-14 09:29:46 | +------+------+---------------------+ 1 rows in set (0.00 sec)

-- the trigger didn't run select * from bar; Empty set (0.00 sec)

-- UPDATE with change UPDATE foo SET b = 4 WHERE a=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

-- the timestamp changed select * from foo; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 1 | 1 | 2011-06-14 09:29:46 | | 2 | 2 | 2011-06-14 09:29:46 | | 3 | 4 | 2011-06-14 09:34:59 | +------+------+---------------------+ 3 rows in set (0.00 sec)

-- and the trigger ran select * from bar; +------+------+---------------------+ | a | b | ts | +------+------+---------------------+ | 3 | 4 | 2011-06-14 09:34:59 | +------+------+---------------------+ 1 row in set (0.00 sec) 由于mysql在处理时间戳方面的行为，因此可以正常工作。仅当更新中发生更改时，时间戳才会更新。

文档在这里：https : //dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html来源：stack overflow