转载

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");
复制代码
Sql 语句空字符串和NUll值的区别及使用技巧

表插入操作 

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');--插入空格成功
复制代码

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

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

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

  • 对于定义为 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 允许任何一种数据类型的字段为空，除了以下两种情况：

  1. 主键字段（primary key），
  2. 定义时已经加了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";
复制代码
Sql 语句空字符串和NUll值的区别及使用技巧

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 (' ', ' ');--插入空格成功
复制代码

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

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

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

  • 空字符串和 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

热门推荐

相关文章

阿里云首购8折
说给你听

本文目录
随机标签
书籍教程
springboot-demo Java入门教程 bootstrap3 CSS Apache基础教程 php ionic 教程 Python mysql教程 eclipse Ubuntu VPS系统配置 AngularJS 教程 MongoDB教程 Struts2教程 Redis教程 Spring教程 Git教程 Jenkins进阶系列 openfire参考指南 Java设计模式 HBase教程 Maven教程 hibernate教程 Docker 教程 memcached教程 Quartz指南 Hive教程 Ant教程 ANTLR教程 SpringCloud java实例教程 springcloud-demo XStream教程 Hazelcast教程 Elastic-Job-Lite 深入浅出MyBatis SVN教程 ibaties教程 rabittmq教程 solr教程 Hadoop教程 WebService CXF学习 JPA教程 ActiveMQ中文指南 java-demo Java内存模型 dubbo教程 python3-demo Linux入门视频教程
近期评论
  1. 1 Spring Cloud Consul实现选举机制
  2. 2 Spring Boot集成ShedLock实现分布式定时任务
  3. 3 利用oss进行数据库和网站图片备份
  4. 4 Spring Cloud Stream实现数据流处理
  5. 5 Python3访问MySQL数据库快速入门Demo
  6. 6 Github开源项目作者可以免费申请 JetBrains 全家桶
  7. 7 Spring Cloud Vault快速入门Demo
  8. 8 Spring Cloud Gateway快速入门Demo
  9. 9 Spring Cloud Contract快速入门Demo
  10. 10 Spring Cloud Consul快速入门Demo
网站信息
  • 文章总数：82,714 篇
  • 文件总数：284,287 个
  • 标签总数：2,393 个
  • 分类总数：85 个
  • 留言数量：2,560 条
  • 在线人数：670 人
  • 运行天数：4,409天
  • 最后更新：2024年11月22日03点
Loading...

其他链接

关于本站

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

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

问题交流

[HBLOG]公众号 HBLOG HBLOG