The usage of the <=> operator in MySQL and the mysql Operator
Problem:
When I read the code of a previous developer
WHERE p.name <=> NULL
In this query statement, what does the <=> symbol mean? Is it the same as the =? Or a syntax error? However, no errors or exceptions are displayed. I already know <> =! =.
Best Answer:
Similarities with =
Like the regular = operator, two values are compared and the result is 0 (not equal to) or 1 (equal); in other words: 'A' <=> 'B' gets 0 and 'A' <=> 'A' gets 1.
2. Differences between and =
Unlike the = Operator, NULL does not make any sense. Therefore, the = Operator cannot use NULL as a valid result. Therefore, use <=>,
'A' <=> NULL: 0 NULL <=> NULL: 1. In contrast to the = Operator, the = Operator rule is 'A' = NULL, and the result is NULL. By the way, almost all operators and functions in mysql work like this, because it is meaningless to compare with NULL.
Usage
When two operands may contain NULL, you need a consistent statement.
... WHERE col_a <=> ? ...
Here, placeholders may be constants or NULL. When you use the <=> operator, you do not need to modify the query statement.
Related operators
In addition to <=>, there are two other operators used to compare a value with NULL, that IS, is null and is not null. They are part of the ANSI standard and therefore can be used in other databases. And <=> can only be used in mysql.
You can use <=> As a dialect in mysql.
'a' IS NULL ==> 'a' <=> NULL'a' IS NOT NULL ==> NOT('a' <=> NULL)
Accordingly, you can modify the query statement segment to make it more portable:
WHERE p.name IS NULL