【MySQL常见错误】关于MySQL NULL值的处理

前言

从概念上看，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)