第13篇：细说NULL导致的神坑，让人防不胜防

star2017 1年前 ⋅ 553 阅读

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

这是Mysql系列第13篇。

环境：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)
  9. mysql> select 1<NULL;
  10. +--------+
  11. | 1<NULL |
  12. +--------+
  13. |   NULL |
  14. +--------+
  15. 1 row in set (0.00 sec)
  17. mysql> select 1<>NULL;
  18. +---------+
  19. | 1<>NULL |
  20. +---------+
  21. |    NULL |
  22. +---------+
  23. 1 row in set (0.00 sec)
  25. mysql> select 1>NULL;
  26. +--------+
  27. | 1>NULL |
  28. +--------+
  29. |   NULL |
  30. +--------+
  31. 1 row in set (0.00 sec)
  33. mysql> select 1<NULL;
  34. +--------+
  35. | 1<NULL |
  36. +--------+
  37. |   NULL |
  38. +--------+
  39. 1 row in set (0.00 sec)
  41. mysql> select 1>=NULL;
  42. +---------+
  43. | 1>=NULL |
  44. +---------+
  45. |    NULL |
  46. +---------+
  47. 1 row in set (0.00 sec)
  49. mysql> select 1<=NULL;
  50. +---------+
  51. | 1<=NULL |
  52. +---------+
  53. |    NULL |
  54. +---------+
  55. 1 row in set (0.00 sec)
  57. mysql> select 1!=NULL;
  58. +---------+
  59. | 1!=NULL |
  60. +---------+
  61. |    NULL |
  62. +---------+
  63. 1 row in set (0.00 sec)
  65. mysql> select 1<>NULL;
  66. +---------+
  67. | 1<>NULL |
  68. +---------+
  69. |    NULL |
  70. +---------+
  71. 1 row in set (0.00 sec)
  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)
  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)
  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)
  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)
  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
  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)
  11. mysql> select * from test1 where a in (null);
  12. Empty set (0.00 sec)
  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)
  4. mysql> select * from test1 where a not in (null);
  5. Empty set (0.00 sec)
  7. mysql> select * from test1 where a not in (null,2);
  8. Empty set (0.00 sec)
  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)
  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)
  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)
  9. mysql> select 1 is null;
  10. +-----------+
  11. | 1 is null |
  12. +-----------+
  13. |         0 |
  14. +-----------+
  15. 1 row in set (0.00 sec)
  17. mysql> select null is null;
  18. +--------------+
  19. | null is null |
  20. +--------------+
  21. |            1 |
  22. +--------------+
  23. 1 row in set (0.00 sec)
  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)
  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)
  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，给个默认值

最新资料

更多内容请访问：IT源点

注意：本文归作者所有，未经作者允许，不得转载

#MySQL教程
阅读全部
相关文章推荐
  • 1
  • 2

全部评论: 0 条

    我有话说: