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

2021-11-20 00:00:00 sql database mysql triggers

是否有可能仅在数据已真正更改的情况下才使用更新后"触发器.我知道新旧".但是在使用它们时，我只能比较列.例如NEW.count <> OLD.count".

Is there any possibility to use an "after update" trigger only in the case the data has been REALLY changed. I know of "NEW and OLD". But when using them I'm only able to compare columns. For example "NEW.count <> OLD.count".

但我想要类似的东西:如果NEW <> OLD"运行触发器

But I want something like: run trigger if "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 |
+------+------+

重点是，有更新，但什么都没有改变.但是触发器还是运行了.恕我直言，应该有办法没有.

The point is, there was an update, but nothing has changed. But the trigger ran anyway. IMHO there should be a way it doesn't.

我知道我可以使用

如果现在.b <> 旧.b

IF NOW.b <> OLD.b

对于这个例子.

但是想象一个带有不断变化的列的大表.您必须比较每一列，如果数据库发生变化，您必须调整触发器.并且比较硬编码的行的每一列感觉"不好:)

BUT imagine a large table with changing columns. You have to compare every column and if the database changes you have to adjust the trigger. AND it doesn't "feel" good to compare every column of the row hardcoded :)

添加

正如你在网上看到的

匹配的行数:1 更改:0 警告:0

Rows matched: 1 Changed: 0 Warnings: 0

MySQL 知道该行没有改变.但它不与触发器共享此知识.像AFTER REAL UPDATE"这样的触发器或类似的东西会很酷.

MySQL knows that the line didn't change. But it doesn't share this knowledge with the trigger. A trigger like "AFTER REAL UPDATE" or something like this would be cool.

推荐答案

作为一种解决方法，您可以使用时间戳(旧的和新的)进行检查，当有时间戳时 不会 更新行没有变化.(可能这就是混淆的根源?因为那个也被称为更新时"，但在没有发生变化时不会执行)一秒钟内的更改将不会执行触发器的那部分，但在某些情况下可能没问题(例如，当您的应用程序无论如何都拒绝快速更改时.)

As a workaround, you could use the timestamp (old and new) for checking though, that one is not updated when there are no changes to the row. (Possibly that is the source for confusion? Because that one is also called 'on update' but is not executed when no change occurs) Changes within one second will then not execute that part of the trigger, but in some cases that could be fine (like when you have an application that rejects fast changes anyway.)

例如，而不是

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

然后您不必每次更新方案时都更改触发器(您在问题中提到的问题.)

Then you don't have to change your trigger every time you update the scheme (the issue you mentioned in the question.)

添加完整示例

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 在处理时间戳方面的行为.只有在更新中发生更改时才会更新时间戳.

It is working because of mysql's behavior on handling timestamps. The time stamp is only updated if a change occured in the updates.

文档在这里:
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 |
+-------+-----------+------+-----+-------------------+-----------------------------+

相关文章