MySQL 的整数类型看起来简单TINYINT、INT、BIGINT 在大多数人眼里无非就是“能存多大的数”的区别。但我在一线帮人排查线上问题时发现类型选错造成的故障往往比 SQL 写错更隐蔽、更致命。上个月朋友公司的一张 6000 万行流水表就因为主键用了 INT 差点写不进去全组 DBA 加班到凌晨。今天我把三种整数类型的存储原理、选型方法和踩坑经验一次性讲透。1. 先讲个真实翻车案例6000 万行的流水表INT 主键差点扛不住朋友公司的核心业务是一张流水表主键id INT NOT NULL AUTO_INCREMENT跑了一年半快接近 21 亿的 INT 上限了。他们老板还在谈新合作说数据量再翻一倍没问题结果某天凌晨告警主键继续自增的插入开始出现Duplicate entry报错——虽然行数还不到 21 亿但自增计数已经越过边界再插入就冲突了。很多人觉得 21 亿很大了毕竟地球人也就 80 亿。但在互联网业务里流水表、日志表、埋点表是增长速度最吓人的三类表。尤其当你用AUTO_INCREMENT做单机自增主键时INT 的 4 字节空间会被快速消耗。这个案例让我意识到一件事整数类型选型本质是在做数据规模的预判。你不需要精确知道未来有多少行但你至少要判断它会不会超过 21 亿、能不能控制在 42 亿以内无符号 INT、或者干脆直接用 BIGINT 一劳永逸。2. 1 字节、4 字节、8 字节TINYINT/INT/BIGINT 的存储魔力和数学本质先看基础数据这是整篇文章的地基类型字节数位数有符号范围无符号范围TINYINT18-128 ~ 1270 ~ 255SMALLINT216-32768 ~ 327670 ~ 65535MEDIUMINT324-8388608 ~ 83886070 ~ 16777215INT432-2147483648 ~ 21474836470 ~ 4294967295BIGINT864-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615如果你记不住就记住一条计算规律n 位二进制有符号整数的最大值是 2^(n-1) - 1最小值是 -2^(n-1)无符号最大值是 2^n - 1。比如 INT 是 32 位有符号最大就是 2^31 - 1 2147483647。很多人在建表时对“大概够用”产生错觉问题就出在没把指数增长放在心上2 的 31 次方看起来是 21 亿但把一张表的每一行自增主键换算成“每天写入量 × 天数”你很快会算出临界时间。2.1 存储字节背后影响的不是“一个数字”而是整棵索引树MySQL 默认的 InnoDB 引擎B 树索引页的大小固定为 16KB。这意味着主键越短单个索引页能容纳的键值越多树的高度越低二级索引的叶子节点会存储主键值所以主键越大所有二级索引占用的空间也会水涨船高。举个例子一张 10 亿行的表如果主键是 BIGINT8 字节比 INT4 字节在单个二级索引上可能多出 4GB 以上的存储空间。为了给 20 年后可能出现的“超大数”留余地你把全表所有索引的体积扩大换来的是缓冲池命中率下降、磁盘 IO 上升。这也是我为什么特别反对“无脑 BIGINT”。2.2 别用浮点数或字符串存整数这是两笔智商税我见过有人用DOUBLE存金额、用VARCHAR(20)存订单号。整数用浮点存比较时会产生精度误差用字符串存排序会变成字典序10会排在9前面。TINYINT/INT/BIGINT 用二进制存储比较和计算都是 CPU 原生运算这是数据库设计里最不该妥协的部分。3. 结合业务场景定类型状态值、业务ID、时间戳、自增主键怎么选很多人的建表习惯是打开 Navicat字段类型统一INT(11)不够就 BIGINT。这种操作方式省事但会给后续埋雷。我把常见场景整理成一个选型表建议收藏数据特征推荐类型理由布尔值/枚举状态如 0、1、2TINYINT1 字节256 个取值足够小范围业务编码如分类 IDSMALLINT 或 MEDIUMINT省空间范围可预期普通计数器、文章浏览数INT单表百万级时足够21 亿很难突破自增主键日增百万级别BIGINT 或 INT UNSIGNED提前预留增长空间秒级时间戳INT UNSIGNED 或 BIGINTINT 有 2038 年问题建议直接 BIGINT雪花 ID / 分布式 IDBIGINT几十位的整数只有 BIGINT 装得下金额单位分BIGINT 或 DECIMAL整数存分能避免浮点误差3.1 TINYINT 不是“省一点空间”而是把状态控制写进了数据库状态字段用 TINYINT其实是在告诉团队这个字段只允许容纳个小集合。配合CHECK约束或者枚举约束乱写状态值的概率会小很多。我看过不少表演示代码用INT存一个只有三种状态的status浪费 3 个字节还是小事关键是将来你不知道它会不会被塞进一个奇怪的值。3.2 时间戳为什么别用 INT 了很多老系统用INT UNSIGNED存 Unix 时间戳上限 42 亿秒到 2106 年才溢出看起来没问题。但如果是有符号 INT2038 年就会溢出这就是著名的 Y2038 问题。其实问题本质是32 位有符号整数只装得下 1970 到 2038 年的秒数。我的建议是新表时间字段要么用 DATETIME要么用 BIGINT 存毫秒级时间戳别再用 INT 了。分布式系统里时间戳经常是毫秒精度一个毫秒级时间戳已经超过 17 亿秒级计算的话 32 位还能凑合毫秒级必须上 BIGINT。3.3 一个典型的建表选型案例CREATE TABLE user_activity_log ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT UNSIGNED NOT NULL COMMENT 用户ID来自分布式ID, activity_type TINYINT NOT NULL DEFAULT 0 COMMENT 活动类型:0点击,1收藏,2下单, duration_ms INT UNSIGNED NOT NULL COMMENT 停留时长(毫秒), log_date DATE NOT NULL, PRIMARY KEY (id), KEY idx_user_date (user_id, log_date) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户行为日志表;这里每一个BIGINT都不是随便选的而是对应上游系统生成的 64 位 ID。你把user_id换成 INT当时建表没问题等上线联调发现 ID 溢出就晚了。4. INT(11) 和 ZEROFILL早该被遗忘的显示宽度为什么还有人在问你在老版本的 Navicat 或 mysqldump 导出脚本里经常看到int(11)、tinyint(4)、bigint(20)这种写法。括号里的数字叫显示宽度display width它和存储空间、取值范围毫无关系纯粹是告诉 MySQL“当展示数字时最少补几个零”。这是一个历史遗留特性很容易让新手误以为INT(4)比INT(11)存得少实际上两者能存的范围一模一样。4.1 显示宽度最迷惑人的地方ZEROFILL如果把字段定义为CREATE TABLE t_demo ( num INT(5) ZEROFILL );插入123后查询你会看到00123。ZEROFILL 会在数字左侧补零而它依赖显示宽度。这个特性看起来很酷实际业务里几乎没人用因为它把“数字”变成了“带样式的文本”还会影响程序读取时的类型判断。4.2 MySQL 8.0 的态度废弃它从 MySQL 8.0 开始整数类型的显示宽度基本已被废弃新导出工具生成的建表语句已经不再包含INT(11)这种写法了。如果你还在用旧习惯写INT(11)代码能跑但没必要。我更推荐建表时直接写INT或INT UNSIGNED不带括号干净利落。提示如果你维护的是老项目导出 SQL 里全是int(10) unsigned不要试图把括号里的数字删掉再导入它们对现有数据没有任何影响删了反而容易引起不必要的 diff。5. UNSIGNED 不是银弹无符号整数的三个隐藏雷区无符号整数把负数范围让给了正数所以 INT UNSIGNED 上限是 42 亿而不是 21 亿。听起来很好但我在实际项目里吃过三次亏。5.1 雷区一主键一旦溢出回退空间变小如果主键用INT UNSIGNED你会觉得“42 亿够大了”。但当它真的满的那一天你要面临的不是“改 BIGINT 就行”而是整整一个生产库的索引重建。反过来如果一开始用有符号 INT在接近 21 亿时你还有“先改成 UNSIGNED 顶一顶”的空间。虽然我不推荐拿 UNSIGNED 当续命手段但它确实是排查问题时的额外退路。5.2 雷区二应用语言可能不支持无符号类型Java 里的int最大只能表示 21 亿你从数据库查出INT UNSIGNED的值是 30 亿时用int直接接收会变成负数。很多 ORM 框架默认把 INT 映射成 Java Integer一旦遇到无符号 INT 的大值就会踩坑。这属于典型的“数据库类型和语言类型不匹配”。5.3 雷区三混合运算时隐式转换很诡异看这段 SQLSELECT CAST(-1 AS SIGNED) CAST(1 AS BIGINT UNSIGNED);如果你拿有符号数和无符号数做比较MySQL 会把有符号数转成无符号数-1会变成一个巨大的正数比较结果完全违背直觉。这类 bug 的排查难度极高因为 EXPLAIN 不会直接报错只有执行结果诡异。所以我的经验是除非你非常确定字段永远不会参与负数运算、也不会和其他有符号字段关联否则不要随手加 UNSIGNED。主键可以例外但也要评估应用层的读取逻辑。6. 关联字段类型不一致索引失效、慢查询和数据错乱的真正源头整数类型选型还有一个特别隐蔽的坑两个表关联时字段类型必须完全一致。我把这个放在最后单独说因为它和 TINYINT/INT/BIGINT 的关系太密切了。6.1 典型的关联字段类型错配假设users表的id是 BIGINT而orders表的user_id是 VARCHAR(20)。你写SELECT * FROM orders o LEFT JOIN users u ON o.user_id u.id;执行计划里MySQL 大概率会对orders做全表扫描。因为o.user_id是字符串u.id是整数MySQL 会把字符串隐式转换成数字再比较这意味着索引列上发生了函数操作索引自然失效。6.2 更隐蔽的同类问题INT 和 BIGINT 关联有人问两个字段都是整数一个 INT一个 BIGINT应该没问题吧问题恰恰出在这里。如果orders.user_id是 INTusers.id是 BIGINT关联时一样可能发生隐式转换导致索引无法有效利用。因为 MySQL 在比较不同类型的整数时会把低精度类型向高精度类型转换这在执行计划里会被视为对列做了类型转换。6.3 排查方法遇到慢查询先跑一遍EXPLAIN SELECT ...看type列如果出现ALL且Extra列有Using where字样就要怀疑关联字段类型不一致。再用SHOW CREATE TABLE orders; SHOW CREATE TABLE users;逐个核对关联字段的列类型、有无符号、长度三者必须一致。注意不仅是关联字段要一致WHERE条件里的字段也要一致。比如WHERE user_id 12345678901如果user_id是 BIGINT字符串常量会被转成数字这是安全的但如果字段是 VARCHAR常量是数字索引也会失效。7. 选型错误之后怎么办ALTER TABLE 的代价和应急方案如果真的发生了 INT 主键临近溢出怎么办我的处理步骤是先确认当前自增值SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND TABLE_NAME your_table;低峰期执行类型变更ALTER TABLE your_table MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;这条 DDL 在 MySQL 8.0 里可以用在线方式执行但表很大时依然要重建索引耗时可能以小时计。我见过 2TB 的表改主键类型跑了整整一个晚上期间从库延迟一度拉到 10 分钟以上。如果业务完全不能停那就走双写方案新建一张 BIGINT 主键的新表从旧表导入数据同时业务层把新写入切到新表最终割接。这是最稳但最费劲的方案。我个人现在的原则是能选 BIGINT 的场景我不会省这 4 个字节但不该用 BIGINT 的小状态字段我也绝不会去浪费。整数选型从来不是数学题而是对业务增长曲线的预判题。希望这篇帖子能让你在建表时多想一步少熬一个夜。