Mysql 中的 Null
编辑本文主要讨论MySQL中Null值的特性
主要内容:
Null的定义
Null与条件判断
Null与分组以及排序操作
Null对聚合函数以及约束的影响
Null可能导致子查询出现意料之外的错误
Null的定义
在MySQL官方的定义中，NULL是一种未知的、未定义的值
原句: “a missing unknown value”
故对NULL的处理方式与其他致会有些许不同。
Null与判断条件
在学习MySQL的时候，无论是老师还是教程，都会明确强调对于NULL的判断，只能使用 IS NULL 或是 IS NOT NULL，为什么，要是不这么做会怎样；
我们假设有这一张表：
mysql> DESC student;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| sid | int | NO | PRI | NULL | |
| name | varchar(255) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)数据如下:
mysql> SELECT * FROM student;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 1 | 小明 | NULL | 男 |
| 2 | 小红 | 21 | 女 |
+-----+--------+------+------+
2 rows in set (0.00 sec)00 sec)小明的年龄是NULL值，我们这时候我们尝试头铁使用正常的运算符，看看会是什么结果
mysql> SELECT * FROM student WHERE age = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM student WHERE age > 0;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 2 | 小红 | 21 | 女 |
+-----+--------+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM student WHERE age = age;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 2 | 小红 | 21 | 女 |
+-----+--------+------+------+
1 row in set (0.00 sec)可以发现，小明的年龄无法使用常规的运算符判断，为什么？
因为NULL值在MySQL中的定义是一种不确定的未知的值，故在做条件判断时，这个判断结果也是未知的，也就是，还是NULL，如下：
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
1 row in set (0.00 sec)官方的原句:Because the result of any arithmetic comparison with
NULLis alsoNULL, you cannot obtain any meaningful results from such comparisons.
那么，既然对于普通的判断是无意义的，那官方就定义了专门用于判断NULL值的语法：
mysql> SELECT 1 IS NULL, 1 IS NOT NULL, NULL IS NULL, NULL IS NOT NULL;
+-----------+---------------+--------------+------------------+
| 1 IS NULL | 1 IS NOT NULL | NULL IS NULL | NULL IS NOT NULL |
+-----------+---------------+--------------+------------------+
| 0 | 1 | 1 | 0 |
+-----------+---------------+--------------+------------------+
1 row in set (0.00 sec)顺便提一嘴: MySQL中本质是没有布尔类型(至少在写这篇文章时没有)，虽然官方提供了一个
BOOL类型，但究其本质还是TINYINT(1)mysql> SELECT True, False; +------+-------+ | True | False | +------+-------+ | 1 | 0 | +------+-------+ 1 row in set (0.00 sec)
在MySQL中，采用空即假的定义，而NULL有未定义的意思，即在做条件判断时NULL值也表示为False。故我们的条件查询不到小明
特例 <=>
在MySQL中<=>表示严格判断两端是否相等
mysql> SELECT NULL <=> NULL, NULL <=> 1, NULL <=> False, 1 <=> 1;
+---------------+------------+----------------+---------+
| NULL <=> NULL | NULL <=> 1 | NULL <=> False | 1 <=> 1 |
+---------------+------------+----------------+---------+
| 1 | 0 | 0 | 1 |
+---------------+------------+----------------+---------+
1 row in set (0.00 sec)可以发现，在使用<=>运算符判断带NULL的条件时，结果符合正常的逻辑
分组和排序
既然Null被表示为一个，不存在的值，那遇到分组以及排序操作会发生什么？
我们先在测试表中加几条数据，结果如下：
mysql> SELECT * FROM student;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 1 | 小明 | NULL | 男 |
| 2 | 小红 | 21 | 女 |
| 3 | 小白 | NULL | 男 |
| 4 | 小丽 | 21 | 女 |
+-----+--------+------+------+
4 rows in set (0.00 sec)GROUP BY
我们尝试统计各年龄的学生人数：
mysql> SELECT age,COUNT(sid) AS student_count FROM student GROUP BY age;
+------+---------------+
| age | student_count |
+------+---------------+
| NULL | 2 |
| 21 | 2 |
+------+---------------+
2 rows in set (0.00 sec)我们发现，所有的NULL值被归为同一类
ORDER BY
我们尝试按年龄对学生进行排序：
mysql> SELECT * FROM student ORDER BY age;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 1 | 小明 | NULL | 男 |
| 3 | 小白 | NULL | 男 |
| 2 | 小红 | 21 | 女 |
| 4 | 小丽 | 21 | 女 |
+-----+--------+------+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM student ORDER BY age DESC;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 2 | 小红 | 21 | 女 |
| 4 | 小丽 | 21 | 女 |
| 1 | 小明 | NULL | 男 |
| 3 | 小白 | NULL | 男 |
+-----+--------+------+------+
4 rows in set (0.00 sec)貌似。NULL被认为是一个很小的值，搞点特别的数据看看
mysql> SELECT * FROM student ORDER BY age DESC;
+-----+--------+---------+------+
| sid | name | age | sex |
+-----+--------+---------+------+
| 2 | 小红 | 0 | 女 |
| 4 | 小丽 | -999999 | 女 |
| 1 | 小明 | NULL | 男 |
| 3 | 小白 | NULL | 男 |
+-----+--------+---------+------+
4 rows in set (0.00 sec)看来印证了我的猜想
对聚合函数的影响
在使用聚合函数时，要留意Null值可能会被聚合函数忽略
mysql> SELECT * FROM student;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 1 | 小明 | NULL | 男 |
| 2 | 小红 | 21 | 女 |
| 3 | 小白 | NULL | 男 |
| 4 | 小丽 | 21 | 女 |
+-----+--------+------+------+
4 rows in set (0.00 sec)
mysql> SELECT COUNT(*),COUNT(age),SUM(age),AVG(age) FROM student;
+----------+------------+----------+----------+
| COUNT(*) | COUNT(age) | SUM(age) | AVG(age) |
+----------+------------+----------+----------+
| 4 | 2 | 42 | 21.0000 |
+----------+------------+----------+----------+
1 row in set (0.00 sec)对唯一约束的影响
Null可能会导致唯一约束失效，来看实验。
假定有这么一张课程表，结构与数据如下:
mysql> DESC course;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | UNI | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM course;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | NULL |
| 2 | 操作系统原理 |
+----+--------------------+
2 rows in set (0.00 sec)name字段是个唯一键，此时已经有个name为Null的记录，这时候我们再加入一条看看：
mysql> SELECT * FROM course;
+----+--------------------+
| id | name |
+----+--------------------+
| 1 | NULL |
| 3 | NULL |
| 2 | 操作系统原理 |
+----+--------------------+
3 rows in set (0.00 sec)可以发现，属性值为Null时，唯一约束不生效
对子查询的影响
Null可能会对子查询结果产生影响，再来看个实验:
我们再建一张表
mysql> DESC score;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| s_id | int | YES | MUL | NULL | |
| c_id | int | YES | MUL | NULL | |
| score | double | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
4 rows in set (0.00 sec)插入几条数据：
mysql> SELECT * FROM score;
+----+------+------+-------+
| id | s_id | c_id | score |
+----+------+------+-------+
| 1 | 1 | 1 | 95 |
| 2 | 1 | 2 | 93 |
| 3 | 2 | 1 | 94 |
| 4 | NULL | 2 | 88 |
+----+------+------+-------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM student;
+-----+--------+------+------+
| sid | name | age | sex |
+-----+--------+------+------+
| 1 | 小明 | 22 | 男 |
| 2 | 小红 | 21 | 女 |
| 3 | 小白 | 23 | 男 |
| 4 | 小丽 | 21 | 女 |
+-----+--------+------+------+
4 rows in set (0.00 sec)眼尖的应该发现了，这里Null也导致外键约束失效
此时，我们来查询，没有任何课程成绩的学生信息：
mysql> SELECT * FROM student
-> WHERE sid NOT IN (
-> SELECT s_id FROM score
-> );
Empty set (0.00 sec)先不考虑我这里随意的命名
我们发现，怎么查询结果是空的，理论上SELECT s_id FROM score应该会返回所有的已经有成绩的学生ID(不去重)，可结果不可能包含3(小白)以及4(小丽)，在做NOT IN时，按我们正常的逻辑他们应该是会被输出的
我们单独看子查询的输出
mysql> SELECT s_id FROM score;
+------+
| s_id |
+------+
| NULL |
| 1 |
| 1 |
| 2 |
+------+
4 rows in set (0.00 sec)欸，就和这个Null有关，往下看
当一个值，不包含在待判断的集合中时，无论你使用IN 还是NOT IN,结果都是NULL
但很神奇，若包含其中就没事，这就超出我的能力范围了。
mysql> SELECT 3 IN (1,1,2), 3 IN (NULL,1,1,2), 1 IN (NULL,1,1,2);
+--------------+-------------------+-------------------+
| 3 IN (1,1,2) | 3 IN (NULL,1,1,2) | 1 IN (NULL,1,1,2) |
+--------------+-------------------+-------------------+
| 0 | NULL | 1 |
+--------------+-------------------+-------------------+
1 row in set (0.00 sec)
mysql> SELECT 3 NOT IN (1,1,2), 3 NOT IN (NULL,1,1,2), 1 NOT IN (NULL,1,1,2);
+------------------+-----------------------+-----------------------+
| 3 NOT IN (1,1,2) | 3 NOT IN (NULL,1,1,2) | 1 NOT IN (NULL,1,1,2) |
+------------------+-----------------------+-----------------------+
| 1 | NULL | 0 |
+------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)而我们前面提到，在MySQL中，空即为假，Null的定义本就有未定义的意思，故在判断3 和 4是否不被包含时，结果为False，这或许是为什么没输出小白和小丽的原因。
十几亿美元的错误
在网上收集相关资料的时候，发现许多文章都在讨论这个问题
但我添加这段并不是为了吐槽
托尼·霍尔（Tony Hoare），1980年获得图灵奖、快速排序算法的创造者，这位大佬在1965年创造了Null与Null指针，并加入到ALGOL W语言中
Null实现起来比较容易，以致后来Null 被用于（或者说是被滥用于）掩盖意外情况，代码中的错误可能要在很远的地方才能被发现，从而产生令人担忧的连锁反应。
Tony 希望用它来表示每一种类型的成员，而用户必须在每一次引用变量时都进行 Null 检查。 事实证明，这是个错误，是个价值十亿美元的大麻烦
This led me to suggest that the null value is a member of every type, and a null check is required on every use of that reference variable, and it may be perhaps a billion dollar mistake. --Tony Hoare
好在C#、Spec#乃至 Java 这样的现代语言都引入了非 Null 引用参数的思想，并在编译时进行检查，以确保代码当中不可能存在 Null 值
Modern languages such as C# or Spec# and even Java are introducing the idea of non-null reference parameters, and compile time checking which verifies that they cannot possibly have null values. --Tony Hoare
我引入这段内容，并不是想将吐槽Tony设计的不严谨，而是想通过这件事件也告诉读者，在我们开发程序时应抱有严谨负责的态度，作为开发者应负起对代码质量的责任。
同样，当我们知道MySQL中Null值存在的一些问题后，在实际开发过程中应尽可能避免，而非为了一时之快而抱有侥幸心理，这才是对代码质量负责任的体现。
参考资料：
官方文档：MySQL :: MySQL Tutorial :: 4.4.6 Working with NULL Values
MySQL NULL 值处理 | 菜鸟教程 (runoob.com)
Null References： The Billion Dollar Mistake - InfoQ
后悔发明Null：堪称CS史上最严重错误，至少造成10亿美金损失_语言 & 开发_Tony Hoare_InfoQ精选文章
- 0
-
赞助
微信
支付宝
-
分享