表属性设置为 NULL，你可能要面临很多麻烦

我们知道使用 MySQL 去存储数据，我们需要先去建表，那么在表中去添加各个数据列，其实我们需要给数据列指定一些属性，比如说我们在列属性设置为允许，那就可以不放置任何数据到这一列中。但是这会带来哪些麻烦或者是坑呢？那么带着这个问题，我们来聊聊表属性设置为 NULL，你可能要面临很多麻烦。

为什么会有很多人用 NULL 呢？

第一点，NULL 它是一个默认行为，如果你不去主动指定这个列字段是 NOT NULL，那么它就是 NULL，这其实是允许 NULL 列最常见的原因了。很多刚开始学习使用 MySQL 的同学，没有显示的去指定当前列是 NOT NULL ，那么 MySQL 会自动的设置为当前列是允许 NULL 的。

第二点，有一个很严重的误区，就是很多初学者会认为 NULL 它代表的是空，也就是 Mysql 什么都不会存储，所以使用那可以节省存储空间， 但是实际上这是一个误区，那它是会占用存储空间的。

第三点，NULL 属性非常方便，SQL 语句或者代码不需要额外的填充或判断。这个说法看起来非常合理，我们在操作数据时，如果某一列是空值，那么无论是 SQL 语句还是代码，我们都不需要去考虑它。

但是即使是有这么多原因，这么多人使用允许 NULL 的属性列，我们似乎还是会经常听到，那么使用 NULL 列并不好，那么我们到底应该去怎样做选择呢？也就是去应该听谁的呢？

我们当然不能仅凭听说去认证哪种选择会更好，也不能认为使用的人多就是对的。那么为了搞清楚这个问题，我们可以看看官网对允许 NULL 列的一个描述。

看看 MySQL 官网对 NULL 列的描述

在 MySQL 的官网上可以看到如下的解释：

NULL columns require additional space in the rowto record whether their values areNULL.For MyISAM tables, each NULL columntakes one bit extra, rounded up to thenearest byte.

翻译过来就是：MySQL 难以优化引用可空列的一个查询，它会使索、引索引统计和值更加复杂。可空的列需要更多的存储空间，还需要 MySQL内部进行特殊处理。可空列被索引之后，每条记录都需要一个额外的字节，它还会导致像 MySAM 中固定大小的索引变成可变大小的索引。

我们从官网这一段描述中就可以看出来，其实 NULL 列它不仅仅会占用额外的存储空间，而且会对索引优化有影响。

总结下来就是说，MySQL 并不建议我们去允许 NULL 列。那么究竟 NULL 列会对我们的业务逻辑造成哪些影响呢？接下来我们就去探究一下这个问题。

NULL 列存在的问题

NULL 的长度并不是零

我们一直在说 NULL 列实际上是占据空间的，我们可以去执行一个 SQL 语句去查看一下。

我们打开 MySQL 客户端，我们可以去通过 select，使用MySQL 中的 length 函数，比如说我们可以去看一看空字符串所占据的空间，看一看 NULL 所占据的空间，int 类型的数字占据的内存空间，字符串零所占据的内存空间。

注意：MySQL 数据库是不区分大小写的。

我们可以看到 MySQL打印的这个 NULL 的长度是 NULL 而不是零。这其实是 MySQL 做的一个特殊处理，或者是叫标记。

以此也可以得出结论，那么为了标识这一列是 NULL，需要额外的存储空间，至少它并不是零。如果是零的话，它会直接打印零。

接下来为了更方便的去演示 NULL 列参与的查询、计算以及这个聚合等等操作，我们先去创建一张数据表，并且插入一些演示数据。

我已经准备了一张数据表以及一些演示数据，这些数据我都会提供给大家。

创建数据库。

create database db_mysql_escape;

创建完了之后，切换当前的数据库。

use db_mysql_escape;

创建数据库表。

CREATE TABLE `db_mysql_escape`.`t_do_not_use_null` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `one` varchar(10) NOT NULL,
    `two` varchar(20) DEFAULT NULL,
    `three` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_one` (`one`),
    KEY `idx_two` (`two`),
    UNIQUE KEY `idx_three` (`three`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建成功，我们去看一看这个表的一些属性列。

这张表结果非常简单，除了这个主键 id 之外，它只有三个数据列，我们分别叫做 one、two、three 这三个字段。它们的 type 类型都是 varcher 的类型。其中对于这个 one 字段，它是不允许空的，也就是不允许为 NULL。对于 two 和 three 我们都没有写到的，所以它是遵循 MySQL 的一个默认的数据列的性质，也就是为 NULL，是一个默认的。

此外，我们去跟这张表定制了一些索引，从建表语句可以看到 one 和 two 都有索引，three 是有一个唯一性索引的，这就是关于这张表的一些这个基本属性。三个索引以及三个字段，两个字段是允许 NULL，一个字段是不允许 NULL。

插入演示数据。

-- 初始化一些数据
INSERT INTO `db_mysql_escape`.`t_do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (1, '', 'a2', 'a3');
INSERT INTO `db_mysql_escape`.`t_do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (2, 'b1', NULL, 'b3');
INSERT INTO `db_mysql_escape`.`t_do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (3, 'c1', 'c2', NULL);
INSERT INTO `db_mysql_escape`.`t_do_not_use_null`(`id`, `one`, `two`, `three`) VALUES (4, 'd1', 'd2', NULL);

执行查看插入数据 SQL 语句。

查看数据。

select * from t_do_not_use_null;

这些数据也非常简单，这里这个为空的地方它不是 NULL，如果是 NULL 的话， MySQL 会直接打印的是 NULL。

这里的 one 插入的数据其实是空字符串，我们去看一看 insert 语句，这里是一个空字符串，这里插入数据的地方如果是 NULL，MySQL 会显示 NULL，非常简单的四条数据。

NULL 参与的查询

NULL 参与的查询存在的问题是，如果你要查询的某个字段为 NULL 或者是不为 NULL。那我们不能使用等于号或者是不等于号，而需要去使用 IS NULL 和 IS NOT NULL 去做判断。

案例演示，我们给大家去演示一下。

比如对于 two 来说，id 为 2 的这行数据是 NULL。我们尝试去通过等于号或者是不等于去实现查询。

使用 = 查询 NULL 的结果。

select * from t_do_not_use_null where two = null;

查询结果。

可以看到它返回了一个空的数据集，也就是我们通过这个等于号，NULL 的结果是查不到的。

那么看一看，我们通过不等于再去查询，那么我们可以猜想，不等 NULL ，是否会返回这个 id 为 1、2和 3 的数据呢？

使用 != 查询 NULL 。

select * from t_do_not_use_null where two != null;

通过案例演示，返回也空结果。

这就是关于 NULL 参与查询的一个坑。如果你使用 = 或者是 != ，你所查询的数据都得不到。此时，我们需要将!=换成 IS NOT NULL。

使用 IS NOT NULL 查询 NULL

select * from t_do_not_use_null where two is not null;

查询结果

可以看到这里返回的是 id 为 1、3 和 4 的数据。

如果需要查询 two 字段中某条记录为 NULL，此时我们需要使用 IS NULL。

使用 IS NULL 查询记录为 NULL

select * from t_do_not_use_null where two is  null;

使用 IS NULL 查询返回的结果如下。

我们可以看到返回了这个 id 为 2 的这样一条记录。这就是关于 NULL 列的一个参与的一个查询。

我们再去看一个查询，假设我们此时将条件改为 two != 'e'。想想此时会得到什么样的结果呢？

select * from t_do_not_use_null where two != 'e';

查询得到的结果如下。

可以看到，当 two !='e' 的时候，返回的仍然是1、3、4 这个三条记录，它与这个 IS NOT NULL 返回的结果是相等的。

其实关于这个 where two 使用到 !=这个符号去判断符号之后，它其实是等价于 where two != 'e' and IS NOT NULL 。

select * from t_do_not_use_null where two != 'e' and two IS NOT  NULL;

查询的结果如下图所示。

一定需要理解对于这个 NULL 列参与的查询一定是使用到 IS NULL 或者是 IS NOT NULL。

如果 where 中，你使用了这样一个 = 号或者是 != 号，它都会给你加上某一个字段 IS NOT NULL 这样的一个属性，这就是关于 NULL 参与查询的一个特性。

NULL 对索引的影响

接下来我们去看 NULL 对索引的影响。MySQL 会对这个 NULL 列也加上索引，但是只有 IS NULL 的查询才会使用到索引。

之前我们去创建 t_do_not_use_null 张表结果的时候，对于这个two字段是给它加上了索引的，也就是 MySQL 库它允许对这个 NULL 列加上索引。

我们通过 EXPLAIN 或者是 DESC，我们去检验一下 NULL 的列对象索引的影响。

使用 EXPLAIN 进行 two 字段的 NULL 的列进行索引分析

explain select * from t_do_not_use_null where two != 'e'\G

使用 EXPLAIN 分析的结果如下所示。

我们可以看到 EXPLAIN 或者是 DESC命令，它返回的信息如上图所示，其中 key 字段是一个空，可能用到的索引是 idx_two，但是 key 字段是实际应用的索引，也就是为 NULL ，也就是我们当前的这样一条查询语句并不会使用到 two 这一列上建立的索引，这就是 NULL 对索引的一个影响。

只有你去使用 IS NULL这样的查询，那才会使用到索引。比如把条件 two !='e' 改成 two is null 。

explain select * from t_do_not_use_null where two is null;

分析结果如下图所示。

明显的可以看到，可能使用到的索引是 idx_two，但是实际上使用的索引也是 idx_two。这就是因为这个 NULL 对索引的一个影响，大家需要明白，这也是 MySQL 中一个比较大的坑。当你对允许 NULL 的列加上索引，MySQL 很难对你的查询进行优化。这也就是我们为什么不建议你去使用 NULL 的一个重要的原因。

我们在之前创建表的时候，three 字段呢的是一个文献索引，但是我们仍然插入了两条为 NULL的记录。

select * from t_do_not_use_null;

那么S这个字段我们加上了唯一性索引，但是可以看到 id为 3 和 4 记录，它们的这一列都是 NULL ,虽然 three 列定义了唯一性索引，但是也可以认为这一个唯一性失效了，也就是允许插入一条为NULL 或者是插入多条为 NULL 的记录，也就是说唯一性索引对 NULL 是不生效的。这也是 MySQL 数据库中 NULL 对索引的一个影响。

NULL 参与计算

NULL 参与计算的时候，如果允许某个字段是 NULL，那么这个字段的数据类型从程序的角度来说是不统一的。你很难把 NULL 跟整数或者字符串当作是同一个类型去处理。所以 MySQL 规定，NULL 值与任何的其他值进行计算，结果总是得到一个NULL，我们去验证一下这一点。

验证 1+2

select 1 + 2;

验证的结果如下图所示。

验证 1+NULL

select 1 + null;

验证 1+NULL 结果如下图所示。

如果我们让 int + NULL ,它会返回的就是 NULL。

验证字符串与 NULL 拼接

select concat('mysql',null);

验证字符串与 NULL 拼接结果如下图所示。

可以看到这里的结果也是 NULL，这就是允许 NULL的列造成数据不统一的一个结果。MySQL 就规定 NULL 值与任何其他类型的值去计算的时候，结果一定得到的是 NULL。

NULL 参与聚合

NULL 参与聚合也是一个非常重要的坑，也是一个非常容易引起问题的一个坑。我们去看一看参与聚合函数的 count 函数。

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

select count(*),count(three) from t_do_not_use_null;

查询执行结果如下图所示。

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

如果你对允许为 NULL 的列去使用到 count 聚合函数，它返回的实际上是不为 NULL 这一列的这一行记录的数据。

所以对某一个列字段，你对它进行 count 聚合的时候，如果这一列值 NULL，那么它所返回的结果是不包含字段为 NULL 的记录。

聚合函数跟 NULL 去结合的时候，也会去产生一个不一样的一个效果。如果在某列存在 NULL 值时，就使用 count(*) 进行数据统计。

总结：count(*) 会统计为 NULL 的行，为 count(列名) 不会统计此列为 NULL 值的行。

NULL 参与排序。

不知道大家有每有去考虑过，在 MySQL 中对数据进行排序。如果对某一列进行排序的时候，如果这一列会出现 NULL 值，那么 MySQL 会如何进行处理呢？

我们先去尝试对t_do_not_use_null 数据表进行一个排序，比如我们数据表中的这个 two 字段它是允许为 NULL，且它里面存在着一些数据为 NULL，那我们就尝试对 two 字段进行一个排序。

MySQL 排序分为正向排序和逆向排序两种排序方式，正向排序排序使用 ASC，逆向排序使用 DESC。

使用 ASC 正向排序

select * from t_do_not_use_null order by two asc;

排序查询结果如下所示。

从以上可以看出，除了 NULL 的列排到最前面之外，其他的记录都会按照字符序列顺序去排。

使用 ASC 逆向排序

select * from t_do_not_use_null order by two desc;

排序查询结果如下所示。

反过来也是一样，NULL 的列排在最后面。

注意：这只是 MySQL 的一种规定或者是一种约定，而不是 NULL 的列更小。

因为 MySQl实在是不知道根据什么去考虑、去排序，所以 MySQL 就给一个默认的规定，正序就排在最前，逆序就排在最后，只是一种约定。

这里给大家去讲解了这个 NULL 它存在的一些问题。

应该用什么去代替 NULL 呢？

第一个，使用特殊值去填充 NULL，例如：空字符串或者是数字0。这是最常见的一个替代方法。但是也需要注意，去结合你所处理的这个业务逻辑实现一个适配。比如对于这个字符类型来说，某一列是字符类型，那么如果这一列确实没有值，你可以去填充一个空字符串。对于数字类型也是一样的，如果这一列不填充什么内容，那么数字零正好也可以去表达一下。

第二个，对于已经存在的数据表，填充特殊值到 NULL 这一列，再去修改表结构，也是去修改列的限制是 NOT NULL 的，这个是一个更好的做法。这样它不仅仅规避了当前表中的NULL 的列，而且对表结构进行一个修改限制，这样在将来也不会出现 NULL 的列，因为我们的表不允许为 NULL 的列出现了，你已经修改了这个列的限制是 NOT NULL ，，将来也就不会出现 NULL 问题。

最后我们就可以得出结论，那么在任何场景下，你都不应该考虑去使用 NULL。也就是因为 NULL 存在各种各样问题，或者说是一些特殊的特性。