更新触发后仅在数据更改时触发的MySQL中的NULL处理 [英] NULL Handling in MySQL After Update Trigger that Fires Only on Data Change

问题描述

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

Due to reputation constraints, this is a follow-up question to this answer to a prior question. I'd like to know if there's a more efficient way to test each field for changes while handling NULL values.

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不会导致INSERT:

I want a trigger whose actions occur after update only if a value is changed by the update. So this UPDATE won't result in an INSERT:

UPDATE foo SET a = 1 WHERE b = 2;

但是此更新将导致插入:

But this UPDATE will result in an INSERT:

UPDATE foo SET a = 2 WHERE b = 2;

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

I have written this trigger to handle that, however I'm hoping the IF statement can be simplified.

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语句可以简化吗?还是有一个更简单的整体解决方案?

Can the IF statement be simplified? Or is there an easier overall solution?

推荐答案

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

You could use coalesce(), which returns the first of its arguments that is 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值的常见情况.

It can be tricky to choose the second argument. The above example works for the common case when a, b and c are strings and when an empty string value is equivalent to a null value.

这篇关于更新触发后仅在数据更改时触发的MySQL中的NULL处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！