MySQL数据库，详解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，给个默

认值