为什么数据库字段要使用 NOT NULL？

最近刚入职新公司，发现数据库设计有点小问题，数据库字段很多没有 NOT NULL，对于强迫症晚期患者来说，简直难以忍受，因此有了这篇文章。

基于目前大部分的开发现状来说，我们都会把字段全部设置成NOT NULL并且给默认值的形式。

通常，对于默认值一般这样设置：

1. 整形，我们一般使用 0 作为默认值。

2. 字符串，默认空字符串

3. 时间，可以默认1970-01-01 08:00:01，或者默认0000-00-00 00:00:00，但是连接参数要添加zeroDateTimeBehavior=convertToNull，建议的话还是不要用这种默认的时间格式比较好

但是，考虑下原因，为什么要设置成 NOT NULL？

来自高性能 Mysql 中有这样一段话：

尽量避免 NULL

很多表都包含可为 NULL（空值）的列，即使应用程序并不需要保存 NULL 也是如此，这是因为可为 NULL 是列的默认属性。通常情况下最好指定列为 NOT NULL，除非真的需要存储 NULL 值。

如果查询中包含可为 NULL 的列，对 MySql 来说更难优化，因为可为 NULL 的列使得索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySql 里也需要特殊处理。当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM 里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为 NULL 的列改为 NOT NULL 带来的性能提升比较小，所以（调优时）没有必要首先在现有 schema 中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为 NULL 的列。

当然也有例外，例如值得一提的是，InnoDB 使用单独的位（bit）存储 NULL 值，所以对于稀疏数据有很好的空间效率。但这一点不适用于 MyISAM。

书中的描述说了几个主要问题，我这里暂且抛开 MyISAM 的问题不谈，这里我针对 InnoDB 作为考量条件。

1. 如果不设置 NOT NULL 的话，NULL 是列的默认值，如果不是本身需要的话，尽量就不要使用 NULL

2. 使用 NULL 带来更多的问题，比如索引、索引统计、值计算更加复杂，如果使用索引，就要避免列设置成 NULL

3. 如果是索引列，会带来的存储空间的问题，需要额外的特殊处理，还会导致更多的存储空间占用

4. 对于稀疏数据又更好的空间效率，稀疏数据指的是很多值为 NULL，只有少数行的列有非 NULL 值的情况

默认值

对于 MySql 而言，如果不主动设置为 NOT NULL 的话，那么插入数据的时候默认值就是 NULL。

NULL 和 NOT NULL 使用的空值代表的含义是不一样，NULL 可以认为这一列的值是未知的，空值则可以认为我们知道这个值，只不过他是空的而已。

举个例子，一张表中的某一条name字段是 NULL，我们可以认为不知道名字是什么，反之如果是空字符串则可以认为我们知道没有名字，他就是一个空值。

而对于大多数程序的情况而言，没有什么特殊需要非要字段要 NULL 的吧，NULL 值反而会对程序造成比如空指针的问题。

对于现状大部分使用MyBatis的情况来说，我建议使用默认生成的insertSelective方法或者纯手动写插入方法，可以避免新增 NOT NULL 字段导致的默认值不生效或者插入报错的问题。

值计算

聚合函数不准确

对于 NULL 值的列，使用聚合函数的时候会忽略 NULL 值。

现在我们有一张表，name字段默认是 NULL，此时对name进行count得出的结果是 1，这个是错误的。

count(*)是对表中的行数进行统计，count(name)则是对表中非 NULL 的列进行统计。

=失效

对于 NULL 值的列，是不能使用=表达式进行判断的，下面对name的查询是不成立的，必须使用is NULL。

与其他值运算

NULL 和其他任何值进行运算都是 NULL，包括表达式的值也是 NULL。

user表第二条记录age是 NULL，所以+1之后还是 NULL，name是 NULL，进行concat运算之后结果还是 NULL。

可以再看下下面的例子，任何和 NULL 进行运算的话得出的结果都会是 NULL，想象下你设计的某个字段如果是 NULL 还不小心进行各种运算，最后得出的结果。。。

distinct、group by、order by

对于distinct和group by来说，所有的 NULL 值都会被视为相等，对于order by来说升序 NULL 会排在最前

其他问题

表中只有一条有名字的记录，此时查询名字!=a预期的结果应该是想查出来剩余的两条记录，会发现与预期结果不匹配。

索引问题

为了验证 NULL 字段对索引的影响，分别对name 和age添加索引。

关于网上很多说如果 NULL 那么不能使用索引的说法，这个描述其实并不准确，根据引用官方文档[3]里描述，使用 is NULL 和范围查询都是可以和正常一样使用索引的，实际验证的结果好像也是这样，看以下例子。

然后接着我们往数据库中继续插入一些数据进行测试，当 NULL 列值变多之后发现索引失效了。

我们知道，一个查询 SQL 执行大概是这样的流程：

首先连接器负责连接到指定的数据库上，接着看看查询缓存中是否有这条语句，如果有就直接返回结果。

如果缓存没有命中的话，就需要分析器来对 SQL 语句进行语法和词法分析，判断 SQL 语句是否合法。

现在来到优化器，就会选择使用什么索引比较合理，SQL 语句具体怎么执行的方案就确定下来了。

最后执行器负责执行语句、有无权限进行查询，返回执行结果。

从上面的简单测试结果其实可以看到，索引列存在 NULL 就会存在书中所说的导致优化器在做索引选择的时候更复杂，更加难以优化。

存储空间

数据库中的一行记录在最终磁盘文件中也是以行的方式来存储的，对于 InnoDB 来说，有 4 种行存储格式：REDUNDANT、 COMPACT、 DYNAMIC 和 COMPRESSED。

InnoDB 的默认行存储格式是COMPACT，存储格式如下所示，虚线部分代表可能不一定会存在。

变长字段长度列表：有多个字段则以逆序存储，我们只有一个字段所有不考虑那么多，存储格式是 16 进制，如果没有变长字段就不需要这一部分了。

NULL 值列表：用来存储我们记录中值为 NULL 的情况，如果存在多个 NULL 值那么也是逆序存储，并且必须是 8bit 的整数倍，如果不够 8bit，则高位补 0。1 代表是 NULL，0 代表不是 NULL。如果都是 NOT NULL 那么这个就存在了。

ROW_ID：一行记录的唯一标志，没有指定主键的时候自动生成的 ROW_ID 作为主键。

TRX_ID：事务 ID。

ROLL_PRT：回滚指针。

最后就是每列的值。

为了说明清楚这个存储格式的问题，我弄张表来测试，这张表只有c1字段是 NOT NULL，其他都是可以为 NULL 的。

可变字段长度列表：c1和c3字段值长度分别为 1 和 2，所以长度转换为 16 进制是0x01 0x02，逆序之后就是0x02 0x01。

NULL 值列表：因为存在允许为 NULL 的列，所以c2,c3,c4分别为 010，逆序之后还是一样，同时高位补 0 满 8 位，结果是00000010。

其他字段我们暂时不管他，最后第一条记录的结果就是，当然这里我们就不考虑编码之后的结果了。

这样就是一个完整的数据行数据的格式，反之，如果我们把所有字段都设置为 NOT NULL，并且插入一条数据a,bb,ccc,dddd的话，存储格式应该这样：

虽然我们发现 NULL 本身并不会占用存储空间，但是如果存在 NULL 的话就会多占用一个字节的标志位的空间。

文章参考文档：

1. https://dev.mysql.com/doc/refman/8.0/en/problems-with-null.html

2. https://dev.mysql.com/doc/refman/8.0/en/working-with-null.html

3. https://dev.mysql.com/doc/refman/5.6/en/is-null-optimization.html

4. https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html

5. https://www.cnblogs.com/zhoujinyi/articles/2726462.html