为什么数据库字段默认值不建议用NULL？索引失效与慢查询的元凶

年前帮一家电商客户做数据库巡检遇到一个特别典型的慢查询订单表明明建了索引几千万行却走了全表扫描接口响应从 80ms 一下子涨到 3.8 秒。EXPLAIN 拉出来一看问题出在一个后来新增的字段上——建表脚本里写着DEFAULT NULL存量数据里这个字段八成以上都是 NULL。更巧的是同一批上线的还有另外两个字段也全部默认 NULL客户那边列表页有些订单查不出来财务统计对不上的工单顺着查下去全是这个不起眼的默认值在捣鬼。今天这篇就想认真聊一聊为什么我不建议给数据库字段加默认值 NULL。这不是洁癖是这几年在生产环境实打实踩坑踩出来的结论。全文会从 NULL 的 SQL 语义讲起再拆索引、统计、代码三层的真实影响最后给出一套可以直接上手的字段整改方案。正在设计表结构、维护存量库、或者写 SQL 总被 NULL 坑到的同学这篇值得看完。1. DEFAULT NULL 到底是什么先看清 SQL 的三值逻辑1.1 NULL 不是空而是未知很多人对 NULL 的理解停留在空值——觉得 NULL 就是没值跟空字符串 、数字 0 差不多。这是最大的认知误区。在 SQL 标准里NULL 表达的是未知unknown它不是一个具体的值而是一种状态。SQL 是比较逻辑是三值逻辑除了 TRUE、FALSE还有一个 UNKNOWN。任何普通值与 NULL 做比较结果都是 UNKNOWNUNKNOWN 在 WHERE 条件里会被当作不成立处理。这个特性会衍生出很多匪夷所思的行为-- 下面这条 SQL 永远查不到数据因为没有任何值会等于 NULL SELECT * FROM order_info WHERE payer_name NULL; -- 应该写成这样 SELECT * FROM order_info WHERE payer_name IS NULL; -- 表达式里掺入 NULL结果也是 NULL SELECT 1 NULL; -- NULL SELECT CONCAT(订单, NULL); -- NULLMySQL 的 CONCAT 遇到 NULL 返回 NULL我见过不少新手在代码里写WHERE column NULL执行完发现一条数据都没有第一反应是表是不是空的而不是去怀疑 NULL 的语义。这种问题查起来非常浪费时间因为 SQL 不报错只给你一个空结果。1.2 DEFAULT NULL 和不写默认值是两码事有的朋友会说那我建表时不写 DEFAULT字段不就默认是 NULL 了吗没错在 MySQL 里如果一个列允许为空没有 NOT NULL 约束不写 DEFAULT 时隐含的默认值就是 NULL。但DEFAULT NULL 是显式声明默认就为空两者在行为上结果一样在意图上完全不同。显式写DEFAULT NULL相当于告诉后来维护这张表的人我允许这个字段为空而且默认就是空。很多同学建表时习惯性给每个字段都补一个DEFAULT NULL甚至不管什么字段都来这么一句。这种脚本一旦流入生产哪天字段真出问题了排查的时候你很难分清这是设计时故意允许为空还是顺手写错了。更危险的是如果列已经带上了 NOT NULL 约束再写DEFAULT NULL就是自相矛盾。新版数据库如 MySQL 8.0会直接报错Invalid default value就算某些老版本在非严格模式下放过了后面也会埋下同步、迁移时的兼容性隐患。2. 默认值 NULL 的真实危害索引、统计、代码三线崩盘2.1 索引层面NULL 会让优化器绕开索引先说结论**允许 NULL 的字段索引效果通常比 NOT NULL 字段差而且组合索引更容易失效。**InnoDB 的二级索引不是完全不存 NULL它是会记录的但优化器在评估查询计划时会考虑 NULL 值对范围扫描、排序、去重的影响结果往往选择全表扫描。举一个我实际排查过的案例。用户表加了mobile字段的索引业务查询主要是按手机号查账号。因为历史原因mobile 默认 NULL存量数据里大约 20% 的行是 NULL。EXPLAIN 的结果显示typeALL优化器认为走这个索引需要回表、再过滤掉 NULL 行代价不如全表扫。后来把 mobile 统一改为NOT NULL DEFAULT 并刷新统计信息同样的 SQL 走了索引查询时间从 1.2 秒降到 30 毫秒左右。排序也会被影响。MySQL 里升序排序时 NULL 默认排在最前面Oracle 里默认 NULL 排在最后SQL Server 又是另一种行为。同样一条 SQL在不同数据库上跑出来的列表顺序完全不一样如果前端做分页很容易出现数据显示不完整、翻页串数据的诡异问题。2.2 统计与聚合COUNT、SUM、GROUP BY 全被 NULL 带偏NULL 的第二个重灾区是统计口径。直接上示例CREATE TABLE user_action ( id INT PRIMARY KEY, user_id INT NOT NULL, action_type VARCHAR(20) DEFAULT NULL, -- 有些行没有动作类型 score INT DEFAULT NULL -- 有些行没有分数 ); -- COUNT(*) 统计行数 SELECT COUNT(*) FROM user_action; -- 1000 -- COUNT(action_type) 只统计非 NULL 的行数 SELECT COUNT(action_type) FROM user_action; -- 850少了150行 -- SUM(score) 忽略 NULL但如果全是 NULL结果是 NULL 而不是 0 SELECT SUM(score) FROM user_action WHERE score IS NULL; -- NULL -- AVG(score) 的分母不包含 NULL 行导致平均值虚高 SELECT AVG(score) FROM user_action; -- 只算有分数的行对于报表开发来说这个特性是致命的。很多 BI 工程师直接用SUM(amount)统计金额结果某个月字段默认 NULL 的行特别多汇总数据悄悄少了一截等到财务对账发现不平已经在错误数据的基础上跑了很久。**更稳妥的做法是统计时明确用IFNULL(column, 0)或COALESCE(column, 0)兜底并且建表时就把字段默认值定为 0。**统计模块一旦被 NULL 坑过你才会理解默认值 0 和默认值 NULL之间的差别有多大。GROUP BY 同样会出问题所有 NULL 会被分到同一组这一组在报表里的展示名通常是空字符串业务方根本看不懂。2.3 应用层Java 的 NPE 与 ORM 的更新失效NULL 的破坏力不止在数据库内部它会顺着数据访问层一路炸到业务代码。最常见的就是 Java 开发里的 NullPointerException// 订单金额字段如果是 NULL下面这段代码直接抛 NPE BigDecimal amount order.getAmount(); BigDecimal tax amount.multiply(new BigDecimal(0.06));字符串拼接更隐蔽订单号: order.getOrderNo()遇到 NULL 会变成订单号:null前端展示的时候莫名其妙多出null字样用户看到还会以为系统出了 bug。ORM 框架的更新丢失问题同样值得警惕。以 MyBatis-Plus 为例默认的字段更新策略是NOT_NULL也就是实体里某个属性为 null 时生成 UPDATE 语句时会自动忽略这个字段。这个设计的初衷是避免误覆盖但副作用是当你真的想把这个字段清空时UPDATE 语句根本不会包含该列。想让数据库字段从有值改成 NULL得额外加注解或者写自定义 SQL。生产环境里我接过不少这种工单我明明把字段置空了保存后再查还是有值。十有八九是默认值 NULL ORM 更新策略的双重问题。顺带说一个 ERP 领域的例子。像 ACDOCA 这种核心财务表二次开发加自定义字段时顾问为了避免报错经常把增强字段定义为可空。结果月末报表一跑空值记录参与汇总财务怎么对都对不上最后还得靠IFNULL一层层补丁。扩展字段尽量给明确的默认值空字符串、0、既定枚举而不是放任 NULL这条经验在 SAP 物料主数据扩展比如 BAPI_MATERIAL_SAVEDATA 传扩展字段里同样适用——你传一个 null 进去下游逻辑根本不知道是没传还是传了个空。3. 表结构怎么改一套可直接抄作业的整改流程3.1 建表阶段默认值就应该有明确的含义先看反例和正例的对照-- 反例字段默认值全是 NULL CREATE TABLE user_profile ( id BIGINT PRIMARY KEY, nickname VARCHAR(32) DEFAULT NULL, age INT DEFAULT NULL, avatar_url VARCHAR(255) DEFAULT NULL, bio VARCHAR(500) DEFAULT NULL, status TINYINT DEFAULT NULL ); -- 正例明确默认值能用 NOT NULL 就用 NOT NULL CREATE TABLE user_profile ( id BIGINT PRIMARY KEY, nickname VARCHAR(32) NOT NULL DEFAULT , age INT NOT NULL DEFAULT 0, avatar_url VARCHAR(255) NOT NULL DEFAULT , bio VARCHAR(500) NOT NULL DEFAULT , status TINYINT NOT NULL DEFAULT 1 );设计原则我总结成三句话字符串字段用NOT NULL DEFAULT 。空字符串表达没有内容但它参与字符串拼接、比较、索引时都比 NULL 友好得多。数值字段用NOT NULL DEFAULT 0。但要确认业务上 0 到底有没有特殊含义。比如订单金额默认 0 没问题但年龄默认 0 就有点假这种字段如果业务上确实可能未知可以考虑其他哨兵值如 -1做兜底或者允许 NULL 但应用层做好防御。时间字段区分业务时间和记录时间。记录时间可以直接用DEFAULT CURRENT_TIMESTAMP业务时间如审核时间没产生之前就是没有这种我建议允许 NULL但查询时一律用IS NULL/IS NOT NULL不要用等值比较。3.2 存量表三步完成 NULL 字段的清理和改造老项目不可能推倒重来存量表的改造才是重头戏。我一般按三步走第一步先摸清家底找出所有允许为 NULL 的字段-- MySQL查出一个库里所有可空字段 SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_db_name AND IS_NULLABLE YES ORDER BY TABLE_NAME, ORDINAL_POSITION;第二步逐个字段做业务评估。打开这张清单和业务方逐个确认这个字段有没有空的实际业务含义哪些行真的会是空如果只是默认没值那就该改成 NOT NULL 默认值。第三步谨慎执行变更。核心点是先更新数据、再改表结构顺序不能乱-- 1. 先把存量 NULL 替换为默认值大表务必分批别直接 UPDATE 全表 UPDATE user_profile SET nickname WHERE nickname IS NULL LIMIT 5000; -- 循环执行 UPDATE user_profile SET age 0 WHERE age IS NULL LIMIT 5000; UPDATE user_profile SET bio WHERE bio IS NULL LIMIT 5000; -- 2. 修改列定义 ALTER TABLE user_profile MODIFY COLUMN nickname VARCHAR(32) NOT NULL DEFAULT , MODIFY COLUMN age INT NOT NULL DEFAULT 0, MODIFY COLUMN bio VARCHAR(500) NOT NULL DEFAULT ;这里必须提醒一句**大表 ALTER TABLE 会造成长时间元数据锁生产环境不能直接跑。**更稳妥的做法是借助在线 DDL 工具如 gh-ost、pt-online-schema-change或者把流程改成新建一张新表 → 双写 → 灰度切换。我自己在线上用 gh-ost 改过一张 5000 万行的表几乎无感比在原表上直接 MODIFY 安全太多。3.3 什么时候允许 NULL 不是坏事强调一下**我不是说所有字段都不能有 NULL。**数据库字段该不该允许 NULL要看这个空是明确没有还是暂时未知、未来可能有。适合保留 NULL 的场景可选外键比如积分明细里的订单号用户还没下单时确实没有关联对象这时用 NULL 表示暂未关联比编一个假的订单号靠谱。低频业务属性比如员工表的离职日期在职员工这个字段天然为空强行 NOT NULL 反而要伪造一个 2099-12-31查询全都乱了。扩展预留字段业务上明确大部分行都不会填内容的字段NULL 比空字符串更能表达未提供。这些场景保留 NULL 没问题但配套纪律必须跟上Java 代码里所有读取路径都要做空值兜底SQL 里一律用IS NULL/IS NOT NULLORM 实体字段用包装类型Integer而不是int避免 NPE。4. 常见问题速查与我的避坑清单4.1 高频问题排查速查表典型现象根因解决办法WHERE col NULL查不到任何数据误用等值比较 NULL三值逻辑导致 UNKNOWN改为IS NULL/IS NOT NULL明明建了索引SQL 还是全表扫描列允许 NULL优化器放弃索引改成 NOT NULL 合理默认值必要时刷新统计信息COUNT(字段)和COUNT(*)对不上COUNT(字段) 不统计 NULL 行明确口径不需要行数统计时统一用 COUNT(*)SUM(amount)汇总结果偏小或返回 NULLSUM 忽略 NULL全为 NULL 时返回 NULL用COALESCE(SUM(amount), 0)兜底Java 后端接口突然报 NullPointerException数据库查到 NULL 映射到包装类型运算直接炸建表避免 NULL 默认值 代码层统一判空MyBatis-Plus 把字段置空后保存无效默认更新策略忽略 NULL 字段改用 LambdaUpdateWrapper 显式 set 字段为 null列表分页顺序不稳定不同数据库对 NULL 排序规则不一致排序字段设计为 NOT NULL排序条件加NULLS LAST/FIRST唯一索引保护邮箱不重复失效多个 NULL 行不参与唯一约束比较业务上需要空也唯一时将空字符串替代 NULL这张表是我日常排查问题前必看的清单很多看着毫无头绪的数据库诡异问题最终都能收敛到某个字段是 NULL这个根因上。4.2 踩坑多年总结的避坑清单**第一建表脚本先过审DEFAULT NULL 亮红灯。**我现在的习惯是所有新建表的字段默认都写成NOT NULL只有明确需要未知语义的列才放开。代码评审阶段看到DEFAULT NULL会直接被问一句这个字段真的需要默认空吗不能填默认值吗问完这一句至少能挡掉一半隐患。**第二改造存量字段时先小步灰度再全量。**不要一边跑业务一边直接 ALTER 大表也不要一开始就 update 所有历史数据。我在生产环境惯用的流程是先写 SQL 查出 NULL 比例评估影响面然后用影子表验证修改后的读写逻辑最后通过在线 DDL 工具逐步切换。整个过程要能随时回滚。**第三不同数据库的 NULL 行为差异要心里有数。**MySQL、PostgreSQL、达梦、GBase 这些数据库对 NULL 的排序规则、唯一索引行为、统计函数处理都有细节差异。同一个表结构要同时兼容多种数据库时比如做数据库同步工具的项目DEFAULT NULL 带来的坑会被放大好几倍。设计阶段就统一口径后面会省下大把排查时间。**第四给 ORM 加一道空值防火墙。**如果团队代码里已经有大量依赖 NULL 字段的老逻辑改动表结构之前先在应用层加统一的字段映射处理查询结果里的 NULL 统一转成空字符串或 0再往上传。这一步能避免数据库改了、应用炸了的尴尬。最后再分享一个个人习惯我每次接到慢查询或者数据对不上的工单第一步不是看 SQL 本身而是先SHOW CREATE TABLE扫一眼所有字段的默认值。只要看到一片 DEFAULT NULL心里基本就有数了。这些年处理的数据事故里相当大一部分能追到字段默认值设计不规范上。希望这篇能帮你少踩几个坑。