写在前面 在前面学习的过程中，我们多次遇到由于数据值为NULL而导致的各种问题，那么本篇就来总结一下NULL使用过程中需要注意的点。
本文的主要内容如下：（1）在比较运算符中使用NULL；（2）IN和NULL比较；（3）NOT IN和NULL比较；（4）EXISTS、NOT EXISTS和NULL比较；（5）使用IS NULL、IS NOT NULL来判断NULL；（6）聚合函数中NULL的坑；（7）主键的值不允许为NULL；（8）文章总结。
在比较运算符中使用NULL 仔细阅读下面SQL语句的执行结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 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)
由上面SQL的执行结果可以知道，任何值和NULL使用比较运算符，如>、>=、=、<、<=、<>、!=或者in、not in、any/some、all比较时，其返回值都是NULL；当NULL作为布尔值的时候，注意它既不是0也不是1，就是NULL。
数据准备 为了后续更好地进行演示和查看执行效果，这里先准备一些数据，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 mysql> drop table if exists book4; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table book4(a int,b int); Query OK, 0 rows affected (0.03 sec) mysql> insert into book4 values(1,1),(1,null),(null,null); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from book4; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec)
可以看到这里插入了3条记录，请注意其中值为NULL的记录。
IN、NOT IN和NULL比较 IN和NULL比较 仔细阅读下面SQL的执行结果，然后得出结论：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 mysql> select * from book4; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> select * from book4 where a in (null); Empty set (0.00 sec) mysql> select * from book4 where a in (null,1); +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | +------+------+ 2 rows in set (0.00 sec) mysql> select * from book4 where a in (1); +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | +------+------+ 2 rows in set (0.00 sec)
通过对比可以发现，当IN和NULL比较时，它是无法查询出值为NULL的记录 。
NOT IN和NULL比较 仔细阅读下面SQL的执行结果，然后得出结论：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 mysql> select * from book4; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> select * from book4 where a not in(1); Empty set (0.00 sec) mysql> select * from book4 where a not in(null); Empty set (0.00 sec) mysql> select * from book4 where a not in(null,2); Empty set (0.00 sec) mysql> select * from book4 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比较 首先创建一个book5表，其中的数据和定义语句和book4表的完全一致：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 mysql> drop table if exists book5; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table book5(a int,b int); Query OK, 0 rows affected (0.03 sec) mysql> insert into book5 values(1,1),(1,null),(null,null); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from book5; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec)
之后仔细阅读下面SQL的执行结果，然后得出结论：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 mysql> select * from book4 as b4 where exists (select * from book5 as b5 where b5.a=b4.a); +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | +------+------+ 2 rows in set (0.01 sec) mysql> select * from book4 as b4 where not exists (select * from book5 as b5 where b5.a=b4.a); +------+------+ | a | b | +------+------+ | NULL | NULL | +------+------+ 1 row in set (0.00 sec)
可以看到我们创建了book5表，然后复制了book4表的数据和结构，在子查询中使用exists和not exists关键字，同时由于=无法比较NULL，因此满足b5.a=b4.a条件的a的值肯定不为NULL，因此查询结果就是预先所期望的。
使用IS NULL、IS NOT NULL来判断NULL 前面我们也说过不能使用=来比较NULL值，而应该使用IS NULL、IS NOT NULL来判断NULL值。
仔细阅读下面SQL的执行结果，然后得出结论：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 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)
尽管上面的执行结果返回的是0和1，但是我们也知道，1代表是，0代表否。
因此我们就可以得出结论，判断NULL必须使用IS NULL或者IS NOT NULL 。
聚合函数中NULL的坑 说完了NULL值的判断，接下来再来学习聚合函数中NULL的坑。
仔细阅读下面SQL的执行结果，然后得出结论：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> select * from book4; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> select count(a),count(b),count(*) from book4; +----------+----------+----------+ | count(a) | count(b) | count(*) | +----------+----------+----------+ | 2 | 1 | 3 | +----------+----------+----------+ 1 row in set (0.01 sec)
可以看到，使用count(a)返回了2行记录，此时a字段值为NULL的记录没有被统计；count(b)返回了1行记录，此时b字段值为NULL的记录没有被统计；而count(*)则返回了3行记录，也就是说count(*)可以统计所有的数据，无论字段的值是否为NULL。
再来看看两条SQL语句，这些会加深你对上述论据的理解：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 mysql> select * from book4; +------+------+ | a | b | +------+------+ | 1 | 1 | | 1 | NULL | | NULL | NULL | +------+------+ 3 rows in set (0.00 sec) mysql> select * from book4 where a is null; +------+------+ | a | b | +------+------+ | NULL | NULL | +------+------+ 1 row in set (0.00 sec) mysql> select count(a) from book4 where a is null; +----------+ | count(a) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
可以看到我们第二个SQL使用is nul对a字段进行判断，查询出a字段中值为NULL的记录；而第三个SQL中则是对第二个SQL查询结果进行统计，前面既然可以查询出一条，按照道理这里count(a)的值就应该是1，但是实际值却是0，这也就说明count(a)无法统计字段a值为NULL的记录。
结论：count(*)可以统计所有的数据，无论字段的值是否为NULL；但是count(字段)无法统计字段为NULL的值。
主键的值不允许为NULL 在前面我们也多次提到过，主键不允许存在空值，但是唯一键可以包含NULL值，并且每个NULL值都是唯一的（即NULL！=NULL），接下来就来验证这一论据。
第一步，创建一个book6表，定义表的结构和数据如下所示：
1 2 3 4 5 6 7 8 mysql> drop table if exists book6; Query OK, 0 rows affected (0.01 sec) mysql> create table book6(a int primary key,b int unique key,c int); Query OK, 0 rows affected (0.02 sec) mysql> insert into book6 values(null,1,1); ERROR 1048 (23000): Column 'a' cannot be null
可以看到a字段被设置为主键，但是在定义的时候a字段，未指定不能为空，插入一条值为NULL的记录后抛错，原因在于a字段不能为NULL。
查看一下book6表的创建语句，可以看到当a字段被指定为主键时，系统默认会将其设置为not null，如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> show create table book6; +-------+---------------------------------------------- | Table | Create Table +-------+---------------------------------------------- | book6 | CREATE TABLE `book6` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------- 1 row in set (0.00 sec)
而字段b被设置为唯一键，其值默认是null，往里面插入两条值为NULL的记录，可以发现居然可以插入：
1 2 3 4 5 mysql> insert into book6 values(1,null,1); Query OK, 1 row affected (0.00 sec) mysql> insert into book6 values(2,null,2); Query OK, 1 row affected (0.00 sec)
但是，请注意，b字段中的两个NULL不是相同的，即NULL！=NULL。如果不好理解，可以插入两条b字段相同的值且不是NULL的记录来帮助理解，如都是数字3，此时系统会抛出b字段重复的提示，且无法插入：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> insert into book6 values(3,3,3); Query OK, 1 row affected (0.00 sec) mysql> insert into book6 values(4,3,4); ERROR 1062 (23000): Duplicate entry '3' for key 'b' mysql> select * from book6; +---+------+------+ | a | b | c | +---+------+------+ | 1 | NULL | 1 | | 2 | NULL | 2 | | 3 | 3 | 3 | +---+------+------+ 3 rows in set (0.00 sec)
结论：当某个字段被指定为主键的时候，该字段会自动添加not null属性。
由于NULL值的存在会使得问题变得复杂，因此建议在定义表结构的时候，将字段都添加not null属性。
文章总结 接下来将对本文的内容进行一个小结，如下所示：>、>=、=、<、<=、<>、!=或者in、not in、any/some、all比较时，其返回值都是NULL；count(*)可以统计所有的数据，无论字段的值是否为NULL；但是count(字段)无法统计字段为NULL的值；NULL！=NULL）；
