Comparing a Null to another value in MySQL Trigger

Question

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?

Thank You

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

Answer 1

MySql has a special null-safe equality check operator:

<=>

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

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

You can use this operator with the NOT operator:

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

So, in your case you should write:

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

Answer 2

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

The comment is very much correct.