播面

在 MySQL 表设计中，对于可能为空的字段，业界的最佳实践通常是：尽量设置为 NOT NULL 并指定默认值（如 DEFAULT '' 或 DEFAULT 0）。除非在业务逻辑上，该字段的“未知（Unknown）”状态与“零/空（Zero/Empty）”状态有明确的语义区别，才使用 NULL。

下面从 查询层面、索引层面 以及 实际业务场景 三个维度为您详细拆解它们的区别：

---

一、 查询层面的区别（逻辑与计算）

在查询层面，NULL 会引入三值逻辑（True, False, Unknown），这会极大增加 SQL 编写的复杂度和出错概率。

1. 比较操作符陷阱

• NULL：你不能用 = 或 != 来判断 NULL。WHERE col = NULL 永远返回 False（严格说是 Unknown）。必须使用 IS NULL 或 IS NOT NULL。
• DEFAULT：正常使用 WHERE col = '' 或 WHERE col = 0 即可。

2. 聚合函数的差异

• COUNT()：COUNT(col) 会忽略值为 NULL 的行；而如果使用默认值 '' 或 0，COUNT(col) 会将其统计在内。（注意：COUNT(*) 都会统计）。
• SUM() / AVG()：遇到 NULL 时会忽略该行。如果所有行都是 NULL，结果会返回 NULL 而不是 0，这往往会导致应用程序空指针异常（NPE）。

3. 字符串与数学运算

• 字符串拼接：CONCAT('Hello', NULL) 的结果是 NULL。如果不小心，一条记录里只要有一个字段是 NULL，整个拼接结果全毁。如果是 DEFAULT ''，则正常拼接。
• 数学运算：10 + NULL 结果是 NULL。10 + 0 结果是 10。

4. 排序（ORDER BY）

• 在 MySQL 中，NULL 被视为最小值。升序（ASC）时排在最前面，降序（DESC）时排在最后面。这不一定符合业务预期的展示顺序。

---

二、 索引层面的区别（性能与存储）

早期有一个广泛流传的误区：“MySQL 中含有 NULL 的列不能建立索引”。这是错的，InnoDB 一直支持对 NULL 列建索引。但即便如此，NULL 仍然对索引和性能有负面影响。

1. 存储空间的额外开销

• NULL 标志位：InnoDB 存储记录时，如果是允许 NULL 的列，需要额外的空间（NULL 标志位，通常是 1 byte）来记录该行这个字段是否为 NULL。
• DEFAULT：NOT NULL 不需要这个标志位，数据存储更紧凑。

2. 优化器的估算难度（Cardinality）

• 当一个列包含大量 NULL 值时，MySQL 优化器在计算索引的区分度（基数 Cardinality）时会变得更加困难。
• 虽然可以通过 innodb_stats_method 参数（如 nulls_equal, nulls_unequal, nulls_ignored）来调整优化器如何看待 NULL，但这增加了调优的复杂性。使用默认值则没有这个烦恼。

3. 复合索引与范围查询（Range Scan）

• 在某些老版本或特定查询条件下，如果索引列允许 NULL，可能会影响 Index Condition Pushdown (ICP) 等高级优化特性的效率，或者导致放弃使用索引转而全表扫描（尤其是在使用 != 或 OR 时）。

4. 唯一索引（UNIQUE Index）的特殊表现

• 这是 NULL 唯一有“优势”或特殊作用的地方：在 MySQL 中，UNIQUE 约束允许多个 NULL 值存在（因为 NULL != NULL）。
• 如果你用 NOT NULL DEFAULT ''，那么空字符串只能出现一次，第二次插入就会报 Duplicate Key 错误。

---

三、 场景总结：什么时候用什么？

🟢 强烈建议使用 NOT NULL DEFAULT '' / DEFAULT 0 的场景：

1. 数值类计算字段：如金额、积分、点赞数、库存。直接 DEFAULT 0，避免 SUM() 算出 NULL 导致程序崩溃。
2. 字符串描述字段：如姓名、地址、备注。直接 DEFAULT ''，避免 CONCAT() 变成空，也免去代码里疯狂写 if (val != null)。
3. 状态机字段：如订单状态、用户等级。应该用明确的数字或字符串枚举（如 DEFAULT 1），绝对不要用 NULL。
4. 频繁作为查询条件的字段：经常放在 WHERE、GROUP BY、ORDER BY 后的字段，为了索引效率和逻辑严谨，坚决避免 NULL。

🟡 建议（或必须）使用 NULL 的场景：

1. 外键列：如果一行数据的父节点是可选的（比如多级菜单的 parent_id，顶级菜单没有父节点），使用 NULL 比使用 0 更符合关系型数据库的范式。
2. 时间日期字段：例如 deleted_at, shipped_at。如果没有删除或未发货，用 NULL 是最合理的。极度不推荐使用 0000-00-00 00:00:00 作为时间的默认值（新版 MySQL 默认的 SQL_MODE 也会阻止这种行为）。
3. 需要唯一索引，但允许留空：比如用户的“手机号”或“身份证号”。如果用户不填，允许多个用户不填；如果填了，必须全表唯一。这时候只能设为允许 NULL 并建立 UNIQUE 索引。
4. 业务上需要区分“0”和“未知”：
   • 例如：一张考试成绩表，score 字段。
   • 如果用 DEFAULT 0，你无法区分这个学生是“考了0分”还是“缺考（还没录入成绩）”。
   • 此时必须用 NULL 代表缺考，0 代表真考了0分。

总结

作为架构规范，一般建议默认把所有字段设为 NOT NULL，并给个合理的默认值。只有当你明确知道这个字段在业务上需要“空（Unknown）”的语义，或者需要利用 UNIQUE 索引允许多个空值的特性时，才去开启 NULL 允许。