转载

Sql 语句空字符串和NUll值的区别及使用技巧

在上节总结 Oracle语句判断字符串是否为数字及translate函数解析一章中有对 NULL 值的使用，当时更换为空值进行判断，但是效果不一样。对此查询相关文档对空字符串和 NULL 值的区别进行记录。

Oracle 数据库

创建表

新建一张测试表，用于后续的 Sql 操作校验。

DROP TABLE "spring"."student";
CREATE TABLE "spring"."student" (
"id" NUMBER(5) NOT NULL ,
"num" VARCHAR2(20 BYTE) NOT NULL ,
"desc" VARCHAR2(20 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE
;
COMMENT ON COLUMN "spring"."student"."num" IS '序号';
COMMENT ON COLUMN "spring"."student"."desc" IS '描述';

-- ----------------------------
-- Indexes structure for table student
-- ----------------------------

-- ----------------------------
-- Checks structure for table student
-- ----------------------------
ALTER TABLE "spring"."student" ADD CHECK ("id" IS NOT NULL);
ALTER TABLE "spring"."student" ADD CHECK ("num" IS NOT NULL);

-- ----------------------------
-- Primary Key structure for table student
-- ----------------------------
ALTER TABLE "spring"."student" ADD PRIMARY KEY ("id");
复制代码

表插入操作

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (101, '2019001', 'hresh');--插入成功
复制代码

当设置 desc 字段值为 NULL 时进行插入。

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (102, '2019001', NULL);--插入成功
复制代码

当设置 desc 字段值为空字符串时进行插入。

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (104, '2019001', '');--插入成功
复制代码

当设置 num 字段值为 NULL 或空字符串时进行插入。

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (103, NULL, 'hresh');--失败

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (105, '', 'hresh');--失败
复制代码

当设置 num 字段或 desc 字段值为空格时进行插入。

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (107, '2019001', ' ');--插入空格成功

INSERT INTO "spring"."student" ("id", "num", "desc") VALUES (108, ' ', 'hresh');--插入空格成功
复制代码

插入操作结束后，查看表中数据，如图所示：

由表中数据可以得到以下结论：

对于定义为 char 和 varchar2 类型的字段来说，''(空字符串)就是 null ；
定义为 not null 的字段（num）既不能插入空字符串也不能插入 null；
定义为 null 的字段（desc）可以插入空字符串和 null，且显示结果均为 null；

表查询操作

select * from "student" where "desc" =' '; ----判断空格
----
---查询得到1条记录，id为107

select * from "student" where "desc" =''; ----判断空字符串
----
---无记录

select * from "student" where "desc" is NULL; ----判断null
----
----查询得到2条记录，id为102和104
复制代码

NULL 值总结

什么是 NULL 值？

在我们不知道具体有什么数据的时候，也即未知，可以用 NULL，我们也称它为空值。在 Oracle 中，NULL 值的字段长度为 null。

select "LENGTH"(null) from dual;
----
结果为null
复制代码

NULL 值的存在范围？

Oracle 允许任何一种数据类型的字段为空，除了以下两种情况：

主键字段（primary key），
定义时已经加了NOT NULL限制条件的字段

对空值做加、减、乘、除等运算操作，结果仍为空

SELECT null + 1 from dual;
SELECT null - 1 from dual;
SELECT null * 1 from dual;
SELECT null / 1 from dual;
----
结果均为null
复制代码

NULL 值的处理使用 NVL 函数

SELECT "NVL"(NULL, '521') from dual;
---
521
复制代码

NULL 值比较使用关键字用"is null"和"is not null"

SELECT 1 FROM dual where NULL IS NULL;---结果为1
SELECT 1 FROM dual where NULL = NULL;--结果为null
SELECT 1 FROM dual where '' = '';--结果为null
SELECT 1 FROM dual where "NVL"(NULL, 1)="NVL"(NULL, 1);---结果为1
复制代码

空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(列名)中，用nvl(列名,0)处理后再查

select "COUNT"("desc") from "student";--3
select "COUNT"(1) from "student";--5
select "COUNT"("NVL"("desc", 1)) from "student";--5
复制代码

排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后

select * from "student" ORDER BY "desc";
复制代码

MySQL 数据库

创建表

CREATE TABLE `student` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `num` varchar(5) NOT NULL,
  `desc` varchar(20) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
复制代码

表插入操作

INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('100', 'hresh');--插入成功
复制代码

当设置 desc 字段值为 NULL 时进行插入。

INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('101', NULL);--插入成功
复制代码

当设置 desc 字段值为空字符串时进行插入。

INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('102', '');--插入成功
复制代码

当设置 num 字段值为 NULL 或空字符串时进行插入。

INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES (NULL, 'hresh');--失败

INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES ('', 'hresh');--成功
复制代码

当设置 num 字段或 desc 字段值为空格时进行插入。

INSERT INTO `spring`.`student` (`NUM`, `DESC`) VALUES (' ', ' ');--插入空格成功
复制代码

插入操作结束后，查看表中数据，如图所示：

由表中数据可以得到以下结论：

空字符串和 NULL 值不是一回事；
定义为 not null 的字段（num）只能插入空字符串，不能插入 null 值；
定义为 null 的字段（desc）可以插入空字符串和 null，对应显示空字符串和 null。

表查询操作

SELECT * FROM spring.student WHERE `desc` IS NULL;
---查询得到1条记录，id为3
SELECT * FROM spring.student WHERE `desc` = '';
---查询得到2条记录，id为4和8
SELECT * FROM spring.student WHERE `desc` IS NOT NULL;
----查询得到4条记录，id为4，5，7，8
SELECT * FROM spring.student WHERE `desc` != '';
---查询得到2条记录，id为5，7
复制代码

可以发现 is not null 只会过滤为 null 值的列，而 != 会同时过滤空字符串和 null 值，所以要根据实际情况选择过滤方式。另外，判断 null 值只能用 is null 或 is not null ，不能用 = 或 !=、 <> 。

SELECT count(`desc`) FROM spring.student;--4
复制代码

在进行 count()统计某列的记录数的时候，如果采用的 NULL 值，会别系统自动忽略掉，但是空字符串是会进行统计到其中的。

当使用 ORDER BY 时，首先呈现 NULL 值。如果你用 DESC 以降序排序，NULL 值最后显示。当使用 GROUP BY 时，所有的 NULL 值被认为是相等的，故只显示一行。空字符串排在 NULL 值之后，空格排在空字符串后。

原文 https://juejin.im/post/5dd64e6651882579925b1502

正文到此结束

所属分类： Spring

本文标签： 测试 sql 总结 2019 spring 数据库 value 索引 mysql Oracle key id https Select cache struct 解析 Logging 代码 http db 数据统计 tab UI src
版权声明： 本文为互联网转载文章，出处已在文章中说明(部分除外)。如果侵权，请联系本站长删除，谢谢。
本文海报： 生成海报一生成海报二

其他链接

关于本站

本站定位：个人技术类博客

本站作用：写博客、记日志、闲聊扯淡鼓捣技术。

问题交流

Sql 语句空字符串和NUll值的区别及使用技巧

Oracle 数据库

创建表

表插入操作

表查询操作

NULL 值总结

什么是 NULL 值？

NULL 值的存在范围？

对空值做加、减、乘、除等运算操作，结果仍为空

NULL 值的处理使用 NVL 函数

NULL 值比较使用关键字用"is null"和"is not null"

空值不能被索引，所以查询时有些符合条件的数据可能查不出来，count(列名)中，用nvl(列名,0)处理后再查

排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后

MySQL 数据库

创建表

表插入操作

表查询操作

热门推荐

相关文章

说给你听

本文目录

随机标签

书籍教程

近期评论

网站信息

其他链接

关于本站

问题交流