更新后 MySQL 中的 NULL 处理触发器仅在数据更改时触发

Question

由于声誉限制，这是对先前问题的回答的后续问题。 我想知道是否有更有效的方法可以在处理 NULL 值时测试每个字段的更改。

CREATE TABLE foo (
  a INT NULL DEFAULT NULL, 
  b INT NULL DEFAULT NULL,
  c INT NULL DEFAULT NULL
);
CREATE TABLE bar (
  a INT NULL DEFAULT NULL,
  b INT NULL DEFAULT NULL,
  c INT NULL DEFAULT NULL
);
INSERT INTO foo (a, b) VALUES (1, 2);

我想要一个触发器，只有在更新更改值时，其操作才会在更新后发生。 因此，此更新不会导致插入：

UPDATE foo SET a = 1 WHERE b = 2;

但是此更新将导致插入：

UPDATE foo SET a = 2 WHERE b = 2;

我已经编写了这个触发器来处理这个问题，但是我希望 IF 语句可以简化。

DELIMITER ///
CREATE TRIGGER t_after_update_foo
AFTER UPDATE ON foo
FOR EACH ROW 
  BEGIN
    IF
      ((OLD.a <> NEW.a OR OLD.a IS NULL OR NEW.a IS NULL) AND (NEW.a IS NOT NULL OR OLD.a IS NOT NULL))
      OR
      ((OLD.b <> NEW.b OR OLD.b IS NULL OR NEW.b IS NULL) AND (NEW.b IS NOT NULL OR OLD.b IS NOT NULL))
      OR
      ((OLD.c <> NEW.c OR OLD.c IS NULL OR NEW.c IS NULL) AND (NEW.c IS NOT NULL OR OLD.c IS NOT NULL))
    THEN
      INSERT INTO bar (a, b, c) VALUES (NEW.a, NEW.b, NEW.c);
    END IF;
  END;
///
DELIMITER ;

IF语句可以简化吗？ 还是有更简单的整体解决方案？

Answer 1

为此

，您可以使用 NULL-safe 等于运算符 <=> 进行比较，然后使用 NOT 否定结果。

所以

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

会成为

!(OLD.a <=> NEW.a)

要检查多列中的任何一列是否已更改，您可以执行

!(OLD.a <=> NEW.a AND OLD.b <=> NEW.b)

---

如果您有很多列要比较，则一个稍短的选择，由@dlauzon提供：

!((OLD.a, OLD.b, OLD.c, ...) <=> (NEW.a, NEW.b, NEW.c, ...))

Answer 2

您可以使用 coalesce() ，它返回其第一个参数，即 not null 。

if coalesce(old.a,'') <> coalesce(new.a,'') or
   coalesce(old.b,'') <> coalesce(new.b,'') or
   coalesce(old.c,'') <> coalesce(new.c,'')
   then
     insert ...;
   end if;

选择第二个参数可能很棘手。 上面的示例适用于 a、b 和 c 是字符串以及空字符串值等效于 null 值的常见情况。