MySQL中空值和NULL的区别,开发过程中注意必坑!

07-14 1004阅读

目录

1. NULL和空值定义上的区别

2. NULL和空值在表中显示的区别

3. NULL值和空值查询方式的区别

3.1 NULL 值的查询方式

3.2 空值的查询方式

3.3 查询NULL的方式可以查询空值

 3.4 查询空值的方法不可以查询NULL值

4.  聚合函数会计算空值但不计算NULL值


1. NULL和空值定义上的区别

在 MySQL 中,NULL 值和空值是两个不同的概念,空值就是我们常说的空字符串,用两个单引号 '' 代替即可;

NULL 值在MySQL中是占用空间的,而空值则是不占用长度空间的。

举个最简单的例子。

如果把数据比作水果,表中的每一个空位比作一个个箱子,水果要放进箱子里存储;

NULL就可以理解为空位上有一个箱子,但箱子是空的,没有存放任何水果;

空值就可以理解为空位上连箱子都没有,真空状态;

2. NULL和空值在表中显示的区别

如下SQL创建一张 user 用户表,

我将邮箱字段 email 和 性别字段 sex 默认值设计为空值,除了主键uid外其余字段默认值设计为NULL

CREATE TABLE `user` (
  `uid` int NOT NULL,
  `username` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT '',
  `sex` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT '',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

(特别注明:MySQL中创建表默认所有字段都是为NULL,上述建表SQL是我修改后展示给各位的,方便小伙伴复制使用,如果有小伙伴是自己直接创建的表,可以通过执行如下SQL修改字段默认值)

# 修改语法格式
ALTER TABLE 表名 
MODIFY 字段名 字段类型(要修改为的长度) DEFAULT '要修改为的默认值';
 
# 举例语句
# 将 user 表中 age字段长度改为 30,默认值改为kong '',如果只修改默认值,长度可以写原本的值
ALTER TABLE `user` MODIFY 
age VARCHAR(30) DEFAULT ''
...... 
如果要更新多个字段,按照字段名,字段类型(字段长度) DEFAULT 默认值的方式追加即可

然后我在表中添加几条数据,有些字段没有设置数据,SQL语句如下,想自己动手的小伙伴自行复制执行;

INSERT INTO `user` VALUES (1, '张三', '12345', 17, '12shfd', '男');
INSERT INTO `user` VALUES (2, '李四', NULL, 18, '', '男');
INSERT INTO `user` VALUES (3, 'NULL', NULL, NULL, '1763@qq', '女');
INSERT INTO `user` VALUES (4, '王五', NULL, NULL, '', '男');
INSERT INTO `user` VALUES (5, NULL, NULL, NULL, '', '');

执行完毕后,数据表如图,从这里我们就可以清晰地看出NULL值和空值的区别。

如果一个字段默认值为NULL我们没有填写数据,在表中就会显示NULL;

如果一个字段默认值为空值我们没有填写任何数据,在表中就是一片空白,不会显示NULL;

这里有一个误区,如果存储的数据中某条数据用户名为NULL,和在存储的过程中没有传入用户名数据库采用用户名默认值NULL不是相等的,这个应该很好理解。如下,id为3的那条数据,表示用户用户名就是NULL,;

MySQL中空值和NULL的区别,开发过程中注意必坑!

3. NULL值和空值查询方式的区别

3.1 NULL 值的查询方式

查询一个字段的值是否为NULL,判断条件为 IS NULL(字段值为默认值NULL) 或 IS NOT NULL(字段值不为默认值NULL);

举例一:查询 user 用户表中字段 username 为 NULL 值的数据:

SELECT * FROM `user` WHERE `user`.username IS NULL

执行SQL,查到的只有id=5的这条数据,符合预期结果;

MySQL中空值和NULL的区别,开发过程中注意必坑!

举例二:查询 user 用户表中字段 username 不为NULL值的数据;

SELECT * FROM `user` WHERE `user`.username IS NOT NULL

执行SQL,查到的是除了刚才id=5以外的四条数据,符合预期结果; 

MySQL中空值和NULL的区别,开发过程中注意必坑!

3.2 空值的查询方式

空值的查询方式和普通字段查询一样,使用 "=" 或 "!=" 即可;

举例一:查询邮箱字段 email 为空值的数据

SELECT * FROM `user` WHERE `user`.email = '';

执行SQL,结果查询到id=2,4,5的三条数据,符合预期

MySQL中空值和NULL的区别,开发过程中注意必坑!

 举例二:查询邮箱 email 和 性别 sex 都不为空值的数据

SELECT * FROM `user`
WHERE `user`.email != ''
AND `user`.sex != '';

执行SQL语句,查询到id=1,3的两条数据,符合预期 

MySQL中空值和NULL的区别,开发过程中注意必坑!

3.3 查询NULL的方式可以查询空值

举例一:查询默认值为空值的字段 email 不为NULL的数据

SELECT * FROM `user` WHERE `user`.email IS NOT NULL;

执行SQL,查到了表中全部五条数据 

MySQL中空值和NULL的区别,开发过程中注意必坑!

举例二:查询默认值为空值的字段email不为NULL值的数据

SELECT * FROM `user` WHERE `user`.email IS NULL;

执行SQL,没有数据,由此也可以说明 空值 != NULL  

MySQL中空值和NULL的区别,开发过程中注意必坑!

 3.4 查询空值的方法不可以查询NULL值

 举例一:查询字段age默认值为NULL的为NULL的数据

SELECT * FROM `user` WHERE `user`.age = NULL;

执行SQL,没有查到任何数据,但也没有报错,

MySQL中空值和NULL的区别,开发过程中注意必坑!

举例二:查询年龄字段age不为NULL的数据

SELECT * FROM `user` WHERE `user`.age != NULL;

执行SQL,结果仍然为空,可以看出,查询空值的办法并不适用于查询NULL值,查询NULL值只能使用IS NULL(为空),IS NOT NULL(不为空),否则会导致查询结果不准确

MySQL中空值和NULL的区别,开发过程中注意必坑!

4.  聚合函数会计算空值但不计算NULL值

聚合函数 COUNT(),MIN(),SUM()等,他们在计算数据的时候会计算空值,却不会计算NULL值,这一点在COUNT() 计数函数中尤为明显;

SELECT COUNT(`user`.age) FROM `user`;

执行SQL,会发现结果是2,为什么呢?因为 user 表中id = 3,id=4,id=5这三条数据的age都是NULL,所以COUNT函数没有将它们计算在内,得出的结果就只有两条数据; 

MySQL中空值和NULL的区别,开发过程中注意必坑!

 我们再来看空值的情况

SELECT COUNT(`user`.email) FROM `user`;

执行SQL,得出结果是5,和表中总记录数5一致,而我们在上面也看到了,表中id=2,id=4,id=5这三条数据的 email 都为空值,但是COUNT函数仍然将它们计算在内,这就是空值和NULL最大的区别

MySQL中空值和NULL的区别,开发过程中注意必坑!

VPS购买请点击我

文章版权声明:除非注明,否则均为主机测评原创文章,转载或复制请以超链接形式并注明出处。

目录[+]