mysql为什么建议创建字段的时候not null

相信大家在建表或者给表新加字段的时候，一些老司机们都会建议我们，字段要定义为not null，原因呢是一是占用存储空间，另一个是避免出现一些意料之外的错误。当然针对这个问题，大家可能也会在网上去搜下，不过网上一些文章有的说不清楚，有的不仅解释不清楚，另外可能会让大家产生新的疑问。

所以今天呢，我们根据文档试着来梳理下not null相关的问题，希望能通过本篇文章让大家对这个null有个更进一步的认识，然后在平时的工作中不用再过多的纠结这个问题。

1. 列允许为null时的存储问题？

要搞清楚这个问题，其实就要了解MySQL表的存储行(row format)格式，当然我们只说InnoDB存储引擎这块。表的存储行格式决定了MySQL的行的物理存储方式，进而会影响查询和DML操作的性能。

MySQL当前版本8.0目前包含这四种格式：REDUNDANT，COMPACT，DYNAMIC，和COMPRESSED；而当前MySQL默认的格式是DYNAMIC；

查看表的存储行格式row_format：SHOW TABLE STATUS LIKE 'test' (test就是要查询的表名)；

因为DYNAMIC有关null的存储和COMPACT是一致的，所以我们直接以COMPACT为例。这里根据官网文档及《Mysql技术内幕-InnoDB存储引擎》，简单画了一下COMPACT格式的存储方式：

当然这里，还会包含一个6字节的事务ID字段和一个7字节的回滚指针字段，另外如果没有为表定义主键，则还会包含一个6字节的row ID字段，这里不是我们本文的重点，这里就不多说了。

• 根据文档，如果行中列为空，则会在null占位这里占用一位(1 bit)，如果行中有两个列为null，则这里会占用两位，以此类推，然后存储的时候，不满8个bit，算1个字节；
• 也就是说如果有1~8个null，那么会占用1个字节，9~16个null，会占用2个字节，如果为null的列的数量是N，那么最终需要的字节数是：CEILING(N/8)。
• 另外，null除了在该位置处会占用空间外，其他不占用空间。官网来源：

The variable-length part of the record header contains a bit vector for indicatingNULLcolumns. If the number of columns in the index that can beNULLisN, the bit vector occupiesCEILING(N/8)bytes. (For example, if there are anywhere from 9 to 16 columns that can beNULL, the bit vector uses two bytes.) Columns that areNULLdo not occupy space other than the bit in this vector.

其实，到这里大家应该对null的占用空间问题清楚了吧 。接下来我们来梳理下null的一些特殊处理场景，这些场景才是我们值得注意的。

2. Mysql针对null的一些特殊逻辑处理

2.1 比较问题

如果查询字段是否为null，需要使用is null；如果要查询不为某个特定值（该值可能为null），如果使用!=，返回结果将不包含为null的数据。

比如想查询test表中 id不为1的记录，我们使用：

select * from demo where abc != 1;

那么查询等同于：

select * from demo where abc != 1 and abc is not null;

2.2 null不能用于范围查询，不能与非null进行比较

这块可以直接看官网的截图：

2.3 null与count

COUNT(column)：统计结果不包含为null的行；COUNT(*) ：对所有行进行统计，不管是否包含null，类似count(1)；当然不仅仅是count方法，min，sum方法都是类似的：

mysql> SELECT COUNT(*), COUNT(age) FROM person; // output：3，2

这里参考自官网：MySQL :: MySQL 5.7 Reference Manual :: B.3.4.3 Problems with NULL Values

2.4 null与order by/group by

根据Mysql文档，order by中，如果使用asc进行排序，那么null值会排在最前面；desc的话，null会排在最后面；

For sorting with ORDER BY, NULL values sort before other values for ascending sorts, after other values for descending sorts.

而group by的话，所有为null的都是相同的，所以group by的时候会为一行；还有DISTINCT操作是一样的；

这里来源：MySQL :: MySQL 5.7 Reference Manual :: 9.1.7 NULL Values

2.5 null与索引

MYSQL 走索引的方式，目前只有 is NULL的情况下会走索引，当前如果联合索引中的查询符合条件也是生效的：

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

来源：MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.13 IS NULL Optimization

当null列被索引的时候，除了正常的数据类型占用的空间，需要额外占用一个字节的存储空间，这点我们一般可以通过执行计划看一下key_en的值，另外这里参考：

Due to the key storage format, the key length is one greater for a column that can beNULLthan for aNOT NULLcolumn.

3. 总结

根据MySQL的官网文档及《mysql技术内幕-存储引擎》和《高性能Mysql》一说，MySQL中存储null的确为额外占用一些空间，但将这些列从null优化为not null，并非会有很大的性能提升。

null的问题主要还是一些逻辑方面的问题，比如可能会使计算复杂些，会让我们处理的时候要多考虑些，亦或者是和其他中间件进行结合的时候，其他中间件对null的处理不太友好而出现一些问题等。

另外再说一点，建议大家多去看下MySQL官网的文档，另外如果英文不太好的小伙伴，可以使用Chrome浏览器的翻译功能，从我个人的体验来说，这个翻译功能还是很不错的。

其他：

为什么开发规范中不推荐NULL？
开发规范中不推荐使用NULL，并且倾向于使用NOT NULL约束，这样做有几个好处：

数据完整性：使用NOT NULL可以确保列中始终有值，这有助于维护数据的完整性和一致性。
避免歧义：NULL在SQL中是一个特殊的值，表示“未知”或“不存在”。它不同于空字符串或0，而且在进行比较和计算时，NULL会导致一些意想不到的结果。避免使用NULL可以减少这种歧义。
性能优化：不使用NULL可以简化数据库的内部处理。例如，您提到的NULL值列表就是InnoDB为了跟踪哪些列包含NULL值而维护的额外数据结构。如果表中的所有列都不允许NULL值，那么InnoDB就不需要维护这个列表，从而节省了空间并可能提高性能。
关于NULL值列表的处理过程简要概括一下：

InnoDB首先会确定哪些列允许存储NULL值。这包括那些没有明确设置为NOT NULL的列。
对于允许NULL值的列，InnoDB会为它们分配一个二进制位，这些位组合在一起形成了NULL值列表。这个列表用于跟踪每一行中哪些列的值是NULL。
二进制位的排列是逆序的，这意味着表中的第一列（如果它允许NULL值）将对应于列表中的最后一个二进制位，而最后一列将对应于第一个二进制位。
当二进制位的值为1时，表示对应的列值为NULL；当值为0时，表示对应的列值不为NULL。
通过这种方式，InnoDB能够高效地存储和检索NULL值信息，尽管这会增加一些额外的存储开销。但是，如果表中的所有列都设置为NOT NULL，那么这个NULL值列表就完全不需要了，从而节省了这部分空间。这也是为什么开发规范中经常推荐尽量避免使用NULL的原因之一。

mysql隐藏列？

每行数据除了用户定义的列之外，还包含两个隐藏列：6字节的事务ID列和7字节的回滚指针列。
对于NULL值，它只占用NULL标志位，不占用额外的存储空间。
此外，如果表没有定义主键，InnoDB还会为每行添加一个6字节的rowid列。这些隐藏列和rowid列对于保证事务的ACID特性和数据的恢复至关重要。