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()函数.

试试下面的例子,代码如下:

1. root@host#mysql-uroot-ppassword;
2. Enterpassword:*******
3. mysql>useTUTORIALS;
4. Databasechanged
5. mysql>createtabletcount_tbl
6. ->(
7. ->tutorial_authorvarchar(40)NOTNULL,
8. ->tutorial_countINT
9. ->);
10. QueryOK,0rowsaffected(0.05sec)
11. mysql>INSERTINTOtcount_tbl
12. ->(tutorial_author,tutorial_count)values('mahran',20);
13. mysql>INSERTINTOtcount_tbl
14. ->(tutorial_author,tutorial_count)values('mahnaz',NULL);
15. mysql>INSERTINTOtcount_tbl
16. ->(tutorial_author,tutorial_count)values('Jen',NULL);
17. mysql>INSERTINTOtcount_tbl
18. ->(tutorial_author,tutorial_count)values('Gill',20);
20. mysql>SELECT*fromtcount_tbl;
21. +-----------------+----------------+
22. |tutorial_author|tutorial_count|
23. +-----------------+----------------+
24. |mahran|20|
25. |mahnaz|NULL|
26. |Jen|NULL|
27. |Gill|20|
28. +-----------------+----------------+
29. 4rowsinset(0.00sec)
31. mysql>

可以看到=和!=不使用NULL值,如下所示:

1. mysql>SELECT*FROMtcount_tblWHEREtutorial_count=NULL;
2. Emptyset(0.00sec)
3. mysql>SELECT*FROMtcount_tblWHEREtutorial_count!=NULL;
4. Emptyset(0.01sec)

要找到,其中tutorial_count列是或不是NULL的记录,查询应该这样写:

1. mysql>SELECT*FROMtcount_tbl
2. ->WHEREtutorial_countISNULL;
3. +-----------------+----------------+
4. |tutorial_author|tutorial_count|
5. +-----------------+----------------+
6. |mahnaz|NULL|
7. |Jen|NULL|
8. +-----------------+----------------+
9. 2rowsinset(0.00sec)
10. mysql>SELECT*fromtcount_tbl
11. ->WHEREtutorial_countISNOTNULL;
12. +-----------------+----------------+
13. |tutorial_author|tutorial_count|
14. +-----------------+----------------+
15. |mahran|20|
16. |Gill|20|
17. +-----------------+----------------+
18. 2rowsinset(0.00sec)

子查询 NOT IN 与 NOT EXISTS　中的NULL,有些情况下　NOT IN　形式的子查询返回空结果集,但是将其改写为 NOT EXISTS　形式后则恢复正常,如下所示.

建表,代码如下:

1. mysql>CREATETABLEt2(col1intdefaultNULL,col2intdefaultNULL);
2. QueryOK,0rowsaffected(0.01sec)
3. mysql>CREATETABLEt3(col1intdefaultNULL,col2intdefaultNULL);
4. QueryOK,0rowsaffected(0.01sec)

加入数据,代码如下:

1. mysql>INSERTINTOt2VALUES(1,2),(1,3);
2. QueryOK,2rowsaffected(0.00sec)
3. Records:2Duplicates:0Warnings:0
4. mysql>INSERTINTOt3VALUES(1,2),(1,NULL);
5. QueryOK,2rowsaffected(0.00sec)
6. Records:2Duplicates:0Warnings:0

执行如下查询,代码如下:

1. mysql>SELECT*FROMt2WHEREcol2NOTIN(SELECTcol2FROMt3);
2. Emptyset(0.00sec)
3. mysql>SELECT*FROMt2WHERENOTEXISTS(SELECT1FROMt3WHEREt3.col2=t2.col2);//phpfensi.com
4. +------+------+
5. |col1|col2|
6. +------+------+
7. |1|3|
8. +------+------+
9. 1rowinset(0.00sec)

为什么会这样呢？这要从MySQL数据库NULL的特殊性说起:在MySQL中有三种状态：True、False、Unknown，任何NULL的比较操作都是Unknown状态,如下所示:

1. mysql>SELECT1=NULL,1<>NULL,1<NULL,1>NULL;
2. +----------+-----------+----------+----------+
3. |1=NULL|1<>NULL|1<NULL|1>NULL| //phpfensi.com
4. +----------+-----------+----------+----------+
5. |NULL|NULL|NULL|NULL|
6. +----------+-----------+----------+----------+
7. 1rowinset(0.00sec)

而且所有的查询条件（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 形式.