前言

从概念上看，MySQL对Null的定义是a missing unknown value，它与空字符串''并不是一回事，MySQL对于它的处理方式也有些不同，对于初学者来说经常会把这二者混淆，本文就结合一些案例来具体看看。

1. 一道测试题

先从一道简单的测试题看起

表名：customer，表中数据如下：

mysql> select * from customer;
+----+----------+------+
| id | name
| age
|
+----+----------+------+
|
1 | zhangsan |
18 |
|
2 | lisi
|
20 |
|
3 | wangwu
|
18 |
|
4 | xiaoming |
19 |
|
5 | xiaohong | NULL |
|
6 | xiaowang | NULL |
+----+----------+------+
6 rows in set (0.04 sec)

请找出age不为18的人，年龄未知的也算。

mysql> select * from customer where age <> 18;
+----+----------+-----+
| id | name
| age |
+----+----------+-----+
|
2 | lisi
|
20 |
|
4 | xiaoming |
19 |
+----+----------+-----+
2 rows in set (0.03 sec)

正确的处理方式

mysql> select * from customer where age <> 18 or age is null;
+----+----------+------+
| id | name
| age
|
+----+----------+------+
|
2 | lisi
|
20 |
|
4 | xiaoming |
19 |
|
5 | xiaohong | NULL |
|
6 | xiaowang | NULL |
+----+----------+------+
4 rows in set (0.04 sec)

2. 如何判断null值

使用=、!=、<>都不能判断null值

mysql> select * from customer where age = null;
Empty set

正确的方式应该是is null、is not null、ifnull

mysql> select * from customer where age is null;
+----+----------+------+
| id | name
| age
|
+----+----------+------+
|
5 | xiaohong | NULL |
|
6 | xiaowang | NULL |
+----+----------+------+
2 rows in set (0.05 sec)
mysql> select * from customer where age is not null;
+----+----------+-----+
| id | name
| age |
+----+----------+-----+
|
1 | zhangsan |
18 |
|
2 | lisi
|
20 |
|
3 | wangwu
|
18 |
|
4 | xiaoming |
19 |
+----+----------+-----+
4 rows in set (0.05 sec)

mysql> select * from customer where ifnull(age,-1) = -1;
+----+----------+------+
| id | name
| age
|
+----+----------+------+
|
5 | xiaohong | NULL |
|
6 | xiaowang | NULL |
+----+----------+------+
2 rows in set (0.05 sec)

3. Null不要进行算术运算

使用算术比较运算符结果都是null

mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL
| NULL
| NULL
| NULL
|
+----------+-----------+----------+----------+
1 row in set (0.03 sec)

4. 使用GROUP BY、ORDER BY

group by时，null值会被分成一组

mysql> select age from customer group by age;
+------+
| age
|
+------+
| NULL |
|
18 |
|
19 |
|
20 |
+------+
4 rows in set (0.04 sec)

order by时，null值会显示在最前面

mysql> select * from customer order by age;
+----+----------+------+
| id | name
| age
|
+----+----------+------+
|
5 | xiaohong | NULL |
|
6 | xiaowang | NULL |
|
1 | zhangsan |
18 |
|
3 | wangwu
|
18 |
|
4 | xiaoming |
19 |
|
2 | lisi
|
20 |
+----+----------+------+
6 rows in set (0.05 sec)

5. COUNT()、 MIN()和 SUM()忽略 NULL值

mysql> select sum(age) from customer;
+----------+
| sum(age) |
+----------+
| 75
|
+----------+
1 row in set (0.03 sec)
mysql> select max(age) from customer;
+----------+
| max(age) |
+----------+
|
20 |
+----------+
1 row in set (0.03 sec)
mysql> select min(age) from customer;
+----------+
| min(age) |
+----------+
|
18 |
+----------+
1 row in set (0.03 sec)
mysql> select count(age) from customer;
+------------+
| count(age) |
+------------+
|
4 |
+------------+
1 row in set (0.04 sec)
mysql> select count(*) from customer;
+----------+
| count(*) |
+----------+
|
6 |
+----------+
1 row in set (0.04 sec)

count(*)是比较特殊的情况，它计算的是数据行，而不是某个列的值，而count(age)则表示对age列进行非null值的统计。

mysql> SELECT COUNT(*), COUNT(age) FROM customer;
+----------+------------+
| COUNT(*) | COUNT(age) |
+----------+------------+
|
6 |
4 |
+----------+------------+
1 row in set (0.04 sec)

最后

以上就是平常毛豆最近收集整理的关于【MySQL常见错误】关于MySQL NULL值的处理前言1. 一道测试题2. 如何判断null值3. Null不要进行算术运算4. 使用GROUP BY、ORDER BY5. COUNT()、 MIN()和 SUM()忽略 NULL值的全部内容，更多相关【MySQL常见错误】关于MySQL内容请搜索靠谱客的其他文章。