当前位置:首页 > Mysql教程 > 列表

MySQL查询NULL值处理函数详解

发布:smiling 来源: PHP粉丝网  添加日期:2014-09-29 15:08:42 浏览: 评论:0 

我们已经看到使用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 -u root -p password
  2. Enter password:******* 
  3. mysql> use TUTORIALS; 
  4. Database changed 
  5. mysql> create table tcount_tbl 
  6.     -> ( 
  7.     -> tutorial_author varchar(40) NOT NULL
  8.     -> tutorial_count  INT 
  9.     -> ); 
  10. Query OK, 0 rows affected (0.05 sec) 
  11. mysql> INSERT INTO tcount_tbl 
  12.     -> (tutorial_author, tutorial_count) values ('mahran', 20); 
  13. mysql> INSERT INTO tcount_tbl 
  14.     -> (tutorial_author, tutorial_count) values ('mahnaz'NULL); 
  15. mysql> INSERT INTO tcount_tbl 
  16.     -> (tutorial_author, tutorial_count) values ('Jen'NULL); 
  17. mysql> INSERT INTO tcount_tbl 
  18.     -> (tutorial_author, tutorial_count) values ('Gill', 20); 
  19.  
  20. mysql> SELECT * from tcount_tbl; 
  21. +-----------------+----------------+ 
  22. | tutorial_author | tutorial_count | 
  23. +-----------------+----------------+ 
  24. | mahran          |             20 | 
  25. | mahnaz          |           NULL | 
  26. | Jen             |           NULL | 
  27. | Gill            |             20 | 
  28. +-----------------+----------------+ 
  29. rows in set (0.00 sec) 
  30.  
  31. mysql> 

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

  1. mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL
  2. Empty set (0.00 sec) 
  3. mysql> SELECT * FROM tcount_tbl WHERE tutorial_count != NULL
  4. Empty set (0.01 sec) 

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

  1. mysql> SELECT * FROM tcount_tbl  
  2.     -> WHERE tutorial_count IS NULL
  3. +-----------------+----------------+ 
  4. | tutorial_author | tutorial_count | 
  5. +-----------------+----------------+ 
  6. | mahnaz          |           NULL | 
  7. | Jen             |           NULL | 
  8. +-----------------+----------------+ 
  9. rows in set (0.00 sec) 
  10. mysql> SELECT * from tcount_tbl  
  11.     -> WHERE tutorial_count IS NOT NULL
  12. +-----------------+----------------+ 
  13. | tutorial_author | tutorial_count | 
  14. +-----------------+----------------+ 
  15. | mahran          |             20 | 
  16. | Gill            |             20 | 
  17. +-----------------+----------------+ 
  18. rows in set (0.00 sec) 

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

建表,代码如下:

  1. mysql> CREATE TABLE t2 (col1 int default NULL, col2 int default NULL);    
  2.   Query OK, 0 rows affected (0.01 sec)    
  3.   mysql> CREATE TABLE t3 (col1 int default NULL, col2 int default NULL);    
  4.   Query OK, 0 rows affected (0.01 sec)   

加入数据,代码如下:

  1. mysql> INSERT INTO t2 VALUES (1,2),(1,3);    
  2. Query OK, 2 rows affected (0.00 sec)    
  3. Records: 2 Duplicates: 0 Warnings: 0    
  4. mysql> INSERT INTO t3 VALUES (1,2),(1,NULL);    
  5. Query OK, 2 rows affected (0.00 sec)    
  6. Records: 2 Duplicates: 0 Warnings: 0 

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

  1. mysql> SELECT * FROM t2 WHERE col2 NOT IN (SELECT col2 FROM t3);    
  2.  Empty set (0.00 sec)    
  3.  mysql> SELECT * FROM t2 WHERE NOT EXISTS (SELECT 1 FROM t3 WHERE t3.col2 = t2.col2);   //phpfensi.com 
  4.  +------+------+    
  5.  | col1 | col2 |    
  6.  +------+------+    
  7.  | 1 | 3 |    
  8.  +------+------+    
  9.  1 row in set (0.00 sec) 

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

  1. mysql> SELECT 1 = 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.     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 形式.

Tags: 函数NULL值处理 MySQL空函数

分享到: