前言
从概念上看,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)