Mysql NULL导致的神坑
16lz
2021-12-16
比较运算符中使用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)
准备数据
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: 0mysql> select * from test1;+------+------+| a | b |+------+------+| 1 | 1 || 1 | NULL || NULL | NULL |+------+------+3 rows in set (0.00 sec)
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)
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)
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)
查询语句中使用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)
结论:判断是否为空只能用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)
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)
结论: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
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。
看了上面这些还是比较晕,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系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- android从服务器下载文件(php+apache+win7+MySql)
- Android空引用问题的解决方法——on a null object reference
- java.lang.NullPointerException Attempt to invoke virtual met
- java.lang.NullPointerException: Attempt to invoke virtual me
- 【有图】android通过jdbc连接mysql(附文件)
- Android(安卓)NullPointerException解决方法(空指针异常)