比较运算符中使用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，给个默认值
到此这篇关于mysql null导致的神坑的文章就介绍到这了,更多相关mysql null导致坑内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！