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

Question

是否有可能仅在数据确实更改时使用“after update”触发器。 我知道“NEW和OLD”。但是，当使用它们时，我只能比较列。 例如，“NEW.count <> OLD.count”。

但我想要这样的东西：如果“NEW <> OLD”，则运行触发器

一个例子：

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

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

CREATE TRIGGER ins_sum
    AFTER UPDATE ON foo
    FOR EACH ROW
    INSERT INTO bar VALUES(NEW.a, NEW.b);

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


select * from bar;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
+------+------+

问题在于，虽然有更新，但没有任何变化。但触发器仍然运行了。依我之见应该有一种方式可以避免这种情况发生。

我知道对于这个例子我可以使用

IF NOW.b <> OLD.b

来避免这个问题。

但是想象一下一个有许多列正在改变的大型表格。 你必须比较每一列，如果数据库发生变化，则必须调整触发器。 而且硬编码比较每行的每一列并不好：） 补充 如您在此行中所看到的

Rows matched: 1 Changed: 0 Warnings: 0

MySQL 知道该行未更改。但它不与触发器共享此知识。 一个像“AFTER REAL UPDATE”之类的触发器会很酷。

- juwens

---

如果列发生更改，您无论如何都需要调整触发器，因为它使用 INSERT INTO bar VALUES(NEW.a, NEW.b); 进行插入。是否有任何解决方法可以避免这种情况？类似 INSERT INTO bar VALUES(SELECT * FROM foo WHERE…); 这样的语句是否可行？ - zxcat

@ juwens，你对这个问题的回答真是棒极了，值得给你打10+分。真不可思议他们怎么会把行为设计得如此反直觉！ - Ifedi Okonkwo

@zcat，不需要每次表格变化就调整触发器。你可以执行INSERT INTO bar, SELECT * FROM foo WHERE foo.id = OLD.id，这样就可以了。 - Ifedi Okonkwo

顺便提一下，Postgres 也是这样的，但你可以在创建触发器语句中加入 WHEN NEW.* IS DISTINCT FROM OLD.* 条件，所以 MySQL 应该实现类似的东西... - Andy

Answer 1

作为一种解决方法，您可以使用时间戳（旧的和新的）进行检查，因为当行没有更改时，该时间戳不会更新。 （可能这是混淆的源泉？因为该时间戳也被称为“on update”，但在没有更改发生时不会执行。） 一秒钟内的更改将不会执行触发器的该部分，但在某些情况下，这可能是可以接受的（比如当您有一个拒绝快速更改的应用程序时）。

例如，而不是

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 |
+-------+-----------+------+-----+-------------------+-----------------------------+

- Inca

---

我不明白这个怎么可能有效。你能否详细解释一下你的意思？ - juwens

@derkommissar：我添加了一个例子。 - Inca

有没有可用的参考链接？ - Pavan Jaju

如果更新频率低于一秒钟，这种方法将无法工作。时间戳将会改变（但值相同）。您需要使用一个时间戳（6），以便准确地跟踪所有更新。 - Sir Rufo

根据我下面的回答，请不要忘记使用**<=>（空值感知运算符）来跟踪从null到非null的变化和从非null到null的变化**。 - Wax Cage

Answer 2

但是想象一张列会不断变化的大型表格。您必须比较每一列，如果数据库发生更改，您必须调整触发器。并且硬编码比较每一行的感觉也不好 :)

是的，但这是继续前进的方式。

另外，预先进行更新前的检查也是一个好的实践：

UPDATE foo SET b = 3 WHERE a=3 and b <> 3;

在你的例子中，这样做会更新（因此 覆盖）两个 行而不是三个。

- Denis de Bernardy

---

@Denis，这是不必要的，MySQL会检查值是否真正改变，只有在需要时才会启动UPDATE（更新+触发器）。你的检查只会使事情变慢。 - Johan

@Johan：这是必要的，MySQL不会这样做。如果它这样做了，它就不会遵守SQL标准，而且提问者也不会首先提出他的问题。 - Denis de Bernardy

这并不讨厌，而是正常且实际上是可取的：有时，在更新时设置触发器并使其无论是否发生更改都触发是有用的。间接的结果是开发人员（或他的ORM，尽管我知道后者从未做到最好）需要在没有实际更改的情况下不进行更新。 - Denis de Bernardy

@Yohan：你可能会觉得这个讨论很有趣。 - Denis de Bernardy

也许我们可以做一个循环并获得无限的声誉 :-). 只需要几天时间和一些老鼠的磨损就能打败Jon Skeet。 - Johan

Answer 3

由于我无法评论，所以请注意，如果您的列支持NULL值，则OLD.x <> NEW.x是不够的。因为

SELECT IF(1<>NULL,1,0)

返回的结果为0，与之相同的是

NULL<>NULL 1<>NULL 0<>NULL 'AAA'<>NULL

因此，它将不会跟踪从NULL到NULL的更改。

在这种情况下的正确方式是：

((OLD.x IS NULL AND NEW.x IS NOT NULL) OR (OLD.x IS NOT NULL AND NEW.x IS NULL) OR (OLD.x<>NEW.x))

- Wax Cage

---

感谢您宝贵的答案/提示！这使得时间戳解决方案更加有吸引力，而比较解决方案实际上无法使用。 - juwens

或者您可以使用 COALESCE() 函数，它返回其参数中第一个非 NULL 的值。因此，您可以将其编写为 IF COALESCE(OLD.X,'') <> COALESCE(NEW.X,'')。 - Lyman Zerga

或者你可以使用MySQL的空值比较运算符<=>。 - djmj

Answer 4

您可以通过使用NULL安全相等运算符<=>比较每个字段，然后使用NOT否定结果来实现此目的。

完整触发器将变为：

DROP TRIGGER IF EXISTS `my_trigger_name`;

DELIMITER $$

CREATE TRIGGER `my_trigger_name` AFTER UPDATE ON `my_table_name` FOR EACH ROW 
    BEGIN
        /*Add any fields you want to compare here*/
        IF !(OLD.a <=> NEW.a AND OLD.b <=> NEW.b) THEN
            INSERT INTO `my_other_table` (
                `a`,
                 `b`
            ) VALUES (
                NEW.`a`,
                NEW.`b`
            );
        END IF;
    END;$$

DELIMITER ;

(基于我另一个答案。)

- user2428118

Answer 5

使用以下查询来查看哪些行发生了更改：

(select * from inserted) except (select * from deleted)

这个查询的结果应包括所有与旧记录不同的新记录。

- Hawthorne

---

很遗憾，这并没有回答问题。更新操作不会删除行，而且OP试图阻止触发器在更新未更改任何数据的情况下触发（如果将列的值从1更改为1，则不会更改数据，但是更新操作仍然运行，因此触发器也会运行）。 - Takarii

据我理解，UPDATE查询将在已删除的记录集中放置旧值，并在插入的记录集中放置新值。虽然这不会阻止触发器运行，但它可以用于防止触发器执行任何操作，通常是足够的。 - Hawthorne

可能值得用扩展代码来说明您的答案。但请记住，这个问题已经五年了。 - Takarii

这是来自Microsoft的一句话：“更新事务类似于先执行删除操作，然后再执行插入操作；首先将旧行复制到已删除表中，然后将新行复制到触发器表和插入表中。” - Hawthorne

示例代码：CREATE TRIGGER ins_sum AFTER UPDATE ON foo INSERT INTO bar VALUES(select * from inserted except select * from deleted); - Hawthorne

Answer 6

如果有新插入的行受到影响，那么它将在数据库中的不同表中进行更新。

DELIMITER $$

CREATE TRIGGER "give trigger name" AFTER INSERT ON "table name" 
FOR EACH ROW
BEGIN
    INSERT INTO "give table name you want to add the new insertion on previously given table" (id,name,age) VALUES (10,"sumith",24);
END;
$$
DELIMITER ;

- sumith madhushan

Answer 7

MYSQL TRIGGER BEFORE UPDATE IF OLD.a<>NEW.b

USE `pdvsa_ent_aycg`;

DELIMITER $$

CREATE TRIGGER `cisterna_BUPD` BEFORE UPDATE ON `cisterna` FOR EACH ROW

BEGIN

IF OLD.id_cisterna_estado<>NEW.id_cisterna_estado OR OLD.observacion_cisterna_estado<>NEW.observacion_cisterna_estado OR OLD.fecha_cisterna_estado<>NEW.fecha_cisterna_estado

    THEN 

        INSERT INTO cisterna_estado_modificaciones(nro_cisterna_estado, id_cisterna_estado, observacion_cisterna_estado, fecha_cisterna_estado) values (NULL, OLD.id_cisterna_estado, OLD.observacion_cisterna_estado, OLD.fecha_cisterna_estado); 

    END IF;

END

- Herwin Rey

Answer 8

以下是两个有趣的死胡同（截至MySQL 5.7）-

1. new.*和old.*结构是无效的，MySQL会抱怨Unknown table 'new'或syntax to use near '*，这排除了像select ... from (select (select new.* union select old.*)a having count(*)=2) has_change这样的技巧。

2. “ROW_COUNT()”文档提供了一个有用的线索-

对于UPDATE语句，默认情况下受影响的行数值是实际更改的行数

事实上，在更新语句之后，ROW_COUNT()正确显示了从更新中更改的行数。然而，在触发器内部，在更新期间，ROW_COUNT() = 0总是成立。该函数在行级触发器中没有有用的价值，在MySQL中还没有语句级触发器。

希望这个“空结果”能够避免未来的挫败感。

- Yary