在MySQL触发器中将Null与另一个值进行比较 [英] Comparing a Null to another value in MySQL Trigger

问题描述

所以这里是我的问题，我比较新的和旧的值更新表行时。但新的或旧的值有时会为null。所以下面的代码不工作。我可以解决这个问题吗？

So here is my issue I am comparing new and old values when a table row is being updated. But the new or old value will sometimes be null. So the code below doesn't work. Can can I remedy this issue?

谢谢

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

推荐答案

p>

Try:

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

注释是非常正确的。

这篇关于在MySQL触发器中将Null与另一个值进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！