我是靠谱客的博主 平常毛豆,这篇文章主要介绍【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,表中数据如下:
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27mysql> 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的人,年龄未知的也算。
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> select * from customer where age <> 18; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 2 | lisi | 20 | | 4 | xiaoming | 19 | +----+----------+-----+ 2 rows in set (0.03 sec)
正确的处理方式
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20mysql> 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
值
复制代码
1
2
3mysql> select * from customer where age = null; Empty set
正确的方式应该是is null、is not null、ifnull
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34mysql> 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)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13mysql> 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
复制代码
1
2
3
4
5
6
7
8
9
10
11
12mysql> 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值会被分成一组
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15mysql> select age from customer group by age; +------+ | age | +------+ | NULL | | 18 | | 19 | | 20 | +------+ 4 rows in set (0.04 sec)
order by时,null值会显示在最前面
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27mysql> 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值
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41mysql> 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值的统计。
复制代码
1
2
3
4
5
6
7
8
9
10mysql> 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内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复