聊到 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 的整数类型分成五个档位类型存储字节有符号范围无符号范围TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 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 最大 65D 最大 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 只适合近似场景FLOAT4 字节和 DOUBLE8 字节不是一无是处。适合存测量值、温度、传感器读数、百分比展示这类“允许误差、只做趋势分析”的数据用 DOUBLE 完全没问题。但要切记不要在 WHERE 里对浮点列做等值比较比如WHERE score 9.9很可能查不到你以为的那条记录因为存储的是 9.899999999。如果必须精确匹配请用 DECIMAL。3. 字符串类型CHAR、VARCHAR 与 TEXT 的分界在哪3.1 VARCHAR 的长度是一个算术题VARCHAR(n) 里的 n 是字符数不是字节数。不同字符集下一个字符占用的字节数不同。默认 utf8mb4 下一个字符最多 4 字节所以 VARCHAR(255) 最多占用 255×421022 字节那 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 家族按长度分四档TINYTEXT255 字节、TEXT64KB、MEDIUMTEXT16MB、LONGTEXT4GB。表面上看 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 日期时间类型主要有三种先把关键差异摆出来类型存储字节范围时区关联DATE31000-01-01 到 9999-12-31无DATETIME81000-01-01 00:00:00 到 9999-12-31 23:59:59无TIMESTAMP41970-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 却显示typeALL。原因就是隐式类型转换比较的一边是 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 或 BIGINTINT 上限 21 亿很多互联网业务几年就会被撑爆业务编号订单号/流水号VARCHAR(64) 或 BIGINT含字母、前导 0 时必须 VARCHAR手机号VARCHAR(20)11 位数字但可能含 86、空格等字符不参与运算身份证号VARCHAR(18)18 位可能含大写 X必须字符串状态/枚举TINYINT UNSIGNED 或 SMALLINT0~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/DOUBLESUM、等值比较、四舍五入都会出现误差。日期写 VARCHAR日期函数用不了排序错乱格式不统一。状态写 VARCHAR(50)浪费空间状态约束形同虚设代码里还要拆字符串判断。主键写 INT 不写 UNSIGNED到 21.47 亿就有溢出风险。对高频写表来说这个量级在真实业务里并不罕见。团队里最好拉一份建表规范把这些固定下来。新同事写 DDL 时按模板来至少能避开 80% 的类型坑。最后聊一点个人习惯。我现在设计表结构时不会先想“这个字段用什么类型”而是先问四个问题这个字段的最大值是多少它要不要参与加减乘除它需不需要排序和比较它会作为查询条件走索引吗把这四个问题想清楚类型基本就定了。数据类型这件事没有一种万能选择只有适不适合当前业务。被手机号、金额、日期这些坑都教育过一遍之后我现在宁可建表时多花十分钟也不想上线后再花一晚上陪慢查询和错数据。