【MySQL常见错误】关于MySQL NULL值的处理前言1. 一道测试题2. 如何判断null值3. Null不要进行算术运算4. 使用GROUP BY、ORDER BY5. COUNT()、 MIN()和 SUM()忽略 NULL值

60 阅读 0 评论 40 点赞

我是靠谱客的博主平常毛豆，最近开发中收集的这篇文章主要介绍【MySQL常见错误】关于MySQL NULL值的处理前言1. 一道测试题2. 如何判断null值3. Null不要进行算术运算4. 使用GROUP BY、ORDER BY5. COUNT()、 MIN()和 SUM()忽略 NULL值，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

文章目录

前言
1. 一道测试题
2. 如何判断null值
3. Null不要进行算术运算
4. 使用GROUP BY、ORDER BY
5. COUNT()、 MIN()和 SUM()忽略 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)