Mysql NULL导致的神坑

2024-08-10 05:13:10

比较运算符中使用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导致坑内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

MySQL中NOT IN填坑之列为null的问题解决

前一段时间在公司做一个小功能的时候,统计一下某种情况下有多少条数据,然后修改的问题,当时感觉很简单,写了一个如下的 SQL: SELECT COUNT(*) FROM t1 where tl.c1 not IN (SELECT t2.c1 FROM t2); 预期的结果是:有多少条数据在 t1 中,同时不在 t2 中,结果为:0,也就是 t1 中数据都在 t2 中,但是很容易就发现某些数据在 t1 中不在 t2 中,所以就感觉很奇怪,这个 SQL 看着也没问题啊.经过一番查询原来是因为 t2 的
Mysql NULL导致的神坑

比较运算符中使用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<>
简析mysql字符集导致恢复数据库报错问题

mysql字符集编码错误的导入数据会提示错误了,这个和插入数据一样如果保存的数据与mysql编码不一样那么肯定会出现导入乱码或插入数据丢失的问题,下面我们一起来看一个例子. <script>ec(2);</script> 恢复数据库报错:由于字符集问题,最原始的数据库默认编码是latin1,新备份的数据库的编码是utf8,因此导致恢复错误. [root@hk byrd]# /usr/local/mysql/bin/mysql -uroot -p'admin' t4x < /t
MYSQL时区导致时间差了14或13小时的解决方法

目录 CST 时区排错过程解决方案 MySQL时区有问题(相差13或14小时) p>我一般使用MYSQL定义字段类型时,一般使用TIMESTAMP时间戳来定义创建时间与更新时间,并将其定义为默认值为CURRENT_TIME,但是由于场景特殊,现在我需要将一个任务的开始时间与结束时间记录,并写入数据库,那么我的开始时间戳与结束时间戳则不应该是使用数据库自带的默认值的,而是应该使用我使用java代码里面传进去的LocalDateTime.now()方法.但是插入后数据我发现有问题,插入的时间比我
解决JSON数据因为null导致数据加载失败的方法

一.首先分析问题: 使用NSJSONSerialization或者AFN框架的AFHTTPSessionManager(底层也是NSJSONSerialization)将NSData数据转化成OC对象,有时会出现URL正确,加载数据任然会报错: reason: '-[NSNull length]: unrecognized selector sent to instance 分析原因发现,转化出来的OC对象中含有null.所以,NSNull没有length方法,所以会报找不到方法错误. 二.解决
MySQL NULL 值处理实例详解

MySQL NULL 值处理我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时,该命令可能就无法正常工作. 为了处理这种情况,MySQL提供了三大运算符: IS NULL: 当列的值是NULL,此运算符返回true. IS NOT NULL: 当列的值不为NULL, 运算符返回true. <=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true. 关于 NULL 的条件比较运算是比较
MySQL null与not null和null与空值''''的区别详解

相信很多用了MySQL很久的人,对这两个字段属性的概念还不是很清楚,一般会有以下疑问: 我字段类型是not null,为什么我可以插入空值为毛not null的效率比null高判断字段不为空的时候,到底要 select * from table where column <> '' 还是要用 select * from table wherecolumn is not null 呢. 带着上面几个疑问,我们来深入研究一下null 和 not null 到底有什么不一样. 首先,我们要搞清楚
MySQL null的一些易错点

依据null-values,MySQL的值为null的意思只是代表没有数据,null值和某种类型的零值是两码事,比如int类型的零值为0,字符串的零值为"",但是它们依然是有数据的,不是null. 我们在保存数据的时候,习惯性的把暂时没有的数据记为null,表示当前我们无法提供有效的信息. 不过使用null但是时候,需要我们注意一些问题.对此MySQL文档说明如下: problems-with-null 使用null的易错点下面我摘取MySQL官方给出的null的易错点做讲解. 对M
一次MySQL启动导致的事故实战记录

目录背景判断 MySQL 已完全启动的方式事故第一次修改第二次修改总结 MySQL` 启动存在端口被监听, 但服务没完全启动的情况. 背景 MySQL 启动时会进行第一次初始化数据库, 等待 MySQL 完全启动后, 再进行第二次初始化数据库. 第一次初始化数据库使用 --skip-networking (这个选项表示禁止 MySQL 的 TCP/IP 连接方式) 启动 MySQL 进行初始化, 初始化完成后会关闭 --skip-networking 选项重新启动 MySQ
MySQL大小写敏感导致的问题分析

MYSQL对大小写敏感见字如面,见标题知内容.你有遇到过因为MYSQL对大小写敏感而被坑的体验吗? 之前看过阿里巴巴Java开发手册,在MySql建表规约里有看到: [强制]表名.字段名必须使用小写字母或数字 , 禁止出现数字开头,禁止两个下划线中间只出现数字.数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑. 说明: MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写.因此,数据库名. 表名.字段名,都不允许出现任何大写字母,避免节
MySQL skip-character-set-client-handshake导致的一个字符集问题

今天帮同事处理一个棘手的事情,问题是这样的: 无论在客户机用哪个版本的mysql客户端连接服务器,发现只要服务器端设置了复制代码代码如下: character-set-server = utf8 之后, 复制代码代码如下: character_set_client. character_set_connection.character_set_results 就始终都是和服务器端保持一致了,即便在mysql客户端加上选项复制代码代码如下: --default-character-set

Mysql NULL导致的神坑

相关推荐

随机推荐