NULL值
参考：https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html
https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html
https://dev.mysql.com/doc/refman/8.0/en/null-values.html
- 概念
MySQL的NULL值代表“没有数据”或者“缺少的未知值”，0 和 ’ ’ 都不是NULL，只有两个NULL值才是相等的，例子如下：
MariaDB [(none)]> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL,NULL IS NULL;
+-----------+---------------+------------+----------------+--------------+
| 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL | NULL IS NULL |
+-----------+---------------+------------+----------------+--------------+
| 0 | 1 | 0 | 1 | 1 |
+-----------+---------------+------------+----------------+--------------+
1 row in set (0.00 sec)
- 运算
NULL一般用IS NULL和IS NOT NULL运算符，例子如下：
MariaDB [(none)]> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.00 sec)
NULL值与其他运算符比较后结果仍然为NULL，例如 <、>、=、<>、+ 这些，所以NULL不与其他运算符一起比较，没意义，例子如下：
MariaDB [example]> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL,1+NULL;
+----------+-----------+----------+----------+--------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | 1+NULL |
+----------+-----------+----------+----------+--------+
| NULL | NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+--------+
1 row in set (0.00 sec)
tip：在MySQL里面，0和NULL都是代表false，其他值都是代表true，布尔运算的真值是1
- group by、order by、distinct
使用 group by、order by、distinct 所有NULL值都视为相等
例子如下：
MariaDB [example]> select * from person;
+------+-----------+---------+--------+
| id | name | city | income |
+------+-----------+---------+--------+
| 1 | Xiaoming | NULL | 4500 |
| 2 | Xiaohong | NULL | 5000 |
| 3 | Xiaochen | Nanjing | 5500 |
| 4 | Xiaozhang | Nanjing | NULL |
| 5 | Xiaotian | Beijing | NULL |
+------+-----------+---------+--------+
5 rows in set (0.00 sec)
MariaDB [example]> select * from person group by city;
+------+----------+---------+--------+
| id | name | city | income |
+------+----------+---------+--------+
| 1 | Xiaoming | NULL | 4500 |
| 5 | Xiaotian | Beijing | NULL |
| 3 | Xiaochen | Nanjing | 5500 |
+------+----------+---------+--------+
3 rows in set (0.00 sec)
MariaDB [example]> select * from person order by income;
+------+-----------+---------+--------+
| id | name | city | income |
+------+-----------+---------+--------+
| 4 | Xiaozhang | Nanjing | NULL |
| 5 | Xiaotian | Beijing | NULL |
| 1 | Xiaoming | NULL | 4500 |
| 2 | Xiaohong | NULL | 5000 |
| 3 | Xiaochen | Nanjing | 5500 |
+------+-----------+---------+--------+
5 rows in set (0.00 sec)
MariaDB [example]> select distinct(city) from person;
+---------+
| city |
+---------+
| NULL |
| Nanjing |
| Beijing |
+---------+
3 rows in set (0.00 sec)
- order by … desc/asc
使用order by … desc会把NULL全部放在后面
使用order by … asc会把NULL全部放在前面，例子如下：
MariaDB [example]> select * from person;
+------+-----------+---------+--------+
| id | name | city | income |
+------+-----------+---------+--------+
| 1 | Xiaoming | NULL | 4500 |
| 2 | Xiaohong | NULL | 5000 |
| 3 | Xiaochen | Nanjing | 5500 |
| 4 | Xiaozhang | Nanjing | NULL |
| 5 | Xiaotian | Beijing | NULL |
+------+-----------+---------+--------+
5 rows in set (0.00 sec)
MariaDB [example]> select * from person order by income asc;
+------+-----------+---------+--------+
| id | name | city | income |
+------+-----------+---------+--------+
| 4 | Xiaozhang | Nanjing | NULL |
| 5 | Xiaotian | Beijing | NULL |
| 1 | Xiaoming | NULL | 4500 |
| 2 | Xiaohong | NULL | 5000 |
| 3 | Xiaochen | Nanjing | 5500 |
+------+-----------+---------+--------+
5 rows in set (0.00 sec)
MariaDB [example]> select * from person order by income desc;
+------+-----------+---------+--------+
| id | name | city | income |
+------+-----------+---------+--------+
| 3 | Xiaochen | Nanjing | 5500 |
| 2 | Xiaohong | NULL | 5000 |
| 1 | Xiaoming | NULL | 4500 |
| 4 | Xiaozhang | Nanjing | NULL |
| 5 | Xiaotian | Beijing | NULL |
+------+-----------+---------+--------+
5 rows in set (0.00 sec)
- 合计函数
使用合计函数，例如count()、min()、max()、sum() 这些，NULL不会被统计进去，例子如下：
MariaDB [example]> select * from person;
+------+-----------+---------+--------+
| id | name | city | income |
+------+-----------+---------+--------+
| 1 | Xiaoming | NULL | 4500 |
| 2 | Xiaohong | NULL | 5000 |
| 3 | Xiaochen | Nanjing | 5500 |
| 4 | Xiaozhang | Nanjing | NULL |
| 5 | Xiaotian | Beijing | NULL |
+------+-----------+---------+--------+
5 rows in set (0.00 sec)
MariaDB [example]> select count(city) from person;
+-------------+
| count(city) |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
MariaDB [example]> select sum(income) from person;
+-------------+
| sum(income) |
+-------------+
| 15000 |
+-------------+
1 row in set (0.00 sec)
MariaDB [example]> select min(income) from person;
+-------------+
| min(income) |
+-------------+
| 4500 |
+-------------+
1 row in set (0.00 sec)
MariaDB [example]> select max(income) from person;
+-------------+
| max(income) |
+-------------+
| 5500 |
+-------------+
1 row in set (0.00 sec)
- 插入处理
当一个字段数据类型为timestamp时，插入NULL值，字段会插入当前时间而不是NULL
当一个字段数据类型为整型或者浮点型且为自增时，插入NULL，字段会插入下一个自增值而不是NULL，例子如下：
MariaDB [example]> insert into person(name,city,income,date) values ('Tim','Beijing',6000,NULL);
Query OK, 1 row affected (0.06 sec)
MariaDB [example]> select * from person;
+----+--------+---------+--------+---------------------+
| id | name | city | income | date |
+----+--------+---------+--------+---------------------+
| 1 | Alex | NULL | NULL | 2019-10-29 12:04:32 |
| 2 | Bob | NULL | NULL | 2019-10-29 12:04:32 |
| 3 | Jessie | Nanjing | 3500 | 2019-10-29 12:04:32 |
| 4 | Jefrry | Nanjing | 4000 | 2019-10-29 12:04:32 |
| 5 | Tom | Beijing | 6000 | 2019-10-29 12:04:32 |
| 6 | Joe | Beijing | 7000 | 2019-10-29 12:06:43 |
| 7 | Tim | Beijing | 6000 | 2019-10-29 12:07:34 |
+----+--------+---------+--------+---------------------+
7 rows in set (0.01 sec)
MariaDB [example]> insert into person(id,name,city,income) values (NULL,'Tim','Beijing',6000);
Query OK, 1 row affected (0.07 sec)
MariaDB [example]> select * from person;
+----+--------+---------+--------+---------------------+
| id | name | city | income | date |
+----+--------+---------+--------+---------------------+
| 1 | Alex | NULL | NULL | 2019-10-29 12:04:32 |
| 2 | Bob | NULL | NULL | 2019-10-29 12:04:32 |
| 3 | Jessie | Nanjing | 3500 | 2019-10-29 12:04:32 |
| 4 | Jefrry | Nanjing | 4000 | 2019-10-29 12:04:32 |
| 5 | Tom | Beijing | 6000 | 2019-10-29 12:04:32 |
| 6 | Joe | Beijing | 7000 | 2019-10-29 12:06:43 |
| 7 | Tim | Beijing | 6000 | 2019-10-29 12:07:34 |
| 8 | Tim | Beijing | 6000 | 2019-10-29 12:08:43 |
+----+--------+---------+--------+---------------------+
8 rows in set (0.00 sec)
京公网安备 11010502036488号