新闻资讯
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,给个默
认值
回复列表