mysql更新后触发_仅当行已更改时，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

desc foo;

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+-------------------+-----------------------------+

| a | int(11) | YES | | NULL | |

| b | int(11) | YES | | NULL | |

| ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------+-----------+------+-----+-------------------+-----------------------------+

2020-05-17