03｜数据类型与约束

上一篇建了一张 servers 表，字段类型是照着写的。但为什么 id 用 BIGINT 不用 INT？为什么 IP 用 VARCHAR(45) 不用 VARCHAR(255)？金额为什么不能存 FLOAT？

这些选择不是随便定的。字段类型定下来后，存储空间、索引效果、查询行为都跟着定了。改字段类型在线上是重操作（大表要拷贝数据），建表时尽量一次选对。本篇讲常见类型怎么选、约束怎么用。

一、整数类型

存 ID、计数、状态码这类整数，按值的范围选：

类型	范围	适合
TINYINT	-128~127（或 0~255 UNSIGNED）	状态码、布尔标志
INT	约 ±21 亿	一般计数、中等表的 ID
BIGINT	约 ±9.2×10¹⁸	大表 ID、自增主键

一个常见做法：主键用 BIGINT UNSIGNED AUTO_INCREMENT。UNSIGNED 让值翻倍（只用正数，ID 本来就不要负数），自增主键增长到很大的表用 INT 可能不够。

别图省事全用 BIGINT——存个状态码用 BIGINT 浪费空间，索引也更大更慢。按实际范围选最小的够用的类型。

二、小数：金额不能用 FLOAT

存金额、需要精确的小数，用 DECIMAL，不要用 FLOAT/DOUBLE：

price DECIMAL(10, 2)     -- 总共 10 位，小数 2 位

为什么？FLOAT 是浮点数，有精度误差。0.1 + 0.2 在浮点里可能等于 0.30000000000000004。算账时差一分钱都是问题。DECIMAL 是定点数，存什么就是什么，没有误差。

金额场景 DECIMAL(10,2) 够大部分情况（最大 99999999.99）。要求更高的用更大精度。

三、字符串类型

类型	适用	注意
VARCHAR(N)	变长字符串（主机名、IP、名字）	按业务上限估长度
CHAR(N)	定长字符串（固定编码、hash）	不足 N 自动补空格
TEXT	长文本（文章内容、日志）	不能有默认值，索引要前缀

VARCHAR 最常用。关键点是长度按业务实际上限估，别全写 255。

为什么别都写 255？一方面浪费——存 32 字节的 IP 写 255，索引按声明长度分配内存。另一方面误导——读代码的人以为这字段最长 255，实际业务只到 45。按真实上限（IP 用 45、主机名用 128）声明，更准确。

TEXT 用于存很长的内容，但它不能设默认值，建索引要指定前缀长度（KEY(字段(100))），不如 VARCHAR 灵活。能用 VARCHAR 就别用 TEXT。

四、时间类型

类型	特点
DATETIME	不受时区影响，存什么取什么（8 字节）
TIMESTAMP	受时区影响，存入时转 UTC，取出时按会话时区显示（4 字节）

TIMESTAMP 的时区行为容易踩坑：同一个 TIMESTAMP 值，不同时区的会话查出来显示不一样。存的是 UTC，显示按会话时区。如果应用服务器和数据库时区不一致，TIMESTAMP 字段在不同地方看结果不同。

大多数业务场景用 DATETIME 更直观——存进去什么就是什么，不随会话时区变。需要自动记录创建/更新时间，配 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP（上一篇建表里那样）。

五、ENUM 和 JSON

ENUM 存固定枚举值：

env ENUM('dev','test','prod') NOT NULL DEFAULT 'test'

只能取列出的值之一。好处是省空间、值受约束。坏处是加新值要 ALTER TABLE——加个 staging 环境得改表结构。枚举值固定不变用 ENUM，可能变的用 VARCHAR 加应用层校验。

JSON 类型存结构化数据：

extra JSON

适合存扩展属性、不固定的配置。但频繁查询的 key 应该拆成独立列——JSON 里某个 key 要建索引、要查询，每次都要解析 JSON，慢且不方便。JSON 适合"偶尔看看"的字段，不适合核心查询字段。

六、NULL 的陷阱

NULL 在 SQL 里不是空字符串、不是 0——它表示"未知"。这个"未知"有一套特殊的三值逻辑，是很多 bug 的源头。

-- 唯一约束里，多个 NULL 不算冲突
-- 这张表的 remark 字段有 UNIQUE 约束，可以有多行 remark 为 NULL

-- 查询时 NULL 的坑
SELECT * FROM servers WHERE remark != 'test';
-- remark 为 NULL 的行查不出来！NULL != 'test' 的结果是"未知"，不是 true

判断 NULL 要用 IS NULL / IS NOT NULL，不能用 = / !=。

原则：字段如果不需要表达"没有值"，就加 NOT NULL 并给默认值。 只有确实需要区分"空字符串"和"未知"时才用 NULL。绝大多数字段应该 NOT NULL，少踩三值逻辑的坑。

七、约束

约束是建表时给字段加的规则，违反就报错。它们在数据库层挡住脏数据，不依赖应用代码：

约束	作用	违反时报错
PRIMARY KEY	唯一 + 非空	重复主键报错
UNIQUE	值唯一（允许多个 NULL）	Duplicate entry
NOT NULL	不能为空	插 NULL 报错
DEFAULT	不给值时填默认	静默填充
FOREIGN KEY	引用完整性（外键）	删被引用行时阻止或级联

前四个常用。FOREIGN KEY 要特别说——它保证引用完整性（订单引用的用户不能被删），但生产环境经常不用外键：

• 外键带来额外开销（每次写都校验引用）
• 删被引用行受限，批量维护麻烦
• 引用完整性更适合在应用层保证

小项目可以用外键兜底，高并发或大规模系统一般去掉外键，靠应用逻辑保证。

八、约束的取舍

约束不是越多越好。加约束意味着每次写入都校验，有性能开销。但少了约束，脏数据进来了后面清理更痛苦。

判断标准：

• 数据正确性关键的（主键、唯一、非空）——必加
• 业务规则明确的（状态码范围、环境枚举）——加
• 引用关系——看规模，小项目加，大项目应用层保证
• 性能敏感的高频写入——少加，靠应用校验

建表时把这些想清楚，比上线后改约束（又是重操作）省事得多。

类型选好之后

字段类型和约束定了，表结构稳定了。下一步是往表里存数据、查数据——增删改查。下一篇讲。