MySQL查询NULL值处理函数详解
我们已经看到使用WHERE子句的SQL SELECT命令来从MySQL表获取数据。但是，当我们试图给的条件比较字段或列的值为NULL，它不能正常工作。
为了处理这种情况，MySQL提供了三大运算符
IS NULL: 此运算符返回true，当列的值是NULL。
IS NOT NULL: 运算符返回true，当列的值不是NULL。
<=> 操作符比较值（不同于=运算符）为ture，即使两个NULL值
涉及NULL条件是特殊的。不能使用 =NULL 或 !=NULL 寻找NULL值的列。这种比较总是告诉他们是否是真正的失败，因为这是不可能的。即使是NULL=NULL失败。
如果要查找是或不是NULL的列，请使用IS NULL或IS NOT NULL。
如果你想要寻找值是NULL的列，你不能使用=NULL测试。下列语句不返回任何行，因为对任何表达式，
expr = NULL是假的：
| 代码如下 | |
|
mysql> SELECT * FROM my_table WHERE phone = NULL; |
|
要想寻找NULL值，你必须使用IS NULL测试。下例显示如何找出NULL电话号码和空的电话号码：
| 代码如下 | |
|
mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ""; |
|
为了有助于NULL的处理，你能使用IS NULL和IS NOT NULL运算符和IFNULL()函数。
例子:
试试下面的例子：
| 代码如下 | |
|
root@host# mysql -u root -p password; mysql> SELECT * from tcount_tbl; mysql> |
|
可以看到=和!=不使用NULL值，如下所示：
| 代码如下 | |
| mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL; Empty set (0.01 sec) |
|
要找到，其中tutorial_count列是或不是NULL的记录，查询应该这样写：
| 代码如下 | |
|
mysql> SELECT * FROM tcount_tbl |
|
子查询 NOT IN 与 NOT EXISTS 中的NULL
有些情况下 NOT IN 形式的子查询返回空结果集，但是将其改写为 NOT EXISTS 形式后则恢复正常，如下所示：
建表：
| 代码如下 | |
| mysql> CREATE TABLE t2 (col1 int default NULL, col2 int default NULL); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t3 (col1 int default NULL, col2 int default NULL); Query OK, 0 rows affected (0.01 sec) |
|
加入数据：
| 代码如下 | |
|
mysql> INSERT INTO t2 VALUES (1,2),(1,3); |
|
执行如下查询：
| 代码如下 | |
| mysql> SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3); Empty set (0.00 sec) mysql> SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2); +------+------+ | col1 | col2 | +------+------+ | 1 | 3 | +------+------+ 1 row in set (0.00 sec) |
|
为什么会这样呢？这要从MySQL数据库NULL的特殊性说起：
在MySQL中有三种状态：True、False、Unknown，任何NULL的比较操作都是Unknown状态，如下所示：
| 代码如下 | |
| mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 1 row in set (0.00 sec) |
|
而且所有的查询条件（ON, WHERE, HAVING）都是将Unknown状态当做False处理。
所以第一条查询的查询田间等同于：col2 NOT IN (2, NULL) => col2 <> 2 AND col2 <> NULL => true AND Unknow => Unknow => False
查询条件永为False，故该查询没有返回结果。
而 NOT EXISTS 是循环执行的
他首先执行 SELECT 1 FROM t3 WHERE t3.col2 = 2
返回了结果，经 NOT EXISTS 操作后查询条件为 False，故不做任何输出，
接下来执行 SELECT 1 FROM t3 WHERE t3.col2 = 3
无返回结果。经 NOT EXISTS 操作后查询条件为 True，于是输出本次查询结果。
所以，如果当一个 NOT IN 子查询没有返回结果的时候，应该特别注意内层查询的结果集是否包含空值，若包含的话，应尝试将查询改写为 NOT EXISTS 形式。
