新闻资讯
MySQL数据库,详解NULL让人防不胜防的坑(一)
当数据的值为NULL的时候,可能出现各种意想不到的效果,让⼈防不胜防,我们来看看
NULL导致的各种神坑,如何避免?
⽐较运算符中使⽤NULL
认真看下⾯的效果
mysql> select 1>NULL;
+--------+
| 1>NULL |
+--------+
| NULL |
+--------+
1 row in set (0.00 sec)
mysql> select 1<NULL;
这是Mysql系列第13篇。
环境:mysql5.7.25,cmd命令中进⾏演⽰。+--------+
| 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的记录。
回复列表