做 MySQL 开发这些年我见过太多因为数据类型没选对而返工的表结构。有人用 varchar 存日期导致后面想做月份聚合只能字符串截取有人拿 float 存金额月底对账差出几毛钱怎么都查不明白还有人把全部数字字段都设成 bigint一张表愣是多出几倍的存储空间。数据类型看起来是建表时随手一填的小事实际上却是整个 MySQL 体系里最不该马虎的地基。这篇东西不打算复读官方文档我直接把常用的整数、小数、字符串、日期这四大类数据类型掰开揉碎讲一遍顺手把每个类型在真实业务里最容易踩的坑也一起说了适合正在建表、写 SQL或者准备优化表结构的人。1. 整数类型能扛住多大的数据比你想的更讲究整数是 MySQL 里最基础、也最容易被忽视的类型。大多数人的习惯是“不管什么数字都上 int”但 int 真的是万能解吗显然不是。我们先把五种整数类型的存储空间和取值范围拉出来看一遍你就知道该怎么选了。1.1 从 tinyint 到 bigint存储空间与取值边界MySQL 提供五种整数类型区别只在于占用的字节数不同进而决定了能存的数据范围。我用一张表把它们列清楚类型字节数有符号范围无符号范围tinyint1-128 ~ 1270 ~ 255smallint2-32768 ~ 327670 ~ 65535mediumint3-8388608 ~ 83886070 ~ 16777215int4-2147483648 ~ 21474836470 ~ 4294967295bigint8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615选型的基本逻辑就一句话根据业务数据的上界留出足够的增长余量选择一个字节数尽量小的类型。比如用户年龄tinyint 足够了订单状态码tinyint 或者 smallint 都行主键 ID 这种会持续增长且无法预估终点的字段直接上 bigint 才是最稳的选择。这里有一个常见的认知误区很多人以为“小类型更快”于是把 id 设成 mediumint觉得省空间、性能好。实际上在 InnoDB 引擎下单行数据的查询瓶颈很少出现在一个字段占三字节还是四字节上反而是在数据量达到千万级之后主键类型的容量耗尽才是真问题。你可以想象一下一张千万级订单表的主键 int 到了 21 亿上限你要 ALTER TABLE 换主键类型那个锁表时间足够让业务告警打爆手机。所以主键这种字段宁可一开始就 bigint也别后面折腾。1.2 int(11) 的老误区显示宽度不是存储上限早期经常能看到建表语句里写int(11)、tinyint(1)这种写法。很多人以为括号里的数字代表“最大能存多少位”这是 MySQL 使用中最经典的以讹传讹之一。括号里的数字其实是“显示宽度”它和 ZEROFILL 属性配合使用时才有一点意义比如int(5)配合ZEROFILL存 12 会显示成 00012纯属为了对齐展示。它并不会限制你存入的数值大小你写int(2)照样可以存 100000。从 MySQL 8.0 开始显示宽度语法已经被移除官方也彻底不推荐这种写法了。如果你在维护老项目看到int(11)也不用慌把它当成普通 int 就行存储行为和取值范围没有半点区别。另外要提醒的是ZEROFILL这个属性本身它会在你查询时自动补零但这只是展示层的加工存储值依然是数字。很多人被这种“假象”迷惑以为存进去的是字符串实际上是多虑了。1.3 数字字段的隐藏陷阱unsigned 用不好反而添乱无符号unsigned在某些场景下很顺手比如 AGE、数量这种天然不可能为负数的字段设成tinyint unsigned能让取值范围翻倍到 0~255。但问题也随之而来两个无符号字段相减结果如果为负MySQL 会报错或者给出一个非常大的无符号数。我记得有一次排查报表数据有个字段是“上次库存减去本次库存”算差值公式里的两个字段都是int unsigned一旦本次库存大于上次库存结果变成负数SQL 直接抛错导致整张报表跑不出来。当时查了半天才发现是这个原因。所以如果你有字段需要参与加减运算结果可能出现负数就不要轻易用 unsigned。另外在 Java 等后端语言里unsigned 类型的值如果超过有符号上限客户端驱动还可能出现数值溢出的问题解析出来变成负数。为了减少跨语言的沟通成本我的建议是业务主键不要用 unsigned让类型走有符号的正数范围只有那些我确定“永远不需要负数且不做减法”的状态字段才考虑 unsigned。还有一个偏门但很实用的小知识MySQL 里没有独立的布尔类型。BOOLEAN只是tinyint(1)的别名存 0 表示假、1 表示真。所以查布尔字段时WHERE flag 1才是正确的姿势别用 true有些驱动能识别有些不能纯属给自己埋坑。2. 小数存储float 的精度账decimal 才算得清小数是另一个事故高发区。很多人写金额字段时随手就是一个float因为这个类型写起来短、看着也顺眼。但 float 和 double 都是浮点数存储原理决定了它们天生就无法精确表示大多数十进制小数。2.1 float 精度丢失的底层逻辑解释一下为什么 float 存钱会出错。计算机用二进制存储小数而二进制只能通过 1/2、1/4、1/8 这种分数相加来逼近十进制的值。0.1 在二进制里就是一个无限循环小数float 只能用有限的精度去截断它所以存进去之后再读出来就不是完整的 0.1 了。举个最直观的例子你用 float 存 0.1再加上 0.2理论上得到 0.3但实际计算结果是 0.30000000000000004。单条记录看这个偏差无所谓但数据库里存的是海量订单每单差一点点月底一汇总几百上千万的流水总额就会差出几毛钱。很多财务对不上账的问题根子就在这里。我和别人讨论过为什么很多老系统里会有这种设计得到的答案往往是“当年图省事”。这个代价真的不值得——为了少写几个字符后面要对账、要改表、要迁移数据工作量翻几倍。2.2 decimal 是定点数精度和标度怎么设如果有精确计算的需求正确选择是DECIMAL(M, D)。M 是精度代表总共最多能存多少位数字D 是标度代表小数部分保留多少位。比如DECIMAL(10, 2)整数部分是 8 位小数部分是 2 位最多能存到 99999999.99。这里补充一个网上容易搜到的误区在 MySQL 5.0 之后的版本里decimal 的存储方式已经调整为每 9 位数字占用 4 个字节但在语义上它依然是“精确十进制”不会像 float 那样出现精度漂移。所以金额场景下decimal 才是诚实可靠的类型。那 M 和 D 到底怎么设我的建议是看业务里单笔金额的最大值再加上两到三年的增长余量。比如普通电商订单单笔撑死几万块用DECIMAL(10, 2)完全够如果是进销存系统涉及单价乘以数量可能出现大整数场景建议DECIMAL(14, 2)起步整数部分 12 位基本覆盖绝大多数企业的业务规模。还需要注意一点如果数据超出 D 的小数位MySQL 会四舍五入5.7 及之前版本部分模式会直接报错或告警所以在写入层就应该做好金额精度的校验别把脏数据交给数据库处理。2.3 金额字段的最佳实践和 float 的适用场景总结下来我处理金额字段的定型方案是所有涉及钱的字段一律DECIMAL应用层计算时也要注意各大语言的浮点数运算同样有精度问题应该用整数的“分”做计算再把结果转为“元”。数据库层只负责存储不要在 SQL 里做SUM(price * quantity)这类浮点乘法能转成 decimal 的字段就不该让 float 掺和进来。那 float 和 double 是不是就该被拉黑也不是。它们适用于科学计算、坐标位置、比率、百分比这类“允许极小误差”的场景。比如存经纬度用 double 既能保证足够的有效位数存储效率也高。这里头的原则就是能不能接受误差决定了你用 float 还是 decimal。3. 字符串类型varchar 长度的学问和 text 的暗坑字符串绝对是建表时决策最多、坑也最密集的类型。平时大家用得最多的是 varchar但真的追问起来能答清楚“为什么 varchar 里经常写 255”“text 和 varchar 到底差在哪”的人并不多。这一节我把它们彻底讲透。3.1 char、varchar 与 text 的本质区别先看存储层面的差异。char 是定长字符串长度范围 0~255 字符varchar 是变长字符串最大长度 65535 字节text 是专门存长文本的类型可以存到 65535 字节以上。既然是定长char 在存储时会用空格填充到指定长度取出时再把尾部空格去掉MySQL 8.0 以前的行为要小心。因此 char 适合存长度基本固定的短数据手机号、身份证号、MD5 校验值、固定位数的订单号。变长 varchar 则会在数据前额外记录长度信息短数据用 varchar 时每行多一两字节开销但整体更省空间。varchar 和 text 的界限以前很清晰但随着 MySQL 版本迭代两者的物理存储已经非常接近了。真正的区别在于使用感受text 不能设置默认值MySQL 8.0.13 之前而且无法在无前缀的情况下直接建普通索引排序和分组时行为也很别扭。所以我经常和团队说一句话能用 varchar 解决的问题不要指望让 text 来帮你兜底。3.2 varchar 长度里的“字符”和“字节”之争varchar(255)里的 255指的是 255 个字符而不是 255 个字节。这个细节直接决定了你为什么偶尔会遇到“字段过长插入失败”。一个字符占几个字节取决于表的字符集。utf8mb4 是目前的主流每个字符最多占 4 个字节。也就是说varchar(255)在 utf8mb4 下最多可能占用 255 × 4 1020 字节。再加上 InnoDB 的索引限制一个索引列最大字节数旧版本是 767 字节8.0 放宽到了 3072 字节你在老版本上给varchar(255)的 utf8mb4 字段建普通索引可能直接报 “Specified key was too long” 的错。所以这里有一个很实用的经验法则如果要给长字符串建索引不要把长度卡在 255。要么老老实实用前缀索引比如INDEX idx_name(name(20))要么根据实际业务缩短列长度。另外网上流传的“varchar(255) 是性能最优”也是一句没头没尾的结论。它可能来自早期某些版本的存储引擎优化逻辑但放到现在的 InnoDB utf8mb4 环境里盲目 255 反而会带来行变大、索引变宽的问题。正确思路是根据该字段的真实业务长度上限定值不要猜去量。3.3 别把 text 当万能筐排序、去重和临时表的连锁反应text 用得多了问题会从多个方向冒出来。第一个是排序问题。text 字段如果参与 GROUP BY 或 ORDER BYMySQL 只能使用前缀排序默认取前 1024 字节做排序键排序结果可能和你的直觉不一致。明明按字母排的序长文本字段却只比了对前几百个字符剩下的内容完全没参与。第二个是临时表问题。当查询需要用到内部临时表时text 字段可能会导致临时表无法使用内存暂存只能落到磁盘查询性能断崖式下跌。这在做复杂聚合报表时特别明显同样一批数据把 text 改成 varchar 后速度能差出好几倍。第三个是默认值问题。老版本里 text 不允许设置默认值很多 ORM 框架在迁移时专门为 text 字段绕路处理非常麻烦。包括现在使用 8.0 高版本text 的默认值支持也是有限制的远不如 varchar 省心。一句话总结这个章节短而规整的数据用 char一般业务字段用 varchar大段文章、JSON 原始串、日志快照这些才考虑 text。不要把 text 当成“什么都往里塞”的后备字段。4. 日期时间类型timestamp 的 2038 问题和 datetime 的正确姿势日期类型出错不像字符编码那样立刻爆出“乱码”它的坑往往是延迟引爆的等你想按月份汇总、按小时分析时才发现当初的存储选择让你只能用一串字符串做截取。4.1 三种日期类型的基本对比MySQL 常见的日期时间类型就三个date、datetime、timestamp。date 占用 3 字节只存日期范围 1000-01-01 到 9999-12-31。datetime 占用 8 字节存日期加时间范围同上。timestamp 占用 4 字节存的是从 1970-01-01 00:00:00 UTC 到当前时刻的秒数范围也就是 1970 年到 2038 年。这里值得展开的是 timestamp 和 datetime 的时区特性。timestamp 在存储时会把当前会话时区转换成的 UTC 时间存进去读取时再转回当前时区。意思就是如果你的业务是全球化的或者服务器的时区发生过调整timestamp 字段的值会跟着会话时区变化而变化。而 datetime 没有这个机制你存进去什么查出来就是什么不受时区影响。很多老项目里喜欢用 timestamp理由是它占空间小。但它最大的诅咒就是 2038 年问题因为使用 4 字节存储从 1970 年开始计数的秒数它在 2038 年 1 月 19 日就会溢出。虽然看着还有十几年但对于一些生命周期长的系统——比如银行、保险、政府项目——2038 年并不是遥不可及的事。4.2 设计日期字段的几个决定性细节先说默认值和自动更新。建表时给创建时间字段设置DEFAULT CURRENT_TIMESTAMP给更新时间字段设置ON UPDATE CURRENT_TIMESTAMP这样后续 INSERT、UPDATE 都不用手动维护时间减少出错概率。需要注意5.6 之前的版本不支持这种写法老库迁移到新版本时确认一下表结构的 DDL 就清楚了。其次是格式之争。我见过大量把日期存成varchar的“野路子”因为业务方最初传了个字符串进来程序员图省事直接扔进库里。等到后面要查“最近三个月的数据”SQL 只能写WHERE substr(create_time, 1, 7) 202401字段上套了函数索引直接失效数据量一大就是全表扫描。同样的需求datetime 字段写WHERE create_time 2023-11-01 AND create_time 2024-02-01索引走得好好的。所以不要用 varchar 存日期这是我这篇文章里最想强调的一点。再补充一个小知识8.0.19 之后 MySQL 支持用表达式设置字段默认值比如DEFAULT (DATE_FORMAT(NOW(), %Y-%m-%d))但实际项目中没几个人这么玩。创建一个时间字段保持原生的 datetime/timestamp 类型查询时再用 DATE_FORMAT 格式化才是最省心的方案。4.3 实际选型建议业务上优先 datetime很多人会纠结 timestamp 省空间优点但我的建议非常明确新业务统一用 datetime把时区问题交给应用层处理。理由有三层第一2038 问题直接绕开第二datetime 的行为简单可预期不会因为 DBA 调整了服务端时区导致历史数据全部“变了样”第三现在存储成本远没有十年前那么敏感8 字节换省心划算。如果业务确实要面向多时区用户比如海外业务我建议你在应用层统一按 UTC 时间生成并传入数据库用 datetime 存储查询展示时再按用户时区转换。这样数据库这一层的行为是确定性的排查问题时少一层变量。5. 特殊数据类型与最容易翻车的使用方式把基础四类讲完之后MySQL 里还有几个“看着简单但用起来处处是坑”的点位布尔、枚举、JSON以及开发中极其常见的隐式类型转换。这些不搞清楚前面学的类型选择知识可能在你写 SQL 的时候全部白费。5.1 布尔、枚举方便背后的维护性代价先说明 MySQL 里没有真正的布尔类型上一节我提过它实际是 tinyint(1)。但很多人会为了可读性把字段设计成enum(Y,N)或enum(yes,no)这就要说到枚举的维护成本了。enum 本质上是一个隐藏的整数索引字段存的是值在枚举列表里的位置。它有两个大问题一是加枚举值需要 ALTER TABLE在表数据量大的时候锁表风险非常高二是枚举的排序不是按照字母或拼音而是按照你在定义时写的顺序。如果你在一个 enum 里加了新值老数据的位置可能不变但新老数据的对比排序会出现让人摸不着头脑的结果。还有一个小坑如果你用 enum 存状态比如enum(待支付,已支付,已取消)业务后续要增加一个“退款中”的状态这就要改表。而状态枚举在真实业务里是会不断增加、调整的。与其这样不如直接用 tinyint 存状态码状态含义放在代码里的常量类或配置表中维护可扩展性和可读性都会好很多。5.2 JSON 类型不是洪水猛兽也不是万能药MySQL 5.7 开始支持原生 JSON 类型。它最大的优点是可以在 SQL 里直接操作 JSON 字段中的某个属性比如WHERE json_extract(info, $.age) 208.0 版本甚至支持用生成列给 JSON 里的某个 key 建索引。我的看法是JSON 适合存“结构不固定、只是用于展示”的配置数据比如用户扩展信息、商品动态属性。它不适合承载核心业务查询条件更不适合做 JOIN 的关联键。很多人碰到“字段不够用”就想塞 JSON这其实是表设计没想清楚的表现。如果某个 JSON 里的属性值需要频繁作为查询条件应该抽出来单独建列并建索引而不是在 JSON 里“挖”着查。我们团队的真实教训是早期为了快速上线把一批商品参数都塞进 JSON后来运营要做筛选排序每个条件都要用 JSON_EXTRACT 包一层索引基本废掉。最后我们还是老老实实做了字段拆分把那几个高频属性变成了普通列查询速度直接回到毫秒级。这个经验分享给在座各位JSON 可以用但它不是让你偷懒不设计表结构的理由。5.3 隐式类型转换索引失效的最常见元凶最后讲一个和数据类型强相关、但经常被人忽略的问题隐式类型转换。它指的是 SQL 中比较的两个值类型不一致时MySQL 会悄悄把其中一个转换成另一个再比较。转换一旦发生索引就可能失效或行为异常。最常见的场景就是字符串字段和数字比较。假设 user 表的 mobile 列是 varchar(11)你写WHERE mobile 13812345678MySQL 会把字符串列转换成数字再比较等于在每一行上执行了 CAST索引直接失效。更危险的情况是如果手机号里有前导零或者长度超过数字类型的精确范围你甚至查不出正确数据。同样的道理也适用于日期字段。datetime 和字符串比较时MySQL 会尝试把字符串转成日期大多数情况下没问题但如果字符串格式不规范或者比较的对象本身是函数结果也会带来隐藏的扫描代价。这里我给一个自查的习惯清单每次写查询条件时先问自己“比较的字段是什么类型右边传的值是什么类型”。两边类型不一致就该主动修正要么改 SQL 参数要么用 CAST 显式转换。显式转换虽然也可能导致索引失效但至少行为是明确的你能看到问题在哪里而不是被隐式转换悄无声息地坑掉。写 SQL 的人往往一上来就关注 join、子查询、索引但真正让一张表跑不动的常常就是这些数据类型层面的“小问题”。我自己在给团队做 code review 时看表结构的时间远多过看业务逻辑的时间。字段选对了索引建得才有意义索引有意义了SQL 优化才算真正落地。希望这篇文章能帮你在下一次建表时多想一层“这个字段到底该用什么类型、以后会不会成为查询条件”而不是顺手敲一个回车了事。如果真要往回改一张已经存了千万行数据的表那种痛苦谁改谁知道。