在日常使用数据库时，你在意过NULL么？

其实，NULL值在数据库中是一个很特殊且有趣的存在，正式开始之前，我们先来瞅下MYSQL的配置和版本号，下面我们一起来看看吧；比如你在查询数据库时，想知道一个列（例如：用户注册年限 USER_AGE）是否为 NULL，SQL查询语句该怎么写呢？
是这样：

SELECT * FROM TABLE WHERE USER_AGE = NULL;

还是这样：

SELECT * FROM TABLE WHERE USER_AGE IS NULL;

当然，正确的写法应该是第二种 WHERE USER_AGE IS NULL；

但为什么要这样写呢？进行数据库数据比较的时侯，我们不会使用“IS”关键词？，不是吗？例如，如果我们想要知道一个列的值是不是等于1， WHERE语句是这样的：

WHERE USER_AGE = 1;

思考

那为什么NULL 值要用IS呢？为什么要以这种方式来处理？

因为，在SQL中，NULL表示“未知”的意思。也就是说，NULL值表示的是“未知”的值。

NULL=未知；在大多数数据库中，NULL和空字符串是有很大区别的；

比方说 Oracle就不支持空字符串，它会把空字符串自动转成NULL：

在其他大多数数据库里，NULL值和字符串的处理方式是不一样的：

空字符（""）串虽然表示“没有值”，但这个值是确定的。NULL 表示 “未知值”，这个值是不知道的。这就好比我问了一个问题：“贾玲的小名叫什么？”

有人会回答说：“我咋知道贾玲的小名是什么”。对于这种情况，可以在数据库中使用Nickname列来表示贾玲的小名，而这一列的值就是NULL。

也有人会回答说：“贾玲没有小名。他的父母没有给她取小名，大家虽然一直叫玲小花，但是可能知道贾玲确实没有小名”。对于这种情况，Nickname列应该是一个空字符串，长这样（""）；

Oracle就比较特殊，两个值都是使用 NULL来表示，而其他大多数数据库都会区分对待的。

结论：记住一点 NULL表示的是一个未知值，那么在写SQL查询就会好一些；

例如，你有一个这样的查询语句：

SELECT * FROM SOME_TABLE WHERE 1 = 1;

这个查询会返回所有的行（假设 SOME_TABLE不是空表的话），因为表达式“1=1”一定是对；

那么如果我这样写：

SELECT * FROM SOME_TABLE WHERE 1 = 0;

表达式“1=0”是false，这个查询语句不会返回任何数据的：但是如果我写成这样：

SELECT * FROM SOME_TABLE WHERE 1 = NULL

这个时候，数据库它不知道这两个值（1和NULL）是否相等，因此会认定是“NULL”或“未知”，所以它也不会返回任何数据，和下面讲的NOT (1=NULL)
是有区别的哦！

三元逻辑

SQL 查询语句中的WHERE一般会有下面三种：

可以是 true（会返回数据）；可以是 false（不会返回数据）；也可以是 NULL 或未知（也不会返回数据）；你可能会想：“既然这样，那我为什么要去关心是 false还是 NULL？上面不是都写着不会返回数据吗？”

接下来，我来告诉你在哪些情况下会有问题：我们来看看 **NOT()**方法；

假设有这样的一个查询：

SELECT * FROM SOME_TABLE WHERE NOT(1 = 1);

数据库首先会计算1=1，这个显然是 true；

接着，数据库会用 NOT()条件，所以WHERE返回false；

所以，上面的查询不会返回任何数据。

但如果把语句改成这样：

SELECT * FROM SOME_TABLE WHERE NOT(1 = 0);

数据库首先会计算 1=0，这个肯定是false；

接着，数据库用 **NOT()**函数,得到相反的结果，变成了true，所以，这个语句会返回数据；

但如果把语句再改成下面这样呢？

SELECT * FROM SOME_TABLE WHERE NOT(1 = NULL);

数据库首先计算1=NULL，但是它也不知道1是否等于NULL，因为它也不知道NULL的值是什么？

总之，这个计算不会返回 true，也不能返回false，它会返回一个 NULL；

接下来，NOT()会继续解析上一个计算返回的结果。

当 NOT() 遇到 NULL，它会生成另一个NULL。未知的相反的反面面是另一个未知；

SELECT * FROM SOME_TABLE WHERE NOT(1 = NULL);

所以，对于where条件的NOT(1=1)
和NOT(1=NULL)
：都不会返回数据;

NULL 和 NOT IN

如果我有这样的一个查询语句：

SELECT * FROM TABLE WHERE 1 IN (1, 2, 3, 4, NULL);

很显然，WHERE返回true，这个语句将返回数据，因为1在括号列表里是存在的;

但如果这样写：

SELECT * FROM SOME_TABLE WHERE 1 NOT IN (1, 2, 3, 4, NULL);

很显然，WHERE 返回false，这个查询不会返回数据，因为 1在括号列表里存在，但我们说的是NOT IN；

但如果我们把语句改成这样呢？

SELECT * FROM SOME_TABLE WHERE 5 NOT IN (1, 2, 3, 4, NULL);

这里的 WHERE不会返回数据，因为它的结果不是 true。数字 5在括号列表里可能不存在，可能存在，因为当中有一个NULL 值，数据库也不知道 NULL是个啥？

第一步WHERE会返回NULL，第二步整个查询不会返回任何数据。

希望大家应该都清楚该怎么在 SQL中处理 NULL值；

Null五个坑

• 首先如果存在person表先删；DROP TABLE IF EXISTS person;
  
• 创建 person表，其中 username字段可为空，并为其设置普通索引;

CREATE TABLE person (
 id INT PRIMARY KEY auto_increment,
 name VARCHAR(20),
 mobile VARCHAR(13),
 index(name)
) ENGINE='innodb';

-- person 表添加测试数据
insert into person(name,mobile) values('Java','13333333330'),
 ('MySQL','13333333331'),
 ('Redis','13333333332'),
 ('Kafka','13333333333'),
 ('Spring','13333333334'),
 ('MyBatis','13333333335'),
 ('RabbitMQ','13333333336'),
 ('Golang','13333333337'),
 (NULL,'13333333338'),
 (NULL,'13333333339'); 
  
select * from person;

得到测试数据，如下图所示：有了数据之后，我们就来看列中存在 NULL值时，出哪些问题？

1.count数据丢失

当某列存在NULL值时，再使用count查询该列，就会出现数据“丢失”问题，如下 SQL 所示：

select count(*),count(name) from person;

查询结果是：从上述结果可以看出，当使用count(name)查询时，就丢失了两条值为NULL的数据丢失。

解决方案

如果某列存在 NULL值时，就是用count(*)进行数据统计；

扩展知识：不要使用 count(常量)

阿里巴巴《Java开发手册》强制规定：不要使用 count(列名) 或 count(常量) 来替代 count()，count() 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。说明：count(*)会统计值为 NULL 的行，而count(列名)不会统计此列为 NULL 值的行。

distinct 数据丢失

当使用count(distinct col1, col2) 查询时，如果其中一列为 NULL，那么即使另一列有不同的值，那么查询的结果也会将数据丢失，如下 SQL 所示：

select count(disinct name,mobile) from person;

查询执行结果如下：

图片数据库的原始数据如下：图片从上述结果可以看出手机号一列的 10条数据都是不同的，但查询的结果却为8。

select 数据丢失

如果某列存在 NULL 值时，执行非等于查询（<>/!=）会导致为NULL值的结果丢失。比如以下数据：我需要查询除 name等于“Java”以外的所有数据，预期返回的结果是id 从2 到10的数据，但当执行以下查询时：

select * from person where name<>'Java' order by id;
-- 或
select * from person where name!='Java' order by id;

查询结果均为以下内容：看出为 NULL 的两条数据凭空消失了，这个结果并不符合我们的正常预期:

解决方案

要解决以上的问题，只需要在查询结果中拼加上为 NULL 值的结果即可，执行 SQL 如下：

select * from person where name<>'Java' or isnull(name) order by id;

最终的执行结果如下：

空指针异常

如果某列存在 NULL 值时，可能会导致 sum(column) 的返回结果为 NULL 而非 0，如果 sum 查询的结果为 NULL 就可以能会导致程序执行时空指针异常（NPE），我们来演示一下这个问题:
首先，我们先构建一张表和一些测试数据：

-- 如果存在 goods 表先删除
DROP TABLE IF EXISTS goods; 

-- 创建 goods 表
CREATE TABLE goods (
 id INT PRIMARY KEY auto_increment,
 num int
) ENGINE='innodb';

-- goods 表添加测试数据
insert into goods(num) values(3),(6),(6),(NULL);

select * from goods;

表中原始数据如下：接下来我们使用 sum 查询，执行以下 SQL：

select sum(num) from goods where id>4;

查询执行结果如下：

图片当查询的结果为 NULL 而非 0 时，就可以能导致空指针异常:

解决空指针异常

可以使用以下方式来避免空指针异常：

select ifnull(sum(num), 0) from goods where id>4;

查询执行结果如下：

增加了查询难度

当某列值中有 NULL 值时，在进行 NULL 值或者非NULL 值的查询难度就增加了。

所谓的查询难度增加指的是当进行NULL 值查询时，必须使用 NULL值匹配的查询方法，比如 IS NULL 或者 IS NOT NULL 又或者是 IFNULL(cloumn) 这样的表达式进行查询，而传统的 =、!=、<>... 等这些表达式就不能使用了，这就增加了查询的难度，尤其是对小白程序员来说，不太友好：还是以 person 表为例，它的原始数据如下：

错误用法 1：

select * from person where name<>null;

执行结果为空，并没有查询到任何数据，如下图所示：

错误用法 2：

select * from person where name!=null;

执行结果也为空，没有查询到任何数据，如下图所示：

正确用法 1：

select * from person where name is not null;

执行结果如下：

正确用法 2：

select * from person where !isnull(name);

执行结果如下：

推荐用法

阿里巴巴《Java开发手册》推荐我们使用 ISNULL(cloumn) 来判断 NULL 值，原因是在 SQL 语句中，如果在 null 前换行，影响可读性；而 ISNULL(column) 是一个整体，简洁易懂。从性能数据上分析 ISNULL(column) 执行效率也更快一点。

扩展知识：NULL 不会影响索引

细心的朋友可能发现了，我在创建 person 表的 name 字段时，为其创建了一个普通索引，如下图所示：

然后我们用 explain 来分析查询计划，看当 name 中有 NULL 值时是否会影响索引选择：

explain 的执行结果如下图所示：

从上述结果可以看出，即使 name 中有 NULL 值也不会影响 MySQL 使用索引进行查询:

总结

本文我们讲了为啥我们用IS，三元逻辑以及NOT IN的遇到了咋办？当某列为 NULL 时可能会导致的 5 种问题：丢失查询结果、导致空指针异常和增加了查询的难度。因此在最后提倡大家在创建表的时候尽量设置 is not null 的约束，如果某列确实没有值，可以设置空值（''）或 0 作为其默认值；

最后：大家还有因为 NULL 而造成的各种坑吗？欢迎评论区补充留言。