建表时那几个默认值:NULL 与空串、时间列陷阱、隐式转换与一次线上排序错乱的复盘

一、一个字段默认值引发的连锁反应

建表的时候,很多人只关心字段名和类型,「默认值」那一栏能空就空。等到站点跑了半年,突然发现列表页上出现了大量 1970 年 1 月 1 日的文章、某些评论的作者名是空的、排序结果和预期完全不一样,才会回头去翻建表语句。默认值这块看起来只是填几个字,实际决定了所有没有显式赋值的写入会发生什么,而这类写入在真实系统里占的比例远比想象中高:程序漏传字段、旧版本客户端、批量导入、手工修数据,都会走到默认值这一条路上。

下面按「最容易出事」的顺序,把字段定义里那几个常被跳过的部分讲清楚:默认值、隐式转换、时间列、字符串长度与索引、枚举与整数、以及字符集。每一条都配一个真实会踩到的场景,而不是教科书式的语法说明。

二、NULL 与空串:两个「没值」不是一回事

在很多业务代码里,NULL 和空字符串 '' 被当作同一件事处理,但在数据库里它们完全不同。最直接的后果是三值逻辑:WHERE nickname != '管理员' 不会匹配到 nickname 为 NULL 的行,因为 NULL 参与任何比较都得到 NULL 而不是 true。很多人写「排除管理员」的统计,数字总是偏少,就是这一条。

第二个后果是唯一索引。MySQL 的普通唯一索引允许多个 NULL,但只允许一个空字符串。如果你的业务是「手机号可为空,但填了就不能重复」,字段定义成 varchar(20) DEFAULT NULL UNIQUE 是合适的;如果定义成 NOT NULL DEFAULT '',那么第二个没填手机号的用户就会因为重复空串被拒绝插入,报错信息还看不出原因。

第三个后果是聚合。对含 NULL 的列做 COUNT(col) 会跳过 NULL,而 COUNT(*) 不会。平均值也是同样,AVG 忽略 NULL,所以「平均分」会把没打分的人当作不存在,而不是当作 0 分。这条在报表里非常容易出错,而且错得悄无声息。

落地建议只有一句:先想清楚「没值」在业务上是一个状态还是零值。用户没填昵称和用户把昵称删空了,通常是两件事,那就该用 NULL 表示前者、空串表示后者;如果两者在业务上等价,就统一成一种,别留两套。

三、时间列的三个默认值陷阱

时间字段是默认值问题最集中的地方,MySQL 在这方面有几处历史包袱。

零日期与严格模式

老版本 MySQL 在非严格模式下会把非法日期写成 0000-00-00 00:00:00,而很多前端的时间格式化函数遇到这个值会显示成 1970 年或者直接抛异常。现代版本默认开启 STRICT_TRANS_TABLES,非法日期会直接报错而不是静默写入零值,但如果你从旧库迁移过来,历史数据里依然有这些零日期。迁移前的自查一定要跑:

SELECT COUNT(*) FROM 表 WHERE 时间列 = '0000-00-00 00:00:00';
SELECT COUNT(*) FROM 表 WHERE 时间列 IS NULL;

两个数字都要看。前者要清洗成 NULL 或合理的值,后者要确认程序能不能处理 NULL。

ON UPDATE CURRENT_TIMESTAMP 的连带效应

updated_at 加上 ON UPDATE CURRENT_TIMESTAMP 很方便,但它的触发条件是「这一行的任意列值发生了变化」。也就是说,你做一次只改统计字段的 UPDATE,业务上没改内容,updated_at 照样被刷新。如果这个字段被用来做增量同步(比如 WHERE updated_at > 上次时间),那么每次刷统计都会把整批数据再同步一遍,增量变成全量。更糟的是触发器与默认值的时间精度问题:如果列定义是 DATETIME(不带小数秒),同一秒内多次更新会被合并成一个时间点,增量同步可能漏数据。做同步游标的列,定义成 DATETIME(3) 或 TIMESTAMP(3) 更稳妥。

时区

TIMESTAMP 存的是 UTC,读的时候按会话的 time_zone 转换;DATETIME 存什么读什么,不做转换。这对组合最容易出问题的场景是:应用服务器和数据库服务器时区不同,用 TIMESTAMP 时看起来「自动处理了」,实际取决于连接时的会话时区设置;用 DATETIME 时应用写进去什么就是什么,如果应用写的是本地时间而某天换了服务器时区,历史数据的语义就变了。稳妥做法是:全部用 DATETIME 存 UTC,并在应用层统一转换,或者全部用 TIMESTAMP 并确保会话时区一致。最忌讳的是混用,一半表用 TIMESTAMP 一半用 DATETIME,两年后没人说得清哪个字段是什么时区。

四、隐式类型转换:索引失效的隐形杀手

字段是 varchar,查询条件写成 WHERE phone = 13800000000(没有引号),MySQL 会把列值转成数字再比较。这个转换是逐行进行的,索引直接失效,一张百万行的表会退化成全表扫描。更阴险的是结果可能还是对的,所以测试环境发现不了,上线后慢查询才冒出来。

反向的坑同样存在:字段是 int,查询写成 WHERE id = '123',字符串会被转成数字,索引仍然有效,但如果值里有前导零或非数字字符,行为会变得难以预测。判断方法很简单,对可疑查询跑一次 EXPLAIN,看 type 是不是 ALL、key 是不是 NULL。养成习惯:写 SQL 前先看一眼字段类型,字符串就加引号,数字就不加。

还有一种隐式转换来自字符集不一致。两表 JOIN 时,如果一边是 utf8mb4、一边是 utf8mb3,MySQL 需要转换才能比较,索引同样可能失效。这也是为什么「建表时把字符集统一」不只是美观问题,而是性能问题。

五、字符串长度:VARCHAR 的括号不是越小越省

VARCHAR(10) 和 VARCHAR(255) 在存储上并不按括号里的数字预分配空间,只按实际长度存,额外 1~2 字节记录长度。所以把长度设小不会让表变小,唯一的实质影响是能不能建索引、以及能不能存下数据。

真正的约束在索引上。InnoDB 的索引键长度有上限(常见版本是 3072 字节,配合 DYNAMIC 行格式与 innodb_large_prefix),而 utf8mb4 每个字符最多占 4 字节。也就是说,一个 VARCHAR(768) 的 utf8mb4 列刚好能建满整个索引,VARCHAR(1000) 单独建索引就会报「索引键太长」。解决办法是只对前缀建索引(KEY title_prefix (title(191))),或者改用 utf8mb4_0900_ai_ci 这类能压缩存储的排序规则(新版本才有)。

另一个和长度相关的判断是「该不该拆表」。评论内容、文章正文这类大文本,放进主表会让主表的行变宽,每次扫描主表都要跳过这些溢出页,列表页查询会明显变慢。合理做法是把大字段拆到副表,主表只留标题、摘要、时间、状态这些列表页要用的列。

六、枚举、布尔与状态位:三种「是/否」的取舍

状态类字段最常见的三种写法:ENUM、TINYINT(1)、CHAR(1),各有各的坑。

ENUM 存的是整数下标,改动枚举值列表需要 ALTER TABLE,而新增枚举值如果插在中间,会打乱已有数据的语义。更麻烦的是很多 ORM 对 ENUM 的支持不好,读出来是字符串、写进去可能报类型错误。除非枚举值极其稳定(比如性别),一般不建议用。

TINYINT(1) 常被当作布尔用,但括号里的 1 只是显示宽度,在 8.0.17 之后连显示宽度都不推荐了,它和 TINYINT 在存储上没有任何区别。写成 TINYINT(1) NOT NULL DEFAULT 0 就够用,别指望它有额外约束。真正需要约束的话,在应用层做,或者用 CHECK 约束(8.0.16 之后才真正生效,更早版本会解析但忽略,这一点必须注意)。

状态位的实际问题是取值语义没有文档。1 = 正常、2 = 隐藏、3 = 审核中,这种约定如果只存在于代码里,两年后重写系统时会成为灾难。稳妥做法是把状态值写进表注释或旁边的说明表,并且在库里加一条 CHECK (status IN (1,2,3)),让非法值在写入时就失败。

七、一次线上排序错乱的复盘

说一个真实场景把上面的点串起来。一个博客站的首页按「置顶 + 发布时间」排序,某天开始顺序全乱了,新文章跑到最后,老文章排在最前。程序逻辑没改过,第一步先把程序生成的 SQL 打出来,发现是:

SELECT id, title, created_at FROM posts
WHERE status = 1
ORDER BY top DESC, created_at DESC
LIMIT 20;

SQL 本身没错。接着看执行计划,type=ALL,走的是文件排序(Using filesort),对 3000 行的表来说慢一点但不至于乱序——除非 created_at 这一列的值本身有问题。抽查十行:

SELECT id, top, created_at FROM posts ORDER BY id DESC LIMIT 10;

发现新发布的文章 created_at 全是 NULL。原因是程序在插入时没有传这个字段,而建表时写的是 created_at DATETIME DEFAULT NULL——一个空默认值。排序时 NULL 在 MySQL 里被视为最小值,DESC 排序就全跑到最后面了。前三篇刚发的文章恰好是最新的,用户一眼就看出异常。

修复分四步:第一,把列定义改成 NOT NULL DEFAULT CURRENT_TIMESTAMP,让漏传字段的写入自动拿到当前时间;第二,把已有的 NULL 行按 id 顺序补一个合理时间(或直接用 updated_at 兜底);第三,在程序里把插入语句补全,不要依赖默认值;第四,给这张表按 (status, top, created_at) 建一个联合索引,顺手把 filesort 也消掉。改完再看执行计划,type=ref、key=idx_status_top_created,首页从 40ms 降到 3ms。

这个案例的教训不是「记得给时间字段加默认值」,而是默认值是数据质量的最后一道防线,一旦它允许 NULL 或者零值,那么所有漏传字段的地方都会安静地写入一个错误的值,而错误不会报错,只会以结果不对的形式出现在页面最显眼的地方。建表时多花五分钟想清楚每个字段「不给值时会怎样」,比事后补数据划算得多。

八、建表前的一张自检清单

  • 这个字段在业务上真的允许「没有值」吗?允许就用 NULL,不允许就 NOT NULL 加默认值。
  • 时间列存的是 UTC 还是本地时间?全库统一,别混用 TIMESTAMP 与 DATETIME。
  • 做增量同步的游标列有没有小数秒?没有的话同一秒内多次更新会漏。
  • 数字字段会不会被写成字符串比较,字符串字段会不会被写成数字比较?两者都会让索引失效。
  • 要建索引的 varchar 列长度乘 4 会不会超出索引键上限?会的话改用前缀索引。
  • 状态字段的取值范围是否写进了注释或 CHECK 约束?没有的话补上。
  • 表的字符集和排序规则是否与关联表一致?不一致会让 JOIN 变慢。
  • 主键用自增还是 UUID?自增写入更友好,UUID 要处理页分裂,选之前想清楚。

这八条里,最先出事的一般是第二条和第五条,因为它们不影响功能,只在数据量上来以后或者跨系统对接时才暴露。把它们当成建表模板的一部分固定下来,能省掉很多事后修数据的麻烦。

Last modification:November 21st, 2026 at 09:25 pm

Leave a Comment