Comparing Null with a Different Value in MySQL Trigger

Question

So here is my problem. I compare new and old values ​​when updating a table row. But the new or old value will sometimes be null. Therefore, the code below does not work. Can I fix this problem?

thanks

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

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 correct.

Answer 2

MySql has a special equality validation operator:

<=>

NULL is safe. This operator performs equality comparisons like the = operator, but returns 1, not NULL if both operands are NULL and 0, and not 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 3

Zeros must be compared with the is null / is not null construct:

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

You must configure this to suit your requirements, as this will return true if it is null or they are not equal to each other. Perhaps you need to handle the case where both values ​​are zero.

Answer 4

You cannot compare with NULL because NULL is defined as a value that never matches in comparison, even other NULLS. Try it yourself if you don’t believe me.

 SELECT NULL = NULL; 

You will need to verify that the specified column is not NULL. You can use the AND or OR operator to incorporate such a test into existing logic.

Answer 5

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

If the OLD.assignedto != NEW.assignedto has one NULL statement, the whole expression gets NULL , forcing COALESCE() to return the next argument instead, which returns the true value that is used when one of them is NULL - the other must be NULL .