关于MySQL-null值的处理

建表sql

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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql> CREATE TABLE `test` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `name` varchar(255) DEFAULT NULL,
-> `age` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> );

mysql> INSERT INTO `test` VALUES (1, 'wuqihua', 18);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (2, 'yanzu', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (3, 'heshandashabi', 12);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `test` VALUES (4, 'chouming', NULL);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` VALUES (5, NULL, NULL);


mysql> select * from test;
+----+---------------+------+
| id | name | age |
+----+---------------+------+
| 1 | wuqihua | 18 |
| 2 | yanzu | NULL |
| 3 | heshandashabi | 12 |
| 4 | chouming | NULL |
| 5 | NULL | NULL |
+----+---------------+------+
5 rows in set (0.00 sec)

mysql> select * from test where age <=> null;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | yanzu | NULL |
| 4 | chouming | NULL |
| 5 | NULL | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from test where age is null;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | yanzu | NULL |
| 4 | chouming | NULL |
| 5 | NULL | NULL |
+----+----------+------+
3 rows in set (0.00 sec)

mysql> select * from test where age is not null;
+----+---------------+------+
| id | name | age |
+----+---------------+------+
| 1 | wuqihua | 18 |
| 3 | heshandashabi | 12 |
+----+---------------+------+
2 rows in set (0.00 sec)

聚合函数和特殊类型的处理

Aggregate (group) functions such as COUNT(), MIN(), and SUM() ignore NULL values. The exception to this is COUNT(*), which counts rows and not individual column values

mysql对一些特殊类型的处理,timestamp类型的插入null值会显示当前时间戳,自增主键插入null会显示下一个value

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
42
43
44
45
46
mysql> CREATE TABLE `test2` (
-> `id` int NOT NULL AUTO_INCREMENT,
-> `dateTime` datetime ,
-> `date` date ,
-> `timestamp` timestamp ,
-> PRIMARY KEY (`id`)
-> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test2 values(1,null,null,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+----+----------+------+---------------------+
| id | dateTime | date | timestamp |
+----+----------+------+---------------------+
| 1 | NULL | NULL | 2021-02-01 16:46:39 |
+----+----------+------+---------------------+
1 row in set (0.00 sec)

mysql> insert into test2 values(1.2,null,null,null);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test2 values(1.7,null,null,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+----+----------+------+---------------------+
| id | dateTime | date | timestamp |
+----+----------+------+---------------------+
| 1 | NULL | NULL | 2021-02-01 16:46:39 |
| 2 | NULL | NULL | 2021-02-01 16:47:19 |
+----+----------+------+---------------------+
2 rows in set (0.00 sec)

mysql> insert into test2 values(null,null,null,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+----+----------+------+---------------------+
| id | dateTime | date | timestamp |
+----+----------+------+---------------------+
| 1 | NULL | NULL | 2021-02-01 16:46:39 |
| 2 | NULL | NULL | 2021-02-01 16:47:19 |
| 3 | NULL | NULL | 2021-02-01 16:48:12 |
+----+----------+------+---------------------+
3 rows in set (0.00 sec)

mysql if empty set return 0

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
mysql> select count(`date`) from test2 where id > 4 group by id order by id desc limit 1;
Empty set (0.00 sec)


mysql> select ifnull((select count(`date`) from test2 where id > 4 group by id order by id desc limit 1),0);
+-----------------------------------------------------------------------------------------------+
| ifnull((select count(`date`) from test2 where id > 4 group by id order by id desc limit 1),0) |
+-----------------------------------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)


mysql> (select case when count(1) >0 then `date` else 0 end as test222 from test2 where id > 4 group by id order by id desc limit 1) union (select 0) limit 1;
+---------+
| test222 |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)


mysql> select COALESCE((select case when count(1) >0 then `date` else 0 end as test222 from test2 where id > 4 group by id order by id desc limit 1),0);
+---------------------------------------------------------------------------------------------------------------------------------------------+
| COALESCE((select case when count(1) >0 then `date` else 0 end as test222 from test2 where id > 4 group by id order by id desc limit 1),0) |
+---------------------------------------------------------------------------------------------------------------------------------------------+
| 0 |
+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
0%