专栏首页路人甲Java玩转Mysql系列 - 第13篇:细说NULL导致的神坑,让人防不胜防

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

这是Mysql系列第11篇。

环境:mysql5.7.25,cmd命令中进行演示。

当数据的值为NULL的时候,可能出现各种意想不到的效果,让人防不胜防,我们来看看NULL导致的各种神坑,如何避免?比较运算符中使用NULL

认真看下面的效果

mysql> select 1>NULL;
+--------+
| 1>NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1<NULL;
+--------+
| 1<NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1<>NULL;
+---------+
| 1<>NULL |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1>NULL;
+--------+
| 1>NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1<NULL;
+--------+
| 1<NULL |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 1>=NULL;
+---------+
| 1>=NULL |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1<=NULL;
+---------+
| 1<=NULL |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1!=NULL;
+---------+
| 1!=NULL |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

mysql> select 1<>NULL;
+---------+
| 1<>NULL |
+---------+
|    NULL |
+---------+
1 row in set (0.00 sec)

mysql> select NULL=NULL,NULL!=NULL;
+-----------+------------+
| NULL=NULL | NULL!=NULL |
+-----------+------------+
|      NULL |       NULL |
+-----------+------------+
1 row in set (0.00 sec)

mysql> select 1 in (null),1 not in (null),null in (null),null not in (null);
+-------------+-----------------+----------------+--------------------+
| 1 in (null) | 1 not in (null) | null in (null) | null not in (null) |
+-------------+-----------------+----------------+--------------------+
|        NULL |            NULL |           NULL |               NULL |
+-------------+-----------------+----------------+--------------------+
1 row in set (0.00 sec)

mysql> select 1=any(select null),null=any(select null);
+--------------------+-----------------------+
| 1=any(select null) | null=any(select null) |
+--------------------+-----------------------+
|               NULL |                  NULL |
+--------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select 1=all(select null),null=all(select null);
+--------------------+-----------------------+
| 1=all(select null) | null=all(select null) |
+--------------------+-----------------------+
|               NULL |                  NULL |
+--------------------+-----------------------+
1 row in set (0.00 sec)

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

准备数据

mysql> create table test1(a int,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test1 values (1,1),(1,null),(null,null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

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

IN、NOT IN和NULL比较

IN和NULL比较

mysql> select * from test1;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test1 where a in (null);
Empty set (0.00 sec)

mysql> select * from test1 where a in (null,1);
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

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

NOT IN 和NULL比较

mysql> select * from test1 where a not in (1);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (null);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (null,2);
Empty set (0.00 sec)

mysql> select * from test1 where a not in (2);
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

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

EXISTS、NOT EXISTS和NULL比较

mysql> select * from test2;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test1 t1 where exists (select * from test2 t2 where t1.a = t2.a);
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from test1 t1 where not exists (select * from test2 t2 where t1.a = t2.a);
+------+------+
| a    | b    |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

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

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

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

mysql> select 1 is not null;
+---------------+
| 1 is not null |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql> select 1 is null;
+-----------+
| 1 is null |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

mysql> select null is null;
+--------------+
| null is null |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

mysql> select null is not null;
+------------------+
| null is not null |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

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

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

聚合函数中NULL的坑

示例

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

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

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

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

再继续看

mysql> select * from test1 where a is null;
+------+------+
| a    | b    |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

mysql> select count(a) from test1 where a is null;
+----------+
| count(a) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

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

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

NULL不能作为主键的值

mysql> create table test3(a int primary key,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test3 values (null,1);
ERROR 1048 (23000): Column 'a' cannot be null

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

mysql> show create table test3;
+-------+------------+
| Table | Create Table      |
+-------+------------+
| test3 | CREATE TABLE `test3` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
+-------+------------+
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,给个默认值

本文分享自微信公众号 - 路人甲Java(javacode2018),作者:路人甲Java

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Mysql系列 - 第4天:DDL常见操作汇总

    DDL:Data Define Language数据定义语言,主要用来对数据库、表进行一些管理操作。

    路人甲Java
  • 玩转Mysql系列 - 第6篇:select查询基础篇

    DQL(Data QueryLanguage):数据查询语言,通俗点讲就是从数据库获取数据的,按照DQL的语法给数据库发送一条指令,数据库将按需求返回数据。

    路人甲Java
  • 玩转Mysql系列 - 第7篇:玩转select条件查询,避免采坑

    电商中:我们想查看某个用户所有的订单,或者想查看某个用户在某个时间段内所有的订单,此时我们需要对订单表数据进行筛选,按照用户、时间进行过滤,得到我们期望的结果。

    路人甲Java
  • 利用mk-table-checksum监测Mysql主从数据一致性操作记录

    前面已经提到了mysql主从环境下数据一致性检查:mysql主从同步(3)-percona-toolkit工具(数据一致性监测、延迟监控)使用梳理 今天这里再介...

    洗尽了浮华
  • MySQL之Explain详解

    一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划,这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么...

    黄泽杰
  • ​MySQL中explain的结果字段介绍(1)

    我们在使用MySQL的时候,用的最多的情况可能就是select语句了,当我们在一个表查找数据的时候,经常会遇到查找的速度比较慢的情况,作为一名DBA,我...

    AsiaYe
  • R语言 马赛克图

    注意:mosaicplot和mosaic绘制马赛克图是基于多(二维以上)列联表。

    拴小林
  • ASP.NET MVC5+EF6+EasyUI 后台管理系统(31)-MVC使用RDL报表

    这次我们来演示MVC3怎么显示RDL报表,坑爹的微软把MVC升级到5都木有良好的支持报表,让MVC在某些领域趋于短板 我们只能通过一些方式来使用rdl报表。 R...

    用户1149182
  • MySQL:如何快速的查看Innodb数据文件

    如果要得到答案除了学习源码,可能更加直观的方式就是查看Innodb的ibd数据文件了,俗话说得好“眼见为实”,但是我们知道数据文件是二进制形式的,Innodb通...

    用户1278550
  • 【OCP最新题库解析(052)--题28】Which action is automatically perfo

    A. gathering metadata from the Automatic Diagnostic Repository(ADR)

    小麦苗DBA宝典

扫码关注云+社区

领取腾讯云代金券