在上节总结 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 值之后，空格排在空字符串后。
