玩转Mysql系列 - 第13篇：细说NULL导致的神坑，让人防不胜防

打算提升sql技能的，可以加我微信itsoku，带你成为sql高手。

这是Mysql系列第11篇。

环境：mysql5.7.25，cmd命令中进行演示。

当数据的值为NULL的时候，可能出现各种意想不到的效果，让人防不胜防，我们来看看NULL导致的各种神坑，如何避免？

比较运算符中使用NULL

认真看下面的效果

  1. mysql> select 1>NULL;
  2. +--------+
  3. | 1>NULL |
  4. +--------+
  5. |   NULL |
  6. +--------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select 1<NULL;
  10. +--------+
  11. | 1<NULL |
  12. +--------+
  13. |   NULL |
  14. +--------+
  15. 1 row in set (0.00 sec)
  16.  
  17. mysql> select 1<>NULL;
  18. +---------+
  19. | 1<>NULL |
  20. +---------+
  21. |    NULL |
  22. +---------+
  23. 1 row in set (0.00 sec)
  24.  
  25. mysql> select 1>NULL;
  26. +--------+
  27. | 1>NULL |
  28. +--------+
  29. |   NULL |
  30. +--------+
  31. 1 row in set (0.00 sec)
  32.  
  33. mysql> select 1<NULL;
  34. +--------+
  35. | 1<NULL |
  36. +--------+
  37. |   NULL |
  38. +--------+
  39. 1 row in set (0.00 sec)
  40.  
  41. mysql> select 1>=NULL;
  42. +---------+
  43. | 1>=NULL |
  44. +---------+
  45. |    NULL |
  46. +---------+
  47. 1 row in set (0.00 sec)
  48.  
  49. mysql> select 1<=NULL;
  50. +---------+
  51. | 1<=NULL |
  52. +---------+
  53. |    NULL |
  54. +---------+
  55. 1 row in set (0.00 sec)
  56.  
  57. mysql> select 1!=NULL;
  58. +---------+
  59. | 1!=NULL |
  60. +---------+
  61. |    NULL |
  62. +---------+
  63. 1 row in set (0.00 sec)
  64.  
  65. mysql> select 1<>NULL;
  66. +---------+
  67. | 1<>NULL |
  68. +---------+
  69. |    NULL |
  70. +---------+
  71. 1 row in set (0.00 sec)
  72.  
  73. mysql> select NULL=NULL,NULL!=NULL;
  74. +-----------+------------+
  75. | NULL=NULL | NULL!=NULL |
  76. +-----------+------------+
  77. |      NULL |       NULL |
  78. +-----------+------------+
  79. 1 row in set (0.00 sec)
  80.  
  81. mysql> select 1 in (null),1 not in (null),null in (null),null not in (null);
  82. +-------------+-----------------+----------------+--------------------+
  83. | 1 in (null) | 1 not in (null) | null in (null) | null not in (null) |
  84. +-------------+-----------------+----------------+--------------------+
  85. |        NULL |            NULL |           NULL |               NULL |
  86. +-------------+-----------------+----------------+--------------------+
  87. 1 row in set (0.00 sec)
  88.  
  89. mysql> select 1=any(select null),null=any(select null);
  90. +--------------------+-----------------------+
  91. | 1=any(select null) | null=any(select null) |
  92. +--------------------+-----------------------+
  93. |               NULL |                  NULL |
  94. +--------------------+-----------------------+
  95. 1 row in set (0.00 sec)
  96.  
  97. mysql> select 1=all(select null),null=all(select null);
  98. +--------------------+-----------------------+
  99. | 1=all(select null) | null=all(select null) |
  100. +--------------------+-----------------------+
  101. |               NULL |                  NULL |
  102. +--------------------+-----------------------+
  103. 1 row in set (0.00 sec)

结论：任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all）比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。

准备数据 

  1. mysql> create table test1(a int,b int);
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. mysql> insert into test1 values (1,1),(1,null),(null,null);
  5. Query OK, 3 rows affected (0.00 sec)
  6. Records: 3  Duplicates: 0  Warnings: 0
  7.  
  8. mysql> select * from test1;
  9. +------+------+
  10. | a    | b    |
  11. +------+------+
  12. |    1 |    1 |
  13. |    1 | NULL |
  14. | NULL | NULL |
  15. +------+------+
  16. 3 rows in set (0.00 sec)

上面3条数据，认真看一下，特别是注意上面NULL的记录。

IN、NOT IN和NULL比较

IN和NULL比较

  1. mysql> select * from test1;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    1 |    1 |
  6. |    1 | NULL |
  7. | NULL | NULL |
  8. +------+------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> select * from test1 where a in (null);
  12. Empty set (0.00 sec)
  13.  
  14. mysql> select * from test1 where a in (null,1);
  15. +------+------+
  16. | a    | b    |
  17. +------+------+
  18. |    1 |    1 |
  19. |    1 | NULL |
  20. +------+------+
  21. 2 rows in set (0.00 sec)

结论：当IN和NULL比较时，无法查询出为NULL的记录。

NOT IN 和NULL比较

  1. mysql> select * from test1 where a not in (1);
  2. Empty set (0.00 sec)
  3.  
  4. mysql> select * from test1 where a not in (null);
  5. Empty set (0.00 sec)
  6.  
  7. mysql> select * from test1 where a not in (null,2);
  8. Empty set (0.00 sec)
  9.  
  10. mysql> select * from test1 where a not in (2);
  11. +------+------+
  12. | a    | b    |
  13. +------+------+
  14. |    1 |    1 |
  15. |    1 | NULL |
  16. +------+------+
  17. 2 rows in set (0.00 sec)

结论：当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。

EXISTS、NOT EXISTS和NULL比较 

  1. mysql> select * from test2;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    1 |    1 |
  6. |    1 | NULL |
  7. | NULL | NULL |
  8. +------+------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> select * from test1 t1 where exists (select * from test2 t2 where t1.a = t2.a);
  12. +------+------+
  13. | a    | b    |
  14. +------+------+
  15. |    1 |    1 |
  16. |    1 | NULL |
  17. +------+------+
  18. 2 rows in set (0.00 sec)
  19.  
  20. mysql> select * from test1 t1 where not exists (select * from test2 t2 where t1.a = t2.a);
  21. +------+------+
  22. | a    | b    |
  23. +------+------+
  24. | NULL | NULL |
  25. +------+------+
  26. 1 row in set (0.00 sec)

上面我们复制了表test1创建了表test2。

查询语句中使用exists、not exists对比test1.a=test2.a，因为=不能比较NULL，结果和预期一致。

判断NULL只能用IS NULL、IS NOT NULL 

  1. mysql> select 1 is not null;
  2. +---------------+
  3. | 1 is not null |
  4. +---------------+
  5. |             1 |
  6. +---------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select 1 is null;
  10. +-----------+
  11. | 1 is null |
  12. +-----------+
  13. |         0 |
  14. +-----------+
  15. 1 row in set (0.00 sec)
  16.  
  17. mysql> select null is null;
  18. +--------------+
  19. | null is null |
  20. +--------------+
  21. |            1 |
  22. +--------------+
  23. 1 row in set (0.00 sec)
  24.  
  25. mysql> select null is not null;
  26. +------------------+
  27. | null is not null |
  28. +------------------+
  29. |                0 |
  30. +------------------+
  31. 1 row in set (0.00 sec)

看上面的效果，返回的结果为1或者0。

结论：判断是否为空只能用IS NULL、IS NOT NULL。

聚合函数中NULL的坑

示例

  1. mysql> select count(a),count(b),count(*) from test1;
  2. +----------+----------+----------+
  3. | count(a) | count(b) | count(*) |
  4. +----------+----------+----------+
  5. |        2 |        1 |        3 |
  6. +----------+----------+----------+
  7. 1 row in set (0.00 sec)

count(a)返回了2行记录，a字段为NULL的没有统计出来。

count(b)返回了1行记录，为NULL的2行记录没有统计出来。

count(*)可以统计所有数据，不论字段的数据是否为NULL。

再继续看

  1. mysql> select * from test1 where a is null;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. | NULL | NULL |
  6. +------+------+
  7. 1 row in set (0.00 sec)
  8.  
  9. mysql> select count(a) from test1 where a is null;
  10. +----------+
  11. | count(a) |
  12. +----------+
  13. |        0 |
  14. +----------+
  15. 1 row in set (0.00 sec)

上面第1个sql使用is null查询出了结果，第2个sql中count(a)返回的是0行。

结论：count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。

NULL不能作为主键的值 

  1. mysql> create table test3(a int primary key,b int);
  2. Query OK, 0 rows affected (0.01 sec)
  3.  
  4. mysql> insert into test3 values (null,1);
  5. ERROR 1048 (23000): Column 'a' cannot be null

上面我们创建了一个表test3，字段a未指定不能为空，插入了一条NULL的数据，报错原因：a 字段的值不能为NULL，我们看一下表的创建语句：

  1. mysql> show create table test3;
  2. +-------+------------+
  3. | Table | Create Table      |
  4. +-------+------------+
  5. | test3 | CREATE TABLE `test3` (
  6.   `a` int(11) NOT NULL,
  7.   `b` int(11) DEFAULT NULL,
  8.   PRIMARY KEY (`a`)
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  10. +-------+------------+
  11. 1 row in set (0.00 sec)

从上面的脚本可以看出，当字段为主键的时候，字段会自动设置为not null。

结论：当字段为主键的时候，字段会自动设置为not null。

看了上面这些还是比较晕，NULL的情况确实比较难以处理，容易出错，最有效的方法就是避免使用NULL。所以，强烈建议创建字段的时候字段不允许为NULL，设置一个默认值。

总结

  • NULL作为布尔值的时候，不为1也不为0

  • 任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all），返回值都为NULL

  • 当IN和NULL比较时，无法查询出为NULL的记录

  • 当NOT IN 后面有NULL值时，不论什么情况下，整个sql的查询结果都为空

  • 判断是否为空只能用IS NULL、IS NOT NULL

  • count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行

  • 当字段为主键的时候，字段会自动设置为not null

  • NULL导致的坑让人防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值

Mysql系列目录

  1. 第1篇：mysql基础知识

  2. 第2篇：详解mysql数据类型（重点）

  3. 第3篇：管理员必备技能(必须掌握)

  4. 第4篇：DDL常见操作

  5. 第5篇：DML操作汇总（insert,update,delete）

  6. 第6篇：select查询基础篇

  7. 第7篇：玩转select条件查询，避免采坑

  8. 第8篇：详解排序和分页(order by & limit)

  9. 第9篇：分组查询详解（group by & having）

  10. 第10篇：常用的几十个函数详解

  11. 第11篇：深入了解连接查询及原理

  12. 第12篇：子查询（非常重要，高手必备）

mysql系列大概有20多篇，喜欢的请关注一下，欢迎大家加我微信itsoku或者留言交流mysql相关技术!

640?wx_fmt=jpeg

来源：https://itsoku.blog.csdn.net/article/details/101444352

posted @ 2022-04-23 22:13  程序员小明1024  阅读(68)  评论(0)  编辑  收藏  举报