快活林资源网

Mysql NULL导致的神坑
数据库 2024/12/23 佚名
3 1
快活林资源网 Design By www.csstdc.com

比较运算符中使用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，给个默认值
快活林资源网 Design By www.csstdc.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
快活林资源网 Design By www.csstdc.com
www.csstdc.com 快活林资源网
120,135影音资源
344,641技术资源
22,817软件资源
435,032站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
快活林资源网 Design By www.csstdc.com