在MySQL触发器中比较空值和另一个值

Question

以下是我的问题：当更新表格行时，我正在比较新旧值。但是新值或旧值有时会为空。因此，下面的代码不起作用。我该如何解决这个问题？

谢谢你！

BEFORE UPDATE ON mytable
   FOR EACH ROW
BEGIN
        IF OLD.assignedto != NEW.assignedto
        THEN
                INSERT INTO history
                    (
                        asset    ,
                        changedfield     ,
                        oldvalue    ,
                        newvalue      
                    )
                    VALUES
                    (
                        NEW.asset,
                        'assignedto',
                        OLD.assignedto,
                        NEW.assignedto
                    );
        END IF;
    END$$

- dan

Answer 1

MySql有一个特殊的空值安全等式运算符：

<=>

空值安全相等。该运算符执行类似于=运算符的相等比较，但是如果两个操作数都为NULL，则返回1而不是NULL；如果一个操作数为NULL，则返回0而不是NULL。

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

您可以将此运算符与 NOT 运算符一起使用：

mysql> SELECT NOT (1 <=> 1), NOT (NULL <=> NULL), NOT (1 <=> NULL);
        -> 0, 0, 1

因此，在您的情况下，您应该编写：

IF NOT (OLD.assignedto <=> NEW.assignedto)

- nightcoder

---

这个问题的答案非常有用！+1 - djmj

Answer 2

尝试：

IF (   (OLD.assignedto IS NOT NULL AND NEW.assignedto IS NOT NULL 
             AND OLD.assignedto != NEW.assignedto)
    OR (OLD.assignedto IS NULL AND NEW.assignedto IS NOT NULL)
    OR (OLD.assignedto IS NOT NULL AND NEW.assignedto IS NULL)
   )

评论非常正确。

- Chris Cunningham

---

NULL 或 <foo> 或 <bar> 将会是 NULL。你需要使用 COALESCE() 函数来处理。 - glglgl

你的评论是正确的，除了你不一定非得使用 COALESCE()。话虽如此，合并答案也是不错的选择。 - Chris Cunningham

谢谢您的快速回复。这最终对我有用。现在我会知道如何做了。 - dan

@dan 你真的应该研究一下 COALESCE -- 不要忽略其他答案！ - Chris Cunningham

我会看一下。你的解决方案对我来说最有意义。 - dan

Answer 3

你不能与NULL进行比较，因为NULL被定义为永远不会在比较中匹配的值，即使是其他的NULL。如果你不相信我，可以自己试试。

SELECT NULL = NULL;

您需要使用 IS NOT NULL 来检查所涉及的列是否为空。您可以使用 AND 或 OR 运算符将此类测试合并到您已有的逻辑中。

- GordonM

Answer 4

IF COALESCE(OLD.assignedto != NEW.assignedto, OLD.assignedto IS NULL AND NEW.assignedto IS NULL)

如果 OLD.assignedto != NEW.assignedto 部分有一个运算符 NULL，整个表达式将变为 NULL，这使得 COALESCE() 返回下一个参数，以便在其中一个为 NULL 时返回要使用的真值 - 另一个也必须为 NULL。

- glglgl

Answer 5

我知道这已经过时了，但是有一种稍微更清晰的编写条件的方式：

IF COALESCE(OLD.assignedto, 0) <> COALESCE(NEW.assignedto, 0) THEN ...

这样你就告诉引擎用0替换NULL，这样条件就不会因为与NULL比较而失败了。

- AFMeirelles

Answer 6

空值必须使用is null/is not null结构进行比较：

if ((old.assignedto != new.assignedto) or (old.assignedto is null) or (new.assignedto is null))

您需要根据自己的要求进行调整，因为如果两个值中有任意一个为空或它们不相等，则会返回 true。也许您需要处理两个值均为空的情况。

- Marc B