关于mysql的null相关查询的一些坑

我们先看一下效果，然后在解释，示例如下：

mysql> create table test5 (a int not null,b int,c varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test5 values (1,2,'a'),(3,null,'b'),(4,5,null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from test5;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
| 4 |    5 | NULL |
+---+------+------+
3 rows in set (0.00 sec)

上面我们创建了一个表test5，3个字段，a不能为空，b、c可以为空，插入了3条数据，睁大眼睛看效果了：

mysql> select * from test5 where b>0;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 4 |    5 | NULL |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from test5 where b<=0;
Empty set (0.00 sec)

mysql> select * from test5 where b=NULL;
Empty set (0.00 sec)

mysql> select * from test5 t where t.b between 0 and 100;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 4 |    5 | NULL |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from test5 where c like '%';
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from test5 where c in ('a','b',NULL);
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    2 | a    |
| 3 | NULL | b    |
+---+------+------+
2 rows in set (0.00 sec)

mysql> select * from test5 where c not in ('a','b',NULL);
Empty set (0.00 sec)

认真看一下上面的查询：

上面带有条件的查询，对字段b进行条件查询的，b的值为NULL的都没有出现。

对c字段进行like '%'查询、in、not查询，c中为NULL的记录始终没有查询出来。

between and查询，为空的记录也没有查询出来。

 

结论：查询运算符、like、between and、in、not in对NULL值查询不起效。

 

那NULL如何查询呢？继续向下看

IS NULL/IS NOT NULL（NULL值专用查询）

上面介绍的各种运算符对NULL值均不起效，mysql为我们提供了查询空值的语法：IS NULL、IS NOT NULL。

IS NULL（返回值为空的记录）

select 列名 from 表名 where 列 is null;

查询指定的列的值为NULL的记录。

如：

mysql> create table test7 (a int,b varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test7 (a,b) values (1,'a'),(null,'b'),(3,null),(null,null),(4,'c');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from test7;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
| NULL | b    |
|    3 | NULL |
| NULL | NULL |
|    4 | c    |
+------+------+
5 rows in set (0.00 sec)

mysql> select * from test7 t where t.a is null;
+------+------+
| a    | b    |
+------+------+
| NULL | b    |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)

mysql> select * from test7 t where t.a is null or t.b is null;
+------+------+
| a    | b    |
+------+------+
| NULL | b    |
|    3 | NULL |
| NULL | NULL |
+------+------+
3 rows in set (0.00 sec)

IS NULL（返回值不为空的记录）

select 列名 from 表名 where 列 is not null;

查询指定的列的值不为NULL的记录。

如：

mysql> select * from test7 t where t.a is not null;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    3 | NULL |
|    4 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test7 t where t.a is not null and t.b is not null;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    4 | c    |
+------+------+
2 rows in set (0.00 sec)