Mysql NULL导致的神坑

2023-07-16 10:09:03 69

比较运算符中使用NULL

mysql>select1>NULL;
+--------+
|1>NULL|
+--------+
|NULL|
+--------+
1rowinset(0.00sec)

mysql>select1select1<>NULL;
+---------+
|1<>NULL|
+---------+
|NULL|
+---------+
1rowinset(0.00sec)

mysql>select1>NULL;
+--------+
|1>NULL|
+--------+
|NULL|
+--------+
1rowinset(0.00sec)

mysql>select1select1>=NULL;
+---------+
|1>=NULL|
+---------+
|NULL|
+---------+
1rowinset(0.00sec)

mysql>select1<=NULL;
+---------+
|1<=NULL|
+---------+
|NULL|
+---------+
1rowinset(0.00sec)

mysql>select1!=NULL;
+---------+
|1!=NULL|
+---------+
|NULL|
+---------+
1rowinset(0.00sec)

mysql>select1<>NULL;
+---------+
|1<>NULL|
+---------+
|NULL|
+---------+
1rowinset(0.00sec)

mysql>selectNULL=NULL,NULL!=NULL;
+-----------+------------+
|NULL=NULL|NULL!=NULL|
+-----------+------------+
|NULL|NULL|
+-----------+------------+
1rowinset(0.00sec)

mysql>select1in(null),1notin(null),nullin(null),nullnotin(null);
+-------------+-----------------+----------------+--------------------+
|1in(null)|1notin(null)|nullin(null)|nullnotin(null)|
+-------------+-----------------+----------------+--------------------+
|NULL|NULL|NULL|NULL|
+-------------+-----------------+----------------+--------------------+
1rowinset(0.00sec)

mysql>select1=any(selectnull),null=any(selectnull);
+--------------------+-----------------------+
|1=any(selectnull)|null=any(selectnull)|
+--------------------+-----------------------+
|NULL|NULL|
+--------------------+-----------------------+
1rowinset(0.00sec)

mysql>select1=all(selectnull),null=all(selectnull);
+--------------------+-----------------------+
|1=all(selectnull)|null=all(selectnull)|
+--------------------+-----------------------+
|NULL|NULL|
+--------------------+-----------------------+
1rowinset(0.00sec)

结论：任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、notin、any/some、all）比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。

准备数据

mysql>createtabletest1(aint,bint);
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest1values(1,1),(1,null),(null,null);
QueryOK,3rowsaffected(0.00sec)
Records:3Duplicates:0Warnings:0

mysql>select*fromtest1;
+------+------+
|a|b|
+------+------+
|1|1|
|1|NULL|
|NULL|NULL|
+------+------+
3rowsinset(0.00sec)

上面3条数据，认真看一下，特别是注意上面NULL的记录。

IN、NOTIN和NULL比较

IN和NULL比较

mysql>select*fromtest1;
+------+------+
|a|b|
+------+------+
|1|1|
|1|NULL|
|NULL|NULL|
+------+------+
3rowsinset(0.00sec)

mysql>select*fromtest1whereain(null);
Emptyset(0.00sec)

mysql>select*fromtest1whereain(null,1);
+------+------+
|a|b|
+------+------+
|1|1|
|1|NULL|
+------+------+
2rowsinset(0.00sec)

结论：当IN和NULL比较时，无法查询出为NULL的记录。

NOTIN和NULL比较

mysql>select*fromtest1whereanotin(1);
Emptyset(0.00sec)

mysql>select*fromtest1whereanotin(null);
Emptyset(0.00sec)

mysql>select*fromtest1whereanotin(null,2);
Emptyset(0.00sec)

mysql>select*fromtest1whereanotin(2);
+------+------+
|a|b|
+------+------+
|1|1|
|1|NULL|
+------+------+
2rowsinset(0.00sec)

结论：当NOTIN后面有NULL值时，不论什么情况下，整个sql的查询结果都为空。

EXISTS、NOTEXISTS和NULL比较

mysql>select*fromtest2;
+------+------+
|a|b|
+------+------+
|1|1|
|1|NULL|
|NULL|NULL|
+------+------+
3rowsinset(0.00sec)

mysql>select*fromtest1t1whereexists(select*fromtest2t2wheret1.a=t2.a);
+------+------+
|a|b|
+------+------+
|1|1|
|1|NULL|
+------+------+
2rowsinset(0.00sec)

mysql>select*fromtest1t1wherenotexists(select*fromtest2t2wheret1.a=t2.a);
+------+------+
|a|b|
+------+------+
|NULL|NULL|
+------+------+
1rowinset(0.00sec)

上面我们复制了表test1创建了表test2。

查询语句中使用exists、notexists对比test1.a=test2.a，因为=不能比较NULL，结果和预期一致。

判断NULL只能用ISNULL、ISNOTNULL

mysql>select1isnotnull;
+---------------+
|1isnotnull|
+---------------+
|1|
+---------------+
1rowinset(0.00sec)

mysql>select1isnull;
+-----------+
|1isnull|
+-----------+
|0|
+-----------+
1rowinset(0.00sec)

mysql>selectnullisnull;
+--------------+
|nullisnull|
+--------------+
|1|
+--------------+
1rowinset(0.00sec)

mysql>selectnullisnotnull;
+------------------+
|nullisnotnull|
+------------------+
|0|
+------------------+
1rowinset(0.00sec)

看上面的效果，返回的结果为1或者0。

结论：判断是否为空只能用ISNULL、ISNOTNULL。

聚合函数中NULL的坑

示例

mysql>selectcount(a),count(b),count(*)fromtest1;
+----------+----------+----------+
|count(a)|count(b)|count(*)|
+----------+----------+----------+
|2|1|3|
+----------+----------+----------+
1rowinset(0.00sec)

count(a)返回了2行记录，a字段为NULL的没有统计出来。
count(b)返回了1行记录，为NULL的2行记录没有统计出来。
count(*)可以统计所有数据，不论字段的数据是否为NULL。

再继续看

mysql>select*fromtest1whereaisnull;
+------+------+
|a|b|
+------+------+
|NULL|NULL|
+------+------+
1rowinset(0.00sec)

mysql>selectcount(a)fromtest1whereaisnull;
+----------+
|count(a)|
+----------+
|0|
+----------+
1rowinset(0.00sec)

上面第1个sql使用isnull查询出了结果，第2个sql中count(a)返回的是0行。

结论：count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。

NULL不能作为主键的值

mysql>createtabletest3(aintprimarykey,bint);
QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest3values(null,1);
ERROR1048(23000):Column'a'cannotbenull

上面我们创建了一个表test3，字段a未指定不能为空，插入了一条NULL的数据，报错原因：a字段的值不能为NULL，我们看一下表的创建语句：

mysql>showcreatetabletest3;
+-------+------------+
|Table|CreateTable|
+-------+------------+
|test3|CREATETABLE`test3`(
`a`int(11)NOTNULL,
`b`int(11)DEFAULTNULL,
PRIMARYKEY(`a`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8
+-------+------------+
1rowinset(0.00sec)

从上面的脚本可以看出，当字段为主键的时候，字段会自动设置为notnull。

结论：当字段为主键的时候，字段会自动设置为notnull。

看了上面这些还是比较晕，NULL的情况确实比较难以处理，容易出错，最有效的方法就是避免使用NULL。所以，强烈建议创建字段的时候字段不允许为NULL，设置一个默认值。

总结

NULL作为布尔值的时候，不为1也不为0
任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、notin、any/some、all），返回值都为NULL
当IN和NULL比较时，无法查询出为NULL的记录
当NOTIN后面有NULL值时，不论什么情况下，整个sql的查询结果都为空
判断是否为空只能用ISNULL、ISNOTNULL
count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行
当字段为主键的时候，字段会自动设置为notnull
NULL导致的坑让人防不胜防，强烈建议创建字段的时候字段不允许为NULL，给个默认值

到此这篇关于MysqlNULL导致的神坑的文章就介绍到这了,更多相关MysqlNULL导致坑内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

Mysql NULL导致的神坑

热门推荐

随机推荐