我是靠谱客的博主 平常毛豆,这篇文章主要介绍【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
27
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的人,年龄未知的也算。

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> 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
20
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

复制代码
1
2
3
mysql> 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
34
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)
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
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

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
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值会被分成一组

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> 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
27
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值

复制代码
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
41
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值的统计。

复制代码
1
2
3
4
5
6
7
8
9
10
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内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(106)

评论列表共有 0 条评论

立即
投稿
返回
顶部