聊到 MySQL 数据类型，许多开发的第一反应是：int、varchar、datetime，选一个就完事。我过去也这么想，直到被线上事故教训过几次。这篇文章主要聊 MySQL 数据类型怎么选才能少踩坑：数值、字符串、日期时间、JSON 四大类型逐个拆，再聊一个最常见的隐式转换导致索引失效的坑，最后给出一张可以直接抄的建表类型清单。内容偏实战，不讲空泛原理。适合新手照着设计表，也适合被字段类型坑过、想彻底理清楚的老朋友。
1. 选错类型的代价：一个手机号字段引发的连环坑
1.1 手机号存INT，插入报错只是开始
先讲一个我经手排查过的真实问题。某张订单表的 receiver_phone 字段建的是 INT，测试阶段数据量小、假号码也短，暂时没暴露。等真实手机号开始写入，插入语句直接报：
Data truncation: Out of range value for column 'receiver_phone' at row 1原因很简单：手机号是 11 位数字，比如 13800138000 是 138 亿，而 INT 的上限只有 2147483647，约 21.4 亿。所有真实 11 位手机号，都远超这个上限。
当时还分两种环境处理：严格模式开着，插入直接报错，业务不可用；严格模式没开，MySQL 会把值截断成 2147483647，结果所有手机号都变成同一个“幽灵号码”。后面按手机号做会员匹配、发短信，整批串号，对账对到怀疑人生。
这件事里还有个更隐蔽的收尾问题：字段类型不对，后续用手机号和其他表做 JOIN 时，一侧是 VARCHAR、一侧是数字，发生隐式转换，关联出来的数据整批对不上。一个看似简单的建表决定，把开发、运维、报表的同事全拖下水了。
提示：手机号、身份证号、银行卡号这类“长得像数字，但不参与运算、也不需要排序大小”的信息，本质上是编码，要用 VARCHAR。
1.2 类型选错的三重代价：范围、精度与性能
手机号只是冰山一角。把类型选错的代价归纳起来，主要是三层：
- 存储代价：TINYINT 只占 1 字节，INT 占 4 字节，BIGINT 占 8 字节。单看一个字段似乎无所谓，但一张表到千万行、上亿行时，每个多余字节都会被放大成几 GB 的索引体积和缓冲池占用。状态字段到处用 BIGINT，是很多大表索引膨胀的根源。
- 精度代价：用 FLOAT/DOUBLE 存金额或分数，等值比较和汇总计算会出现尾部误差。这类问题往往要等到月底对账时才发现，排查成本极高。
- 性能代价：字符串列和数字常量做比较，会发生隐式类型转换，导致索引无法使用。这个场景太常见了，我会在第 6 章单独展开。
2. 数值类型怎么选：整数按峰值、小数看精度
2.1 整数类型范围速查与真正该关注的量级
MySQL 的整数类型分成五个档位：
| 类型 | 存储字节 | 有符号范围 | 无符号范围 |
|---|---|---|---|
| TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
| SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| INT | 4 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
| BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
选型逻辑就一句话：按业务峰值预估，绝不按当前值。比如状态字段用 TINYINT，有符号也就 128 个状态；如果业务状态码可能超过 128，就换 SMALLINT。别一上来就 BIGINT，也别因为“省得以后改”把所有整数字段都定义成 BIGINT。
我见过太多表把所有整数列都定为 BIGINT，中小型项目确实没啥感觉，但大表上索引体积和排序成本会被实实在在放大。我的习惯是：整数列默认 INT，只有明确需要巨大整数或做主键时才用 BIGINT；状态用 TINYINT/SMALLINT；数量用 INT UNSIGNED。
两个容易被忽略的细节：
- INT(11) 里的 11 是显示宽度，不是取值范围。配合 ZEROFILL 会补 0，但这功能从 MySQL 8.0.17 开始已标记废弃。不要再用“INT(11) 表示 11 位数字”来理解它。
- UNSIGNED 用于只允许非负的业务很合适，但要当心减法溢出。两个 UNSIGNED 列直接相减，结果为负数时会溢出成很大的正数，在严格模式下甚至直接报错。做库存扣减、余额变动这类计算时，建议先转成有符号或 DECIMAL 再算。
2.2 金额与比例：为什么必须用 DECIMAL
金额、利率、折扣、积分这类需要精确计算的字段，一律用 DECIMAL(M,D)。DECIMAL 是定点数，按十进制数字存储，不存在浮点误差。
DECIMAL(M,D) 的含义要牢记：M 是总位数（含小数位），D 是小数位数，M 最大 65，D 最大 30。比如 DECIMAL(10,2) 表示最大 99999999.99，总共 10 位，其中 2 位小数。
这里有一个非常常见的翻车现场：金额定义成 DECIMAL(5,2)，最大只有 999.99，商品单价上一千就溢出。选 M 之前先算业务上限：电商订单金额按亿级规划，用 DECIMAL(12,2) 到 DECIMAL(15,2) 比较稳；折扣比例、税率可以用 DECIMAL(5,4) 或 DECIMAL(8,4)。
为什么不能用浮点存金额？看这个最经典的例子：
SELECT 0.1 + 0.2; -- 结果可能是 0.30000000000000004浮点数是二进制近似存储，在比较和 SUM 汇总时会产生你完全不想看到的误差。金融类系统要求“分毫不差”，要么用 DECIMAL，要么在应用层用整数存最小货币单位（分）。我个人更倾向 DECIMAL，可读性好，配合汇总函数也方便。
注意：金额、折扣这类需要精确计算的字段，永远不要用 FLOAT/DOUBLE。
2.3 FLOAT/DOUBLE 只适合近似场景
FLOAT（4 字节）和 DOUBLE（8 字节）不是一无是处。适合存测量值、温度、传感器读数、百分比展示这类“允许误差、只做趋势分析”的数据，用 DOUBLE 完全没问题。
但要切记：不要在 WHERE 里对浮点列做等值比较，比如WHERE score = 9.9，很可能查不到你以为的那条记录，因为存储的是 9.899999999。如果必须精确匹配，请用 DECIMAL。
3. 字符串类型：CHAR、VARCHAR 与 TEXT 的分界在哪
3.1 VARCHAR 的长度是一个算术题
VARCHAR(n) 里的 n 是字符数，不是字节数。不同字符集下，一个字符占用的字节数不同。默认 utf8mb4 下，一个字符最多 4 字节，所以 VARCHAR(255) 最多占用 255×4+2=1022 字节，那 2 字节是记录长度的前缀。
为什么我会强调这个？因为很多人在建表时报过Column length too big。InnoDB 单行长度限制约 65535 字节，utf8mb4 下 VARCHAR 大约只能开到 16383 个字符，再长就要报错。要是你曾经把某列定义成 VARCHAR(20000)，那不是在给业务留空间，是在挑战 InnoDB 的行大小上限。
实际建表时，我很少把 VARCHAR 长度拉满。MySQL 计算临时表、内存分配时通常会按字段最大长度预估，长度越大会越容易触发磁盘临时表，导致排序和去重变慢。VARCHAR(64)、VARCHAR(128) 这类长度，对大多数业务字段已经够用。
另一个常被混淆的点：CHAR 和 VARCHAR 的区别。CHAR 是定长，定义了 CHAR(10) 就固定占 10 个字符的空间，检索时会去掉尾部空格；VARCHAR 是变长，只占实际字符数加长度前缀。现代 MySQL 里大部分场景优先用 VARCHAR 更省空间，只有长度基本固定且很短时，比如国家代码、固定编码，用 CHAR 才有意义。
3.2 TEXT 的隐性成本：排序与临时表的坑
TEXT 家族按长度分四档：TINYTEXT（255 字节）、TEXT（64KB）、MEDIUMTEXT（16MB）、LONGTEXT（4GB）。
表面上看 TEXT 就是“大一点的 VARCHAR”，但实际差别很大：
- 默认值：很长一段时间里 TEXT/BLOB 字段不能设置默认值，直到 MySQL 8.0.13 才允许通过表达式方式设置，但实际建表依然不建议依赖它。
- 索引：TEXT 字段如果要加索引，必须指定前缀长度，比如
INDEX idx_content (content(100))，没法直接对完整字段建普通索引。 - 临时表：ORDER BY 或 GROUP BY 用到 TEXT 字段时，由于 TEXT 不能放进内存临时表，MySQL 会退到磁盘临时表。一旦分组排序的数据量变大，SQL 会从毫秒级变成秒级甚至更慢。
所以我的原则是：能用 VARCHAR 存的内容就不要用 TEXT。评论、备注、简介这类长度在几百到几千的文本，VARCHAR(1000) 或 VARCHAR(2000) 通常撑得住；真正的长文本，比如文章正文、日志的 JSON 块，才用 TEXT 或 MEDIUMTEXT。
3.3 ENUM 与 SET：方便背后是维护成本
ENUM 和 SET 能把几种固定取值压缩存储，读取时返回字符串，初看很方便。但它有很现实的问题：
- 业务需要新增一个取值时，必须
ALTER TABLE修改 ENUM 定义。表大了以后，这个操作的成本和风险都不低。 - ENUM 排序按定义顺序，不是字典序。比如 ENUM('b','a')，排序结果是 b、a，很容易写出反直觉的查询。
所以我在大多数业务表里会用 TINYINT 存状态码，配合字段注释或代码枚举维护取值含义。ENUM 不是不能用，它适合“取值几乎永远不变”的场景，比如方向、星期、固定流程节点。除此之外，尽量别给自己找麻烦。
4. 日期时间类型：DATETIME 与 TIMESTAMP 的选择困局
4.1 TIMESTAMP 有 2038 期限，DATETIME 没有
MySQL 日期时间类型主要有三种，先把关键差异摆出来：
| 类型 | 存储字节 | 范围 | 时区关联 |
|---|---|---|---|
| DATE | 3 | 1000-01-01 到 9999-12-31 | 无 |
| DATETIME | 8 | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 | 无 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC | 有 |
TIMESTAMP 的 2038 年限制是 32 位时间戳的数字上限，MySQL 的 TIMESTAMP 也逃不掉。现在新建的表，如果核心时间字段用 TIMESTAMP，到 2038 年必然要改表。别觉得遥远，很多系统的设计寿命不止 30 年。新设计我建议优先 DATETIME。
DATETIME 的另一个优势是：它存储的是字面值，跟时区无关。业务需要展示成什么时区，完全由应用层控制。TIMESTAMP 则会在存储时按 session 的 time_zone 转成 UTC，查询时再转回当前时区。如果服务器时区配置混乱，TIMESTAMP 读出来很容易比你预期早 8 小时或晚 14 小时。
一个很典型的事故：应用服务器和数据库服务器的 time_zone 不一致，TIMESTAMP 字段读出来总是差 8 小时，排查半天才发现是时区问题。用 DATETIME 就没有这种烦恼。
4.2 默认值与精度：当前时间和毫秒级需求
MySQL 5.6.5 之后，DATETIME 和 TIMESTAMP 都可以使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。建表时加更新时间字段，可以直接这样写：
CREATE TABLE t ( id BIGINT PRIMARY KEY, title VARCHAR(128), created_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3), updated_at DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) );DATETIME(3)的 (3) 表示毫秒精度，DATETIME(6)是微秒。我做订单、流水类表时，至少保留毫秒。否则同一秒内写入大量数据，排序时没有先后依据，分页也很容易出现重复或乱序。
4.3 千万不要用 VARCHAR 或 INT 存时间
我见过不少表把时间存成 VARCHAR(20)，例如 '2023-01-15 12:03:22'。表面上传参方便，实际让人抓狂：
- 没法直接用
MONTH()、DATE_SUB()等日期函数； - 排序按字典序而不是时间序；
- 同一条时间在 '2023-01-01 00:00:00' 和 '2023/01/01 00:00:00' 两种格式下，排序结果完全不同。
用 INT 存 Unix 时间戳也是个常见方案，可读性差，而且FROM_UNIXTIME转换时很容易把时区搞错。MySQL 自己的 DATETIME 已经足够好，真的别自己造轮子。
5. JSON 字段与生成列：MySQL 8.x 的灵活与边界
5.1 JSON 类型相比 VARCHAR 存 JSON 强在哪
MySQL 从 5.7 开始支持原生 JSON 类型，8.0 又做了不少增强。很多人图省事，把 JSON 字符串直接塞进 VARCHAR/TEXT，然后在应用层反序列化，这是最原始但也最容易踩坑的做法。
JSON 类型自带校验，写入的不是合法 JSON 直接报错；查询时可以用JSON_EXTRACT、->、->>等操作符直接取字段，非常方便：
CREATE TABLE user_profile ( id INT PRIMARY KEY, name VARCHAR(50), attrs JSON ); INSERT INTO user_profile (id, name, attrs) VALUES (1, '张三', JSON_OBJECT('city', '上海', 'level', 3)); SELECT name, attrs->>'$.city' AS city FROM user_profile;5.2 想给 JSON 加索引，生成列是正解
JSON 字段本身不能直接建普通索引，这是很多人绕不过去的坎。正确的做法是使用生成列，把 JSON 里的某个字段“提取”成普通列，再对这个列建索引：
CREATE TABLE user_profile ( id INT PRIMARY KEY, name VARCHAR(50), attrs JSON, city VARCHAR(50) GENERATED ALWAYS AS (attrs->>'$.city') STORED, KEY idx_city (city) );之后WHERE city = '上海'就可以走索引。8.0.17 之后还支持多值索引，面向 JSON 数组场景，但语法和适用边界都更复杂，真正需要时再查官方文档。
一句话建议：JSON 适合存“结构不固定、不常用作查询条件”的属性。如果某个字段经常要过滤、排序、JOIN，那它就不该藏在 JSON 里，老老实实建成独立列。把核心业务数据全塞进 JSON 的“NoSQL 式偷懒”，早期省事，后期各种难受。
6. 隐式类型转换：查询从毫秒变秒的隐形元凶
6.1 一条 SQL 从毫秒变秒的排查实录
有一次我接手一个慢查询，现象是WHERE phone = 13800138000在几百万行的表上全表扫描，拖了足足 3 秒多。表结构里 phone 是 VARCHAR(20)，上面明明建了索引，explain 却显示type=ALL。
原因就是隐式类型转换：比较的一边是 VARCHAR 列，另一边是整数数字常量。MySQL 的转换规则是把字符串列的值全部转成数字再比，于是 phone 列上每一行都做了一次隐式 CAST，索引自然没法用。
改成字符串字面量，立刻恢复索引：
SELECT * FROM orders WHERE phone = '13800138000';我还遇到过应用层用参数化查询，把 phone 绑成数字类型，同样触发这个问题。反方向的一个常见误解是：字段本身是 INT，条件传字符串 '123'，这时优化器把常量 '123' 转成数字，索引还能用。因为转换发生在常量一侧，不影响列上的索引。这个方向差异很容易被忽视，值得在自己团队里反复强调。
6.2 类型转换规则速览与 CAST 显式控制
MySQL 比较运算的类型转换规则，简化后是这几条：
- 两边都是字符串：按字符串比较；
- 一边是数字、一边是字符串：字符串转数字，字符串列在这种比较下容易丢索引；
- 涉及日期时间与字符串：字符串先尝试转成日期时间，失败后再按浮点处理；
- NULL 参与比较：结果还是 NULL，走三值逻辑。
需要明确转换时，最好显式写CAST或CONVERT，不要依赖隐式行为。经常被忽略的是：CAST('abc' AS UNSIGNED)不会报错，而是返回 0，同时带一条 warning。非法数字字符串转数字时经常被当成 0，这会导致某些 SQL 条件莫名其妙命中“等于 0”的数据。
还有一个相关点：UNION 查询里，不同 SELECT 分支的列类型不一样时，MySQL 会自动做类型合并。比如一个分支是 INT，另一个分支是 VARCHAR，结果列可能被推导成 VARCHAR。如果之后把这个结果再去做 JOIN 或 WHERE，又可能引发新的隐式转换链。这类问题在复杂报表 SQL 里最容易出现，排查时要留意。
7. 一张可以直接抄的建表字段类型清单
7.1 常用业务字段的类型对应表
下面这份清单来自我多个项目的沉淀，覆盖了绝大多数业务表的常见字段场景：
| 场景 | 推荐类型 | 原因 |
|---|---|---|
| 主键 | BIGINT UNSIGNED 或 BIGINT | INT 上限 21 亿，很多互联网业务几年就会被撑爆 |
| 业务编号（订单号/流水号） | VARCHAR(64) 或 BIGINT | 含字母、前导 0 时必须 VARCHAR |
| 手机号 | VARCHAR(20) | 11 位数字，但可能含 +86、空格等字符，不参与运算 |
| 身份证号 | VARCHAR(18) | 18 位，可能含大写 X，必须字符串 |
| 状态/枚举 | TINYINT UNSIGNED 或 SMALLINT | 0~255 足够绝大多数状态机，扩展方便 |
| 布尔 | TINYINT(1) | 0/1，简单直接 |
| 名称/昵称 | VARCHAR(50) ~ VARCHAR(128) | 长度足够即可，别拉满 |
| 邮箱 | VARCHAR(255) | 255 是邮箱标准上限 |
| 地址 | VARCHAR(255) ~ VARCHAR(500) | 常见地址不会太长 |
| 长文本 | MEDIUMTEXT | 超过 64KB 再考虑 LONGTEXT |
| 金额 | DECIMAL(12,2) ~ DECIMAL(15,2) | 精确小数，避免浮点误差 |
| 税率/折扣 | DECIMAL(5,4) 或 DECIMAL(8,4) | 按业务精度要求 |
| 库存数量 | INT UNSIGNED | 数量天然非负 |
| 生日 | DATE | 只需要日期 |
| 创建/更新时间 | DATETIME(3) | 带毫秒，避免同秒排序问题 |
| JSON 属性 | JSON + 生成列索引 | 有校验，也有函数支持 |
7.2 几个容易写错的反面案例
这些年做技术支持，我总结过高频翻车现场：
- 手机号写 INT：所有 11 位真实号码都超上限，插入即崩。
- 身份证写 BIGINT：虽然多数 18 位数字不超出 BIGINT 范围，但尾号 X 无法处理，前导 0 会被丢掉。
- 金额写 FLOAT/DOUBLE：SUM、等值比较、四舍五入都会出现误差。
- 日期写 VARCHAR：日期函数用不了，排序错乱，格式不统一。
- 状态写 VARCHAR(50)：浪费空间，状态约束形同虚设，代码里还要拆字符串判断。
- 主键写 INT 不写 UNSIGNED：到 21.47 亿就有溢出风险。对高频写表来说，这个量级在真实业务里并不罕见。
团队里最好拉一份建表规范，把这些固定下来。新同事写 DDL 时按模板来，至少能避开 80% 的类型坑。
最后聊一点个人习惯。我现在设计表结构时，不会先想“这个字段用什么类型”，而是先问四个问题：这个字段的最大值是多少？它要不要参与加减乘除？它需不需要排序和比较？它会作为查询条件走索引吗？把这四个问题想清楚，类型基本就定了。数据类型这件事，没有一种万能选择，只有适不适合当前业务。被手机号、金额、日期这些坑都教育过一遍之后，我现在宁可建表时多花十分钟，也不想上线后再花一晚上陪慢查询和错数据。
