为什么要尽量将MySQL表字段要设置为NOT NULL？

为什么要尽量将MySQL表字段要设置为NOT NULL？

在MySQL数据库中，设计表字段时，有一个不成文的默认建议：尽量将数据库的表字段默认设置为NOT NULL ，除非业务必须允许 NULL。

在Yearning这样的SQL审计平台中也有类似的规则。同样的问题，也会经常出现在数据库相关的面试当中。今天这篇文章我们从性能优势、开发友好性等方面来深入了解这一优化建议背后的初衷和原理。

查询优化

当数据库的某一列（字段）被设置为NOT NULL时，数据库在查询该列信息时，便可进行优化查询。因为，系统已经明确知道该列内容不存在NULL，可以在查询时直接忽略这种情况，从而提高查询性能。特别是在数据记录比较大的情况下，这一性能优化更加明显。

减少存储空间

数据库中的一条记录最终以行的方式存在储磁盘文件中。在MySQL中，InnoDB默认采用COMPACT的行存储格式。

COMPACT存储格式如下所示，虚线部分代表可选。

这里我们重点关注“NULL值列表”部分，它用于记录数据中值为 NULL 的情况。当存在多个 NULL 值时，这些记录会以逆序方式存储，并且长度必须是 8 位（8bit）的整数倍。如果不足 8 位，则通过高位补 0 来填充。其中，1 表示值为 NULL，0 表示值不是 NULL。如果所有字段都为NOT NULL，那么 NULL 值列表将不会存在。

我们可以看到，当所有的字段都设置为NOT NULL时，那么 NULL值列表就不会存在，因为没有需要标记NULL 的列，此时，对于每一条数据的存储，就可以节省一到多个字节数据存储。数据记录量越大，节省的空间可观。

索引效率提升

如果查询中包含可为NULL的列，对MySql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。

在 MySQL 的 B+树索引里，NULL是一个特殊值，它既不等于任何值，也不大于或小于某个值。于是，涉及 NULL的比较往往需要额外的判断逻辑。

比如：

• col = 5 正常走索引；
• col IS NULL 也可以走索引，但优化器的处理逻辑更复杂；
• col != 5 或 col <> 5，如果列里有 NULL，就会导致结果不可预测，甚至可能放弃走索引。

这意味着同样一张表，允许NULL和不允许NULL，性能上可能会差一个数量级。在高并发场景下，NOT NULL + 合理默认值，比NULL要更稳定。

避免聚合函数失真

举一个简单的例子，假设要统计优惠券的使用情况，如将coupon_no列设置以为NOT NULL则对应的SQL语句如下：

SELECT COUNT(*) 
FROM orders 
WHERE coupon_no != '';

但如果将coupon_no列设置以为NULL，对应的SQL语句则需要添加NULL值的过滤：

SELECT COUNT(*) 
FROM orders 
WHERE coupon_no IS NOT NULL AND
  coupon_no != '';

如果没有对coupon_no字段限制为NOT NULL，在第一条查询条件下，查询的结果很可能无法符合预期，而且还不会报错。

而采用第二条查询语句，使用起来明显麻烦了很多，而且语义也变得不明确了，其他根据此字段进行查询的场景下，都要考虑这种情况。

还有一种聚合失真的情况，如下：

SELECT avg(score) from students;

如果score里有 NULL，MySQL会自动忽略这些记录，从而导致平均分被抬高了。

而如果希望缺考的同学分数为0，则必须用 IFNULL(score, 0)。

常见的聚合函数中MAX、MIN、AVG 和 SUM 函数对 NULL 值采取的处理方式是直接忽略，COUNT函数处理 NULL 值则需要分情况进行讨论。

另外，NULL和其他任何值进行运算的结果都是NULL，也会给数据处理带来了很大的不确定性。

综合来看，NULL带来了额外的心智负担，必须随时想一想，这个聚合是不是忽略了NULL，这个比较是不是排除了NULL。

简化代码逻辑

数据库表字段设置为NOT NULL，可以极大地简化开发人员的代码逻辑。如果字段允许为NULL，那么开发人员在处理这些字段时，需要进行大量的空值判断。

以Java为例：

if (user.getAge() == null){
    // do something
} else if (user.getAge == 0){
    // do someting else
}

在上面的例子中，如果用户表的age字段允许为NULL，那么在使用这个字段进行操作时，就需要不断地进行空指针检查，以避免出现空指针异常。这不仅增加了代码的复杂度，还降低了代码的可读性和可维护性。

如果一开始就约定：年龄字段必须是 NOT NULL，没填就存 0，业务里“0”表示“未知”或“未填写”，岂不是更清晰？所以，NOT NULL其实是对业务逻辑的简化。

提高数据一致性

NOT NULL约束能够在数据库层面强制实施数据一致性约束，从而减少数据质量问题。当数据库表中的某一列被设置为NOT NULL 时，这意味着这一列的每一行都必须有值。这样可以确保数据的完整性和一致性，避免出现数据不完整或不一致的情况。

比如，在某些业务场景下，必须要求用户名不能为NULL值，那么将此字段设置为NOT NULL，则可以在数据库层确保创建用户信息时，用户名字段必须被正确的填写。

并不是所有场景都适合NOT NULL

上面讲了那么多NOT NULL的好处，但并不是所有的场景都适合NOT NULL的。

那么，哪些字段可以允许 NULL？确实业务上“不存在”是一种状态，比如：用户的中间名（Middle Name），删除时间（deleted_at，如果没删除就 NULL），可选的备注信息等。

小结

综上所述，将数据库表字段设置为NOT NULL 整体而言利大于弊，它不仅可以减少存储空间的占用，使数据库的存储更加高效，而且便于开发人员的理解和逻辑代码实现等。因此，关门MySQL字段NOT NULL的最佳实践：字段尽量用NOT NULL ，除非业务必须允许 NULL 。