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);
  19. mysql>SELECT*fromtcount_tbl;
  20. +-----------------+----------------+
  21. |tutorial_author|tutorial_count|
  22. +-----------------+----------------+
  23. |mahran|20|
  24. |mahnaz|NULL|
  25. |Jen|NULL|
  26. |Gill|20|
  27. +-----------------+----------------+
  28. 4rowsinset(0.00sec)
  29. 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 形式.

相关广告
  • MySQL查询NULL值处理函数详解 MySQL查询NULL值处理函数详解 MySQL查询NULL值处理函数详解
相关阅读

MySQL查询NULL值处理函数详解

2019/10/10 17:36:19 | 谷歌SEO算法 | Dropbox