null和< =>之间的区别是什么？空值 ？

mysql> SELECT * FROM param WHERE num is null;
+-----+------+
| id  | num  |
+-----+------+
|   8 | NULL |
| 225 | NULL |
+-----+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM param WHERE num<>NULL;
Empty set (0.00 sec)

mysql> SELECT * FROM param WHERE num<=>NULL;
+-----+------+
| id  | num  |
+-----+------+
|   8 | NULL |
| 225 | NULL |
+-----+------+

标准版本的差异？我没有看到参数< =>的实际用途如果它是null

解决方法:

永远不要使用foo = null或foo<>检查空值null或foo！= null

mysql> SELECT 1 <> NULL;
        -> NULL

甚至NULL都不等于NULL！

mysql> SELECT NULL = NULL;
        -> NULL

而是使用以下运算符之一

< =>是Null-Safe 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

另一方面,IS NULL更直接

Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

重要说明：阅读IS NULL documentation以了解sql_auto_is_null设置如何影响此运算符.

另请参阅：IS NOT NULL以测试不等于NULL的值.

您可能也对COALESCE感兴趣.