第 42 章建表与数据类型踩坑

站长2026-11-06 16:00:00

内容摘要

表结构是数据库性能与正确性的地基，地基歪了，后面再怎么加索引、调 SQL 都是补窟窿。本章汇总建表与选数据类型时最容易踩的 11 个坑，覆盖金额、字符串、时间、主键、NULL、枚举、引擎、大字段与自增主键。所有示例基于 MySQL 8.0，其中金额精度部分附真实运行的 Python/sqlite3 输出。读完你能设计出"存储省、查询快、不易出错"的表结构。

📖 本篇简介：表结构是数据库性能与正确性的地基，地基歪了，后面再怎么加索引、调 SQL 都是补窟窿。本章汇总建表与选数据类型时最容易踩的 11 个坑，覆盖金额、字符串、时间、主键、NULL、枚举、引擎、大字段与自增主键。所有示例基于 MySQL 8.0，其中金额精度部分附真实运行的 Python/sqlite3 输出。读完你能设计出"存储省、查询快、不易出错"的表结构。

坑 1：金额用 FLOAT/DOUBLE 存储
坑 2：所有变长列都用 VARCHAR255
坑 3：时间字段不考虑时区
坑 4：主键用 UUID 字符串
坑 5：业务列到处允许 NULL
坑 6：状态字段滥用 INT 或 ENUM
坑 7：建表不写 ENGINE 落到 MyISAM
坑 8：TEXT/BLOB 滥用
坑 9：自增主键类型太小提前用完
坑 10：漏写 NOT NULL DEFAULT 埋下空值隐患
坑 11：DECIMAL 的精度和标量设错
坑 12：滥用 JSON 类型替代关系表
速查表：数据类型选型速记
自测：你能避坑吗

坑 1：金额用 FLOAT/DOUBLE 存储

场景 / 错误示范：

-- 订单金额用浮点类型存储
CREATE TABLE bad_order (
  id BIGINT PRIMARY KEY,
  amount DOUBLE          -- 错误：金额不该用近似浮点
);

INSERT INTO bad_order VALUES (1, 0.1), (2, 0.2);
SELECT SUM(amount) FROM bad_order;

# 用 sqlite3 复现浮点误差（MySQL 的 DOUBLE 同理都是 IEEE754 双精度）
# 实跑输出：
# 0.1 + 0.2 = 0.30000000000000004
# SUM(amount) = 0.30000000000000004
# 用 SUM 结果做等值查询命中行数 = 0   <- 本该等于 0.3 的行查不到

为什么是坑：FLOAT/DOUBLE 是近似二进制浮点，0.1 在底层根本存不准，累加和比较都会出现肉眼可见的误差。财务对账时 0.3 永远匹配不上算出来的 0.30000000000000004，出现"钱对不上"的客诉。金额一旦涉及钱，误差就是事故。

正确做法：

-- 正确：用定点数 DECIMAL，精确存储十进制小数
CREATE TABLE good_order (
  id BIGINT PRIMARY KEY,
  amount DECIMAL(12, 2)   -- 共 12 位，小数点后 2 位，足够分
);

INSERT INTO good_order VALUES (1, 0.10), (2, 0.20);
SELECT SUM(amount) FROM good_order;   -- 精确得到 0.30

# 工程上更稳的做法：直接用整数"分"存储，彻底避开浮点
# amount_cent = 10000（分） -> 展示时 /100 转成元
# 实跑验证：整数求和 sum(分) = 10000，折元 = 100.0，零误差

加分点：对账、结算类字段一律 DECIMAL(M,D)；高并发且对存储敏感的系统，用 BIGINT 存"分"是银行级常见方案。永远不要对浮点列做等值 = 比较。

坑 2：所有变长列都用 VARCHAR(255)

场景 / 错误示范：

-- 图省事，所有字符串列一律 VARCHAR(255)
CREATE TABLE user_profile (
  id BIGINT PRIMARY KEY,
  nick VARCHAR(255),       -- 昵称最多 20 字
  email VARCHAR(255),      -- 邮箱实际最长也就 80
  city VARCHAR(255),       -- 城市名最长 10 字
  remark VARCHAR(255)      -- 备注可能真的长，但也没量过
);

为什么是坑：VARCHAR 的长度在 InnoDB 里会影响索引 key 的最大长度估算和排序时内存临时表的行宽。一列定 255，联合索引、排序缓冲都会按最坏宽度预留内存，白白浪费。更重要的是它传达了"设计者没量过真实数据"的信号，评审会被挑战。长度应反映业务上界，而不是图个安全。

正确做法：

-- 按真实业务长度定义，留一点余量即可
CREATE TABLE good_user_profile (
  id BIGINT PRIMARY KEY,
  nick   VARCHAR(64),       -- 昵称 <= 20 字，留到 64
  email  VARCHAR(128),     -- 邮箱实测上界约 80
  city   VARCHAR(32),      -- 城市名 <= 10
  remark VARCHAR(512)      -- 备注确实可能长，单独放大
);

加分点：utf8mb4 下一个字符最多 4 字节，VARCHAR(255) 在索引里最坏占 1020 字节，接近 InnoDB 单列索引长度限制，这也是要控制长度的现实理由。

坑 3：时间字段不考虑时区

场景 / 错误示范：

-- 直接用 DATETIME 存"墙上时间"，跨时区部署就乱
CREATE TABLE event_log (
  id BIGINT PRIMARY KEY,
  create_time DATETIME      -- 存的是"插入那一刻本机看到的时间"
);

INSERT INTO event_log VALUES (1, '2026-10-07 12:00:00');
-- 服务器在北京，写入 12:00；用户在纽约看到的应该是哪天几点？

为什么是坑：DATETIME 存的是"不带时区的字面时间"，搬到另一个时区的服务器上，这个数字不会自动转换，跨区业务就会出现"同一条记录在两个时区差了 12 小时"。TIMESTAMP 虽然带时区转换，但 2038 年溢出且范围有限。时间字段选错，是国际化业务的定时炸弹。

正确做法：

-- 方案一：统一用 UTC 时间戳（BIGINT 存毫秒），展示层再转时区
CREATE TABLE good_event_log (
  id BIGINT PRIMARY KEY,
  create_time BIGINT       -- 存 UTC 毫秒时间戳，跨时区零歧义
);

-- 方案二：用 DATETIME 但约定一律存 UTC，应用层负责转本地时区
CREATE TABLE good_event_log2 (
  id BIGINT PRIMARY KEY,
  create_time DATETIME     -- 约定：存 UTC，不存本地时间
);

加分点：跨时区系统统一"数据库存 UTC，前端展示按用户时区格式化"，这是行业共识。TIMESTAMP 范围 1970~2038，不适合存生日、历史日期这类远期时间。

坑 4：主键用 UUID 字符串

场景 / 错误示范：

-- 为了分布式全局唯一，直接用 UUID 字符串当主键
CREATE TABLE order_info (
  id VARCHAR(36) PRIMARY KEY,   -- 错误：UUID 做主键
  amount DECIMAL(12,2)
);

为什么是坑：InnoDB 主键即聚簇索引的组织键，数据按主键顺序物理存储。UUID 是随机的，每次插入都要往 B+ 树中间随机位置插，造成页分裂、碎片、缓冲池命中率下降，写入性能比自增主键差很多，二级索引叶子还会存这份 36 字节的字符串主键，白白放大空间。随机主键还让范围查询、页相邻性都变差。

正确做法：

-- 单机：用 BIGINT 自增主键，顺序插入
CREATE TABLE good_order (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  amount DECIMAL(12,2)
);

-- 分布式：用趋势递增的 ID（如雪花算法 Snowflake），而不是随机 UUID
-- 雪花 ID 是约 64 位整数，时间高位保证大致递增，既全局唯一又顺序友好
CREATE TABLE good_order_dist (
  id BIGINT UNSIGNED PRIMARY KEY   -- 应用层生成的雪花 ID
);

加分点：如果一定要存 UUID，用 UUID_TO_BIN(uuid, 1) 转成 16 字节二进制并把时间位前置，比存 36 字符串省空间且更顺序。但首选仍是雪花/自增 BIGINT。

坑 5：业务列到处允许 NULL

场景 / 错误示范：

-- 几乎每列都允许 NULL，没有默认值
CREATE TABLE goods (
  id BIGINT PRIMARY KEY,
  title VARCHAR(100),       -- 允许 NULL
  price DECIMAL(12,2),      -- 允许 NULL
  status TINYINT            -- 允许 NULL
);

-- 后续业务里被 NULL 坑到：
SELECT COUNT(*), COUNT(price) FROM goods;
-- COUNT(price) 会把 price 为 NULL 的行排除掉，和 COUNT(*) 对不上
SELECT * FROM goods WHERE status != 1;
-- status 为 NULL 的行既不 =1 也 !=1，查出来和预期不符

为什么是坑：NULL 在 SQL 里是"未知"语义，参与任何比较结果都是 NULL（既非真也非假），导致 =、!=、IN、COUNT(列)、聚合都出现反直觉结果。InnoDB 里 NULL 还要额外的行头字节标记，索引对 NULL 的处理也更复杂。到处允许 NULL 等于给每个查询埋雷。

正确做法：

-- 业务列尽量 NOT NULL 并给明确默认值，用"约定值"代替"未知"
CREATE TABLE good_goods (
  id BIGINT PRIMARY KEY,
  title VARCHAR(100) NOT NULL DEFAULT '',       -- 空串而不是 NULL
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,   -- 0 而不是 NULL
  status TINYINT NOT NULL DEFAULT 0            -- 0=未知/默认，而不是 NULL
);

加分点：如果业务上确实区分"未设置"和"零值"，那就显式约定一个状态位，而不是依赖 NULL。聚合函数对 NULL 的坑详见第 48 章。

坑 6：状态字段滥用 INT 或 ENUM

场景 / 错误示范：

-- 错误一：状态用 INT，占 4 字节
CREATE TABLE t1 (status INT);

-- 错误二：用 ENUM，加新状态要 ALTER TABLE
CREATE TABLE t2 (
  status ENUM('pending','paid','shipped','done')
);
-- 业务要新增 'refunded'，必须 ALTER TABLE MODIFY ENUM(...)

为什么是坑：状态是有限集合，用 INT 白白占 4 字节、可读性还差（status=2 谁知道是啥）。ENUM 虽然紧凑，但它本质是把字符串和数字内部映射，加一个枚举值就要改表结构，高并发大表上做 DDL 风险大；而且排序按内部数字而非字典序，容易踩排序坑。状态集合会随业务变化，DDL 不灵活是硬伤。

正确做法：

-- 推荐：用 TINYINT（1 字节）存状态码，配套一张状态字典或在文档/常量类里约定
CREATE TABLE good_order (
  id BIGINT PRIMARY KEY,
  status TINYINT NOT NULL DEFAULT 0   -- 0待支付 1已支付 2已发货 3完成 4退款
);

-- 状态含义放在应用层常量或一张字典表，加新状态只改代码，不改表

加分点：状态码定义要有"未使用保留位"，并在字段注释里写全取值。ENUM 仅适合"几乎不变的字典"（如性别），频繁演进的业务状态别用它。

坑 7：建表不写 ENGINE 落到 MyISAM

场景 / 错误示范：

-- 老教程抄来的建表语句，没写 ENGINE
CREATE TABLE old_thing (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);
-- 在老版本/某些默认配置下，默认引擎可能是 MyISAM
mysql> SHOW TABLE STATUS LIKE 'old_thing'\G
Engine: MyISAM

为什么是坑：MyISAM 不支持事务、不支持行锁、崩溃恢复差，表级锁在并发写入时严重瓶颈。一旦表是 MyISAM，你的事务 ROLLBACK 根本不生效，主从、崩溃恢复都出问题。虽然 8.0 默认引擎已是 InnoDB，但显式写 ENGINE=InnoDB 是好习惯，避免历史环境或默认值变化埋雷。

正确做法：

-- 每张表都显式指定 InnoDB 和字符集
CREATE TABLE good_thing (
  id BIGINT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 巡检：找出库里还是 MyISAM 的表
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE
FROM information_schema.TABLES
WHERE ENGINE <> 'InnoDB' AND TABLE_SCHEMA NOT IN ('mysql','sys');

加分点：所有业务表一律 InnoDB，把"是否误用 MyISAM"做成上线前巡检项。引擎选型对比见第 8 章。

坑 8：TEXT/BLOB 滥用

场景 / 错误示范：

-- 把大段描述、图片 base64 都塞进表里
CREATE TABLE article (
  id BIGINT PRIMARY KEY,
  title VARCHAR(100),
  content TEXT,             -- 文章正文
  cover_base64 MEDIUMTEXT   -- 封面图直接 base64 存文本
);

为什么是坑：InnoDB 对变长大列有"行外存储"机制，但一旦表里有 TEXT/BLOB，排序、临时表、SELECT * 时都会把这些大列带上，导致内存临时表暴涨、磁盘临时表增多、网络传输变大。把图片 base64 塞进数据库更是灾难——图片不该进库，该进对象存储，库里只存 URL。

正确做法：

-- 正文这类确实大的内容，拆到扩展表，主表保持窄
CREATE TABLE article_main (
  id BIGINT PRIMARY KEY,
  title VARCHAR(100),
  author_id BIGINT,
  create_time BIGINT
) ENGINE=InnoDB;

CREATE TABLE article_content (
  article_id BIGINT PRIMARY KEY,
  content MEDIUMTEXT        -- 正文单独存，列表查询不碰它
) ENGINE=InnoDB;

-- 图片：只存 URL，文件本身放对象存储/CDN
ALTER TABLE article_main ADD cover_url VARCHAR(512);

加分点：遵循"窄表高频、宽表低频"拆分原则。SELECT * 在有 TEXT 的表上尤其要避免，只取需要的列。

坑 9：自增主键类型太小提前用完

场景 / 错误示范：

-- 用 INT 自增主键，还嫌 BIGINT 占空间
CREATE TABLE flow_log (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
);
-- 跑了两年，插入报：ERROR 1062 Duplicate entry '4294967295' for key
-- INT UNSIGNED 上限约 42.9 亿，写日志的表早就顶满了

为什么是坑：INT UNSIGNED 上限约 42.9 亿，对流水表、日志表、消息表来说一两年就写满。主键一旦到顶，新插入直接报主键冲突，业务停摆。事后要把主键从 INT 改成 BIGINT 是一次超大表 DDL，代价极高。这是典型的"为了省 4 字节，最后赔上一次大迁移"。

正确做法：

-- 主键一律直接上 BIGINT UNSIGNED（8 字节），上限 1.8e19，几乎用不完
CREATE TABLE good_flow_log (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
);

-- 巡检：查各表自增列当前用到哪了，离上限多远
SELECT TABLE_NAME, AUTO_INCREMENT
FROM information_schema.TABLES
WHERE AUTO_INCREMENT IS NOT NULL;

加分点：自增主键就用 BIGINT，别为几字节纠结；真正会被高频写入的日志表，更要一次性给足。INT 留着给那种真的小且不会涨的字典表用。

坑 10：漏写 NOT NULL DEFAULT 埋下空值隐患

场景 / 错误示范：

-- 建表时只给了部分列默认值，其余靠应用层自觉
CREATE TABLE comment (
  id BIGINT PRIMARY KEY,
  content VARCHAR(500),
  like_count INT,              -- 没默认值
  create_time DATETIME        -- 没默认值
);

INSERT INTO comment (id, content) VALUES (1, '不错');
-- like_count、create_time 全是 NULL，前端展示崩了

为什么是坑：数据库应该是数据的最后一道防线，而不是"信应用层永远传对值"。漏了默认值，一旦应用某个分支忘了给值，就写进 NULL，后续统计、展示全乱。时间字段尤其常见——没默认 CURRENT_TIMESTAMP，就得靠代码每处都记得塞时间，漏一处就出现 0000-00-00 或 NULL。

正确做法：

CREATE TABLE good_comment (
  id BIGINT PRIMARY KEY,
  content VARCHAR(500) NOT NULL,
  like_count INT NOT NULL DEFAULT 0,                 -- 默认 0
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 默认当前时间
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP            -- 更新时自动刷新
);

加分点：创建时间、更新时间这两个"标配列"几乎每张业务表都该有，且用 DEFAULT CURRENT_TIMESTAMP + ON UPDATE CURRENT_TIMESTAMP 让数据库自己维护，别依赖应用层。

坑 11：DECIMAL 的精度和标量设错

场景 / 错误示范：

-- DECIMAL(M,D) 理解错：M 是总位数，D 是小数位数
CREATE TABLE wallet (
  id BIGINT PRIMARY KEY,
  balance DECIMAL(5, 2)   -- 总位数 5，小数 2 位 -> 整数部分只有 3 位 = 最大 999.99
);

INSERT INTO wallet VALUES (1, 12345.67);
-- 报错或被截断！余额最多存到 999.99

为什么是坑：很多人以为 DECIMAL(5,2) 是"5 位整数加 2 位小数"，其实是总共 5 位、其中 2 位小数，整数部分只剩 3 位。按错误理解建表，余额超过 999.99 就写不进去或被静默截断，资金类字段出这种问题就是资损。

正确做法：

-- 想存"最多千亿、精确到分"：整数 13 位 + 小数 2 位 = DECIMAL(15,2)
CREATE TABLE good_wallet (
  id BIGINT PRIMARY KEY,
  balance DECIMAL(15, 2) NOT NULL DEFAULT 0.00
);

INSERT INTO good_wallet VALUES (1, 12345.67);   -- 正常存入

加分点：建资金字段前先估算业务最大值（一年流水多少、单笔最大多少），留足整数位。DECIMAL(M,D) 的 M 是总位数不是整数位数，这个区别面试常考。

坑 12：滥用 JSON 类型替代关系表

场景 / 错误示范：

-- 图灵活，把一堆关系字段全塞进一个 JSON 列
CREATE TABLE product (
  id BIGINT PRIMARY KEY,
  attrs JSON          -- 颜色、尺寸、标签、规格全塞这里
);

INSERT INTO product VALUES (1,
  '{"color":"红","size":"L","tags":["潮","新"],"price":99.9}');

为什么是坑：JSON 列虽然灵活，但它无法被当成普通关系列高效 JOIN，也难以对外键、唯一约束做强制。频繁按 JSON 内部字段查询时，必须写 JSON_EXTRACT/->>，不仅写起来啰嗦，还容易用不上普通索引，只能靠生成列+索引补救。把本该拆成多行子表的一对多关系（如商品多规格）塞进一个 JSON 数组，后续统计"有多少商品是红色"要在应用层解析数组，性能和正确性都差。

正确做法：

-- 高频查询、需要约束和关联的字段，拆成正经关系列或子表
CREATE TABLE good_product (
  id BIGINT PRIMARY KEY,
  color VARCHAR(16),          -- 常查常筛 -> 独立列
  size  VARCHAR(8),
  price DECIMAL(10,2)
) ENGINE=InnoDB;

-- 一对多标签：单独建关联表，便于 JOIN 和统计
CREATE TABLE product_tag (
  product_id BIGINT,
  tag VARCHAR(32),
  PRIMARY KEY (product_id, tag)
) ENGINE=InnoDB;

-- 真正"偶发读取、结构多变"的扩展属性才用 JSON，且别在里面做高频过滤
ALTER TABLE good_product ADD ext JSON;

加分点：JSON 适合当"半结构化扩展字段"，不适合当"主存储模型"。要按 JSON 字段查询时，用"生成列 + 索引"（ALTER TABLE ... ADD COLUMN ... GENERATED ALWAYS AS (json->>'$.x') VIRTUAL, ADD INDEX），而不是每次全表 JSON_EXTRACT。

速查表：数据类型选型速记

|---|---|---|---|

| 时间列 | 无默认值 | DEFAULT CURRENT_TIMESTAMP | 防漏传 |

自测：你能避坑吗

题 1：为什么 0.1 + 0.2 在浮点里不等于 0.3？金额字段正确做法是什么？

IEEE754 双精度无法精确表示 0.1，累加后得到 0.30000000000000004（实跑已验证）。金额字段应使用 DECIMAL(M,D) 定点数，或直接用 BIGINT 存"分"，并避免对浮点列做等值比较。

</details>

题 2：DECIMAL(8,2) 能存的最大金额是多少？

M=8 是总位数，D=2 是小数位数，所以整数部分 6 位，最大 999999.99。若业务余额可能上百万，就需要更大的 M。

</details>

题 3：为什么 UUID 字符串不适合做 InnoDB 主键？

InnoDB 主键即聚簇索引组织键，数据按主键物理有序存储。随机 UUID 导致每次插入都落在 B+ 树随机位置，引发页分裂和碎片，写入性能差；36 字节字符串还会被所有二级索引叶子携带，放大空间。应改用自增 BIGINT 或趋势递增的雪花 ID。

</details>

题 4：状态字段为什么推荐 TINYINT 而不是 ENUM？

ENUM 加新枚举值需要 ALTER TABLE 改表结构，大表上 DDL 风险高；且排序按内部数字而非字典序，易出错。TINYINT 用 1 字节存状态码，状态集合放应用层常量/字典表，加状态只改代码不改表。

</details>

下一篇预告：第 43 章字符集与乱码踩坑

— 全文完 —回到顶部 ↑

下载推广海报

DISCUSSION

文章回复

0 条公开回复

还没有回复，欢迎参与讨论。

第 42 章建表与数据类型踩坑

目录

坑 1：金额用 FLOAT/DOUBLE 存储

坑 2：所有变长列都用 VARCHAR(255)

坑 3：时间字段不考虑时区

坑 4：主键用 UUID 字符串

坑 5：业务列到处允许 NULL

坑 6：状态字段滥用 INT 或 ENUM

坑 7：建表不写 ENGINE 落到 MyISAM

坑 8：TEXT/BLOB 滥用

坑 9：自增主键类型太小提前用完

坑 10：漏写 NOT NULL DEFAULT 埋下空值隐患

坑 11：DECIMAL 的精度和标量设错

坑 12：滥用 JSON 类型替代关系表

速查表：数据类型选型速记

自测：你能避坑吗

文章推广海报

文章回复

第 42 章 建表与数据类型踩坑

目录

坑 1：金额用 FLOAT/DOUBLE 存储

坑 2：所有变长列都用 VARCHAR(255)

坑 3：时间字段不考虑时区

坑 4：主键用 UUID 字符串

坑 5：业务列到处允许 NULL

坑 6：状态字段滥用 INT 或 ENUM

坑 7：建表不写 ENGINE 落到 MyISAM

坑 8：TEXT/BLOB 滥用

坑 9：自增主键类型太小提前用完

坑 10：漏写 NOT NULL DEFAULT 埋下空值隐患

坑 11：DECIMAL 的精度和标量设错

坑 12：滥用 JSON 类型替代关系表

速查表：数据类型选型速记

自测：你能避坑吗

第 42 章建表与数据类型踩坑