详解MySQL的NULL值

51 阅读 0 评论 34 点赞

我是靠谱客的博主粗心白猫，最近开发中收集的这篇文章主要介绍详解MySQL的NULL值，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

1.概述

NULL在MySQL中是一个非常特殊的值，官方表述为“一个未知的值”，它与其它数据类型的值均不相同。
本文将从多个角度来阐述NULL值的特殊性。

2.前期准备

为了便于演示，先创建一个用来操作的数据表，表结构如下

CREATE TABLE `mytest_null_tbl` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT 'name value',
`phone` varchar(32) DEFAULT NULL COMMENT 'phone number',
`age` tinyint(3) unsigned DEFAULT NULL COMMENT 'age value',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='mytest table for null value'；

然后插入几条数据

mysql> insert into mytest_null_tbl(name,phone,age) values('Lucy','9299008',18);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone) values('Taylor',0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone) values('Arwiel','');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone,age) values('Jenifor','5622890',16);
Query OK, 1 row affected (0.01 sec)

现在看一下插入的数据情况

mysql> select * from mytest_null_tbl;
+----+---------+---------+------+
| id | name
| phone
| age
|
+----+---------+---------+------+
|
1 | Lucy
| 9299008 |
18 |
|
2 | Taylor
| 0
| NULL |
|
3 | Arwiel
|
| NULL |
|
4 | Jenifor | 5622890 |
16 |
+----+---------+---------+------+

3. NULL值的操作

要操作NULL值，是不可以用数学比较运算符的，因为NULL值与其它所有值的比较结果都是FALSE。
对NULL的操作，MySQL提供了IS NULL和IS NOT NULL，还有一个IFNULL()的方法。
IS NULL表示判断某个值为NULL，IS NOT NULL则刚好相反，表示某个值不是NULL。

mysql> select * from mytest_null_tbl where age is null;
+----+--------+-------+------+
| id | name
| phone | age
|
+----+--------+-------+------+
|
2 | Taylor | 0
| NULL |
|
3 | Arwiel |
| NULL |
+----+--------+-------+------+
2 rows in set (0.00 sec)
mysql> select * from mytest_null_tbl where age is not null;
+----+---------+---------+------+
| id | name
| phone
| age
|
+----+---------+---------+------+
|
1 | Lucy
| 9299008 |
18 |
|
4 | Jenifor | 5622890 |
16 |
+----+---------+---------+------+
2 rows in set (0.01 sec)

IFNULL(param1, param2)，用来逻辑判断"如果参数1的值是NULL，则返回参数2的值；否则就返回参数1的值"。

mysql> select *,IFNULL(age,0) from mytest_null_tbl;
+----+---------+---------+------+---------------+
| id | name
| phone
| age
| IFNULL(age,0) |
+----+---------+---------+------+---------------+
|
1 | Lucy
| 9299008 |
18 |
18 |
|
2 | Taylor
| 0
| NULL |
0 |
|
3 | Arwiel
|
| NULL |
0 |
|
4 | Jenifor | 5622890 |
16 |
16 |
+----+---------+---------+------+---------------+
4 rows in set (0.02 sec)

4.NULL值的插入与更新

正常来说，NULL值可以插到任何数据类型的字段中，以上表为例，做一次update操作看看对varchar，int的操作结果。

mysql> update mytest_null_tbl set phone=null,age=null where id=4;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> select * from mytest_null_tbl;
+----+---------+---------+------+
| id | name
| phone
| age
|
+----+---------+---------+------+
|
1 | Lucy
| 9299008 |
18 |
|
2 | Taylor
| 0
| NULL |
|
3 | Arwiel
|
| NULL |
|
4 | Jenifor | NULL
| NULL |
+----+---------+---------+------+
4 rows in set (0.00 sec)

可以看到NULL值被成功的更新到了表中。
那么，如果不希望字段被插入NULL值应该怎么办？这时候设置字段属性为NOT NULL就可以达到目的。

mysql> alter table mytest_null_tbl add address varchar(32) not null default '' comment 'address info';
Query OK, 0 rows affected (0.05 sec)
Records: 0
Duplicates: 0
Warnings: 0
mysql> insert into mytest_null_tbl(name,phone,age) values(null,null,null);
Query OK, 1 row affected (0.00 sec)
mysql> insert into mytest_null_tbl(name,phone,age,address) values(null,null,null,null);
ERROR 1048 (23000): Column 'address' cannot be null
mysql> select * from mytest_null_tbl;
+----+---------+---------+------+---------+
| id | name
| phone
| age
| address |
+----+---------+---------+------+---------+
|
1 | Lucy
| 9299008 |
18 |
|
|
2 | Taylor
| 0
| NULL |
|
|
3 | Arwiel
|
| NULL |
|
|
4 | Jenifor | NULL
| NULL |
|
|
5 | NULL
| NULL
| NULL |
|
+----+---------+---------+------+---------+
5 rows in set (0.00 sec)

如果某些数据类型，被设置为了NULL，那会发生什么呢？
看一下timestamp类型。

mysql> alter table mytest_null_tbl add create_time timestamp;
Query OK, 0 rows affected (0.05 sec)
Records: 0
Duplicates: 0
Warnings: 0
mysql> select * from mytest_null_tbl;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
3 | Arwiel
|
| NULL |
| 2019-07-25 15:32:54 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
| NULL |
| 2019-07-25 15:32:54 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)
mysql> update mytest_null_tbl set create_time=null where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> select * from mytest_null_tbl;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
3 | Arwiel
|
| NULL |
| 2019-07-25 15:32:54 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
| NULL |
| 2019-07-25 15:34:29 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)

可以看到，对timestamp类型的字段插入NULL值时，MySQL实际是将操作的最新时间插入到了记录里。
那如果是对一个自增的int型做这个操作会如何呢？来看一下。
mytest_null_tbl的id列虽然是auto_increment，但是设置了not null，所以需要建一个新表来做测试。

mysql> create table mytest_null_tbl2 (
-> id int unsigned auto_increment,
-> primary key(id)
-> )engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from mytest_null_tbl2;
Empty set (0.00 sec)
mysql> insert into mytest_null_tbl2(id) values(null),(null),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3
Duplicates: 0
Warnings: 0
mysql> select * from mytest_null_tbl2;
+----+
| id |
+----+
|
1 |
|
2 |
|
3 |
+----+
3 rows in set (0.00 sec)

可以看到，对具有AUTO_INCREMENT属性的字段插入NULL值时，MySQL插入到表中的是插入列的顺序的自增的值。

5.归并与排序

NULL值是可以参与到distinct、group by、order by的操作中的，在这些操作中，所有的NULL值都被视为相等的。
要注意的是，NULL值与0、" "是不同的，虽然三者都可以表示为空，但它们是不相等的。

mysql> select * from mytest_null_tbl;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
3 | Arwiel
|
| NULL |
| 2019-07-25 15:32:54 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
| NULL |
| 2019-07-25 15:34:29 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)
mysql> select distinct(phone) from mytest_null_tbl;
+---------+
| phone
|
+---------+
| 9299008 |
| 0
|
|
|
| NULL
|
+---------+
4 rows in set (0.00 sec)

在测试用的这些数据中，distinct(phone)和group by phone所得出的结果是一致的。
在ORDER BY操作中，NULL值是被做为最小值存在的，ASC时排在最前，DESC时排在最末。

mysql> update mytest_null_tbl set age=20 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> update mytest_null_tbl set age=12 where id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> select * from mytest_null_tbl;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
3 | Arwiel
|
|
20 |
| 2019-07-25 15:50:04 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
|
12 |
| 2019-07-25 15:50:14 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from mytest_null_tbl order by age;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
|
12 |
| 2019-07-25 15:50:14 |
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
3 | Arwiel
|
|
20 |
| 2019-07-25 15:50:04 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from mytest_null_tbl order by age desc;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
3 | Arwiel
|
|
20 |
| 2019-07-25 15:50:04 |
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
|
12 |
| 2019-07-25 15:50:14 |
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)

6.统计与计算

对MySQL的统计和计算函数来讲，NULL值是根据实现目的不同而差异对待的。
COUNT()、MIN()、SUM()，对它们来讲，NULL是无效的，在计算时是被忽略的；
而COUNT(*)则是特例，它会计算所有的行，即使有NULL值存在，在使用的时候要注意这一点。

mysql> select * from mytest_null_tbl;
+----+---------+---------+------+---------+---------------------+
| id | name
| phone
| age
| address | create_time
|
+----+---------+---------+------+---------+---------------------+
|
1 | Lucy
| 9299008 |
18 |
| 2019-07-25 15:32:54 |
|
2 | Taylor
| 0
| NULL |
| 2019-07-25 15:32:54 |
|
3 | Arwiel
|
|
20 |
| 2019-07-25 15:50:04 |
|
4 | Jenifor | NULL
| NULL |
| 2019-07-25 15:32:54 |
|
5 | NULL
| NULL
|
12 |
| 2019-07-25 15:50:14 |
+----+---------+---------+------+---------+---------------------+
5 rows in set (0.00 sec)
mysql> select count(*) from mytest_null_tbl;
+----------+
| count(*) |
+----------+
|
5 |
+----------+
1 row in set (0.00 sec)
mysql> select count(age) from mytest_null_tbl;
+------------+
| count(age) |
+------------+
|
3 |
+------------+
1 row in set (0.00 sec)
mysql> select min(age) from mytest_null_tbl;
+----------+
| min(age) |
+----------+
|
12 |
+----------+
1 row in set (0.02 sec)
mysql> select sum(age) from mytest_null_tbl;
+----------+
| sum(age) |
+----------+
|
50 |
+----------+
1 row in set (0.00 sec)

7.总结

1）NULL只支持IS NULL、IS NOT NULL、IFNULL()操作；
2）NULL对数学比较运算符（>, =, <=, <>）运算出的结果都是FALSE；
3）索引列是允许存在NULL的；
4）DISTINCT、GROUP BY、ORDER BY中认为所有的NULL值都是相等的；
5）ORDER BY认为NULL是最小的值；
6）MIN()、SUM()、COUNT()在运算时会忽略NULL值，但是COUNT(*)不会忽略；
7）TIMESTAMP类型的字段被插入NULL时，实际写入到表中的是当前时间；
8）AUTO_INCREMENT属性的字段被插入NULL时，实际写入到表中的是顺序的下一个自增值；
9）想要禁止某个字段被设置为NULL，则对此字段设置NOT NULL属性；
10）如非必要，不要使用NULL，会带来不可预料的麻烦。

最后

以上就是粗心白猫为你收集整理的详解MySQL的NULL值的全部内容，希望文章能够帮你解决详解MySQL的NULL值所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

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

本文分类：mysql
浏览次数：51 次浏览
发布日期：2023-09-22 02:35:51
本文链接：https://www.kaopuke.com/article/k-p-k_14_uzo_2_f5_14__23__18_y.html

详解MySQL的NULL值

概述

1.概述

2.前期准备

3. NULL值的操作

4.NULL值的插入与更新

5.归并与排序

6.统计与计算

7.总结

最后

评论列表共有 0 条评论

发表评论取消回复

详解MySQL的NULL值

概述

1.概述

2.前期准备

3. NULL值的操作

4.NULL值的插入与更新

5.归并与排序

6.统计与计算

7.总结

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

发表评论取消回复