实际项目里我见过不少因为整数类型选错而引发的线上事故。就拿主键来说某平台早期用INT自增业务跑起来之后主键一度逼近21亿新增记录直接报错紧急改表的那几个小时全组人都盯着监控屏。反过来我也见过状态字段明明只有0和1两个取值却用了BIGINT一张千万级表白白多占几十GB存储。TINYINT、INT、BIGINT是MySQL里最常用的三种整数类型但很多人对它们的理解只停留在“小号、中号、大号”这种模糊层面字节数、取值范围、有符号无符号、显示宽度这些底层细节如果不吃透建表时很容易拍脑袋决定。我先把选型思路、核心原理、实操步骤和复盘经验一条线讲清楚不只是让你知道这三个类型差多少更希望你在下一次写CREATE TABLE的时候能下意识地算清楚“这个字段到底该用什么类型”。1. 整数类型选型的整体思路拆解TINYINT、INT、BIGINT 到底怎么选1.1 为什么这三种整数类型值得单独掰开揉碎讲日常开发里表设计往往被压缩在几分钟内完成业务逻辑还没理清就开始写CREATE TABLE。等到线上数据量上来了慢查询、锁表、空间暴涨这些问题接踵而来回头看根因十有八九出在数据类型这一层。整数类型尤其容易被低估因为它看起来太简单了不过是一个装数字的容器很少有人认真想过这个容器到底多大、够不够用、会不会浪费。TINYINT、INT、BIGINT分别占1字节、4字节、8字节单看一条记录差距确实不大但数据库表就是拿来装海量数据的一行差几个字节千万行就是几十GB的差距更别说二级索引里还会冗余存储主键值。整数类型的选择直接决定了存储成本、索引效率、查询性能甚至决定了某个字段会不会在某一天突然溢出导致写入失败。先把这个“为什么要重视”的问题讲透后面的实操方案才有意义。1.2 三种类型的定位与适用场景一张表看清全局先上结论三种整数类型的核心参数对比如下。类型字节数有符号范围无符号范围典型场景TINYINT1-128 ~ 1270 ~ 255状态码、布尔开关、星级评分、年龄、性别编码INT4-2147483648 ~ 21474836470 ~ 4294967295常规主键、用户ID、订单流水、计数器BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615分布式全局ID、雪花ID、外部系统大数值ID从这张表可以清楚看到TINYINT适合小范围枚举和标记类字段它能让单行更紧凑同一条SQL扫描的数据页更少性能收益实实在在。INT是绝大多数单库单表业务的默认选择4字节对于现代CPU和索引结构来说很均衡。BIGINT则面向超大规模或必须保证绝对唯一的大数值场景虽然空间代价最高但可以为后续扩展留足余量。具体到业务场景性别、用户状态、订单状态、渠道编号、来源平台、星期几这类取值不超过几十个的字段用TINYINT就够了如果未来有对接外部系统的可能性外部协议里定义了三位数编号那就要评估SMALLINT而不是死守TINYINT。用户ID、订单ID这类会持续增长的业务标识常规场景用INT没问题但一旦涉及分布式环境多节点生成ID或者需要和Redis自增、消息队列的全局序号对齐直接BIGINT别想着以后改。1.3 选型背后的三个核心维度容量、性能、扩展性先讲容量。选整数类型不是看“现在够不够用”而是看“未来够不够用”。我常用的估算办法是拿当前峰值乘以10作为未来三年的安全余量再对照取值范围表去选。比如当前用户量10万三年后100万INT绰绰有余但如果你做的是物联网设备ID、埋点ID这类可能指数增长的数据就要把余量放大到百倍千倍。再讲性能。同样的记录数字段字节越少单行占用越小InnoDB缓冲池能缓存的页就越多B树索引的扇出越大全表扫描和范围查询的成本都会显著下降。一个只有小整数字段的表和一个塞满大整数字段的表在同样的硬件条件下跑聚合查询性能差距可能是量级的。最后讲扩展性。选类型的隐形代价是“以后改字段类型有多难”。INT升BIGINT在合适的MySQL 8.0环境里可以秒级完成但在低版本上往往要重建表数据量大了就是几十分钟甚至几小时的锁表窗口。所以决策时要提前把“未来改动成本”算进去能一步到位的别留尾巴。把这三个维度拆开想清楚比单纯背类型范围表要实用得多。2. 字节、范围与显示宽度三种整数类型的核心细节2.1 字节、位与取值范围的内在逻辑很多人记不住三种类型的取值范围其实背后就是一个简单的二进制换算。1字节等于8位每一位是一个二进制位能表示0或1所以8位总共是2的8次方共256种组合。有符号类型需要拿最高位当符号位正数最大值就是2的7次方减1等于127负数最小值则是负的2的7次方等于-128加加减减正好覆盖256个值。把这个逻辑推广到所有整数类型就完全不用死记硬背。INT占4字节也就是32位有符号最大是2的31次方减1等于21亿多BIGINT占8字节64位有符号最大是2的63次方减1大概是9.22乘以10的18次方。中间档位的SMALLINT2字节最大32767MEDIUMINT3字节最大8388607也都能靠这个公式心算出来。再补充无符号的逻辑。UNSIGNED的意思是把原本用来表示符号的那一位也拿来存数据所以正数上限会变成原来的两倍减一。TINYINT UNSIGNED最大255INT UNSIGNED最大4294967295BIGINT UNSIGNED最大18446744073709551615。什么时候用无符号确认数据不可能为负并且需要更多正数空间时。最典型的场景是自增主键、计数器、各种ID字段。注意无符号不是“默认更安全”它只是把负数空间让给了正数选择时要想清楚业务里到底会不会出现负数。2.2 显示宽度与ZEROFILL历史上误导最多人的概念MySQL建表时经常能看到int(11)、bigint(20)、tinyint(4)这样的写法括号里的数字叫“显示宽度”它在存储和取值上没有任何作用。int(1)和int(11)底层完全一样都是4字节取值范围完全相同。显示宽度仅在配合ZEROFILL属性时会影响查询展示比如int(5) ZEROFILL存储的值为1查询出来会显示00001本质是给数字补零让报表对齐更美观。这个概念的误导性极强网上大量老教程还在教int(11)新手很容易把int(1)理解成“只能存一位数”。实际上int(1)一样能存21亿字段里出现上亿的数字完全不奇怪。更关键的是MySQL 8.0.17开始显示宽度语法已经被标记为废弃特性新版本里写int(11)不会报错但官方不再推荐。新项目里我建议直接写int不写括号里的数字干净且没有歧义。2.3 有符号与无符号选对了省空间选错了出怪Bug有符号和无符号的选择经常被忽视默认情况下MySQL整数都是有符号的。无符号使用得当可以扩大正数范围但也藏着两个典型坑必须提前知道。第一个坑是写入负数直接报错。MySQL 8.0对UNSIGNED列的约束比老版本严格往无符号字段写入负数会直接报ERROR老版本可能只是警告然后截断行为不一致容易让跨版本迁移的项目出问题。第二个坑非常隐蔽无符号字段参与减法运算时如果结果小于0MySQL不会返回负数而是可能返回一个巨大的无符号整数。比如在MySQL 5.7上执行SELECT CAST(1 AS UNSIGNED) - CAST(2 AS UNSIGNED)返回的不是-1而是18446744073709551615到了8.0的严格模式下甚至可能直接抛错。库存扣减、余额计算、年龄差计算这些场景里这种溢出最容易爆炸。所以我的个人习惯是常规业务字段大多数保持有符号只有确认数据不会为负且需要更多正数空间时才用UNSIGNED。自增主键用UNSIGNED可以延长寿命这是比较合理的场景但如果有任何一个无符号列要在SQL里做减法一定要显式CAST成SIGNED再计算。2.4 布尔值在MySQL里的真面目MySQL没有内置的布尔类型写BOOL或BOOLEAN最终都会被转换成TINYINT(1)。很多ORM框架看到TINYINT(1)会自动映射成布尔类型前端框架也经常把它渲染成勾选框这带来一个认知错位TINYINT(1)本质上是一个1字节整数它完全可以存127只是习惯上只用0和1来表示布尔语义。如果业务要求严格布尔属性不能让应用层随意写入其他值就需要在建表时加CHECK约束MySQL 8.0.16开始CHECK才真正强制生效。CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), CONSTRAINT chk_is_active CHECK (is_active IN (0, 1)) ) ENGINEInnoDB;这样做的价值是数据库层面兜底应用层就算传了2进来也会被拒绝。如果只是普通枚举状态字段也建议用语义更明确的TINYINT而不是裸写TINYINT(1)避免客户端和ORM误判。2.5 别把SMALLINT和MEDIUMINT这两个中间档位忘了虽然这篇文章的主角是TINYINT、INT、BIGINT但选型时MySQL的整数家族里还有两个中间档SMALLINT占2字节有符号最大32767无符号最大65535MEDIUMINT占3字节有符号最大8388607无符号最大16777215。它们的存在价值是填补“TINYINT不够用INT又太大”的空档。举个例子省份编码、端口号、业务错误码这类取值在几百到几万之间的字段用SMALLINT比INT节省2字节一张千万级表就能省20MB以上。订单量在百万到千万级别的流水号MEDIUMINT也常常够用。不过要注意中间档位的类型在ORM和语言层的映射不如INT、BIGINT那么顺滑有些框架可能不支持MEDIUMINT所以要兼顾开发效率不能为了省空间把团队折腾得很难受。3. 建表实操从选型到改表让每个整数列都各归其位3.1 第一步明确字段语义给每个整数列打标签动手建表前我习惯把所有整数列先分类。一般分四类标识类指主键、外键、业务编号关注唯一性和未来总量枚举类指状态、类型、渠道、等级看枚举值个数和扩展空间度量类指数量、次数、年龄、金额的整数部分注意量级和运算方式标记类指布尔开关、有无标志能小则小。分类不是形式主义每类字段的选型逻辑完全不同后面的步骤都是在这个基础上展开。这个分类动作看起来简单但它直接决定选型方向。同一个值域很窄的字段如果它是枚举类TINYINT足够如果未来可能对接外部协议、取值范围被外部放大那就要宽容一些。给字段打标签的过程其实也是把业务约束重新梳理一遍的过程很多建表时的“随手拍”都是因为跳过了这一步。3.2 第二步估算数据量把上限算出来再选类型选类型最怕拍脑袋我通常按“当前峰值乘以10”作为未来三年的安全余量。当前用户量10万三年后即使涨到100万用户ID用INT也绝对够但如果做的是埋点明细表一天就是上千万的写入量一年之后总量就是几十亿那主键或业务ID就得认真考虑BIGINT了。估算还有一个细节不要只盯着行数要把索引因素算进来。比如一张表的主键用INT二级索引有5个那么每行存储时主键值会被聚簇索引和5个二级索引各记一份一亿行就是不小的索引空间。如果换成BIGINT这个数字会明显变大。所以“够用”的定义里除了直观看数据量的上限还要考虑它对整个索引体系的空间放大效应。3.3 第三步主键类型单独决策不要随大流主键是全表访问最频繁的字段InnoDB的聚簇索引就建立在主键上所有二级索引的叶子节点又都会冗余保存主键值所以主键类型的选择会影响整张表的存储和索引。常规单库单表业务INT无符号主键上限42亿对绝大多数用户、订单类应用都够用但一旦涉及分布式ID、雪花ID、跨系统传递ID或者明确知道表量级会冲到亿级、十亿级直接BIGINT不要犹豫。这里说一个常见的反面案例有些团队觉得“主键用INT够了以后真的爆了再改”可真到爆的那天改主键类型往往要重建整个索引体系在低版本MySQL上就是一次锁表时间极长的DDL。相比之下建表时直接选BIGINT的成本几乎可以忽略。所以我的经验是拿不准主键用INT还是BIGINT时优先BIGINT。3.4 第四步AUTO_INCREMENT与索引配合时的细节自增列必须定义在某个索引上通常就是主键。TINYINT、INT、BIGINT都可以作为自增类型自增上限和类型上限一致一旦撞上上限新增记录会报Duplicate entry。很多人只考虑数据库层的自增上限忽略了应用语言层的类型匹配MySQL的BIGINT上限是9223372036854775807超过Java的int范围如果Java代码里用int接收主键数据库层还没到上限应用层就先溢出了。另外InnoDB在MySQL 8.0下的默认innodb_autoinc_lock_mode2批量插入时一次性申请一段自增值如果事务回滚或中途失败这段值会被消耗掉导致自增ID出现空洞比如连续插入几条之后下一条直接跳到100。这是正常行为不要当成故障去排查。早期5.7版本对自增锁的持有更保守但8.0已经彻底优化掉了批量插入时的性能瓶颈。3.5 第五步用SQL把字段类型查个明明白白判断线上某张表的字段到底用的什么类型最直接的命令是SHOW CREATE TABLE要批量排查几十张表INFORMATION_SCHEMA是更好的选择。下面这个查询可以直接列出某个数据库里所有表的整数列详情SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA your_db AND DATA_TYPE IN (tinyint, smallint, mediumint, int, bigint) ORDER BY TABLE_NAME, ORDINAL_POSITION;在Navicat这类图形客户端里打开表设计器也能看到完整类型但手动一张张点效率太低。我排查“哪些表用了BIGINT却不合理”“哪些状态字段用了INT”这类问题时就是用上面的SQL跑一遍再结合业务逐张确认比界面操作高效得多。3.6 从INT升级到BIGINT的平稳迁移方案如果线上确实需要把INT主键升级成BIGINT方案要选对。MySQL 8.0提供了ALGORITHMINSTANT选项某些纯类型扩展在满足条件时不需要重建表可以做到元数据级修改速度很快。可以这样执行ALTER TABLE order_record ALGORITHMINSTANT, MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;不过INSTANT算法有前置条件比如表结构、版本、行格式都会影响是否支持。执行时如果MySQL弹出了类似“ALGORITHMINSTANT is not supported”的错误就说明这张表不能用这个方案会自动放弃而不是静默回退这时就要改用其他方式。在低版本或超大表场景我更推荐配合在线改表工具或者用影子表迁移建新表、导数据、切换应用连接。核心原则是大表DDL永远要在低峰期执行并且操作前必须留有可回滚的备份。4. 线上案例复盘那些年整数类型挖过的坑4.1 事故现场INT主键自增溢出前面提到的线上事故再展开讲讲。某平台订单表主键用INT默认有符号上限2147483647。业务跑了大概三年订单量持续增长某天下午日志里突然出现大量Duplicate entry 2147483647 for key PRIMARY新增订单全部失败用户下单入口直接瘫痪。排查步骤很明确先看SHOW TABLE STATUS里Auto_increment列发现已经逼近2147483647再查当前最大ID确认临界状态最后定位是主键类型太小导致的容量危机。处理方案是低峰期把主键改成BIGINT UNSIGNED合适的MySQL 8.0环境可以用INSTANT算法快速完成如果版本不够或表结构不支持就得走在线改表工具或者影子表方式整个流程可能要数小时。这个案例的教训是主键类型不是“先凑合后面再改”的决策尤其是订单、流水这类只增不减的表它的增长速度往往比业务预估快得多。4.2 隐式类型转换索引失效的隐形杀手慢查询排查中我碰到过不少“明明有索引却不用”的案例根因就是隐式类型转换。MySQL在比较不同类型值时会把一边转成数字或字符串一旦转换发生在索引列上索引就失效了。方向不同影响也不同。字段是BIGINT、查询条件是字符串比如WHERE id 100123MySQL通常会把字符串转成数字索引还能用反过来如果字段是varchar、存了一串数字条件是WHERE user_no 100123MySQL会把该列全部转成数字再比较索引基本失效全表扫描。这种情况用EXPLAIN一眼就能确认type列变成ALLpossible_keys里有索引但实际没用上。修复方式是让两边的类型统一最干净的是从应用层把参数写成字段相同的类型。我也用过SHOW WARNINGS来查看MySQL具体做了什么类型的转换定位更快。这个坑在接口层传参比较随意时特别常见排查慢SQL时多留个心眼。4.3 TINYINT(1)与布尔勾选框的坑图形化客户端打开带TINYINT(1)字段的表时经常会看到一个勾选框这是客户端根据显示宽度猜测的布尔渲染。这种视觉误导很危险DBA在表设计器里只是点了一下可能就把0改成了1或者把状态勾掉业务数据就变了。有些ORM看到TINYINT(1)也会自动映射成Boolean如果一个字段业务上实际有0、1、2三个状态映射到Boolean之后2就会被当成true数据语义直接错乱。避坑建议很明确真正存布尔值的字段才用TINYINT(1)并且要加注释说明需要存多状态枚举时用TINYINT(4)或者直接TINYINT不给客户端和ORM误解的机会。如果已经建立了大量TINYINT(1)表改结构成本又高那就在应用层做严格校验把非法值挡在业务入口。4.4 无符号相减出来的天文数字无符号整数做减法溢出这个问题我在报表SQL里踩过一次。当时是一张库存表current_stock和locked_stock都定义的UNSIGNED INT报表里直接算available_stock current_stock - locked_stock。平时没问题某次上游数据错误导致被减数小于减数结果不但没有出现负数反而出了一个40多亿的天文数字报表直接失真排查了半天才定位到这个溢出逻辑。从那以后凡是涉及减法、甚至任何可能产生负数的计算我都会在SQL里显式CAST成SIGNED或者用CASE WHEN做保护。比如SELECT CASE WHEN current_stock locked_stock THEN current_stock - locked_stock ELSE 0 END AS available_stock FROM inventory;这个教训提醒我无符号类型可以安全地存数据但它不适合参与默认的数值运算尤其不要让两个无符号字段直接做减法。4.5 整数类型问题速查表整理一个常见问题速查表线上遇到问题可以直接对号入座。现象根因排查命令解决方案新增记录报Duplicate entry 2147483647INT自增溢出SHOW TABLE STATUS LIKE order_record升级BIGINT或影子表迁移查询极慢EXPLAIN显示ALL隐式类型转换EXPLAIN SHOW WARNINGS统一字段与查询条件的类型表设计器出现布尔勾选框TINYINT(1)SHOW CREATE TABLE改用TINYINT(4)或加CHECK约束SQL减法结果出现天文数字UNSIGNED溢出SELECT直接复算表达式CAST为SIGNED或CASE WHEN保护批量插入后自增ID不连续innodb_autoinc_lock_mode2的正常行为SHOW VARIABLES LIKE innodb_autoinc_lock_mode属于预期行为无需处理这张速查表我是按线上真实排障顺序整理的每个案例背后都是一次完整的排查过程。遇到类似问题时建议先别急着优化SQL先确认字段类型定义是否合理。我见过太多人花一下午调SQL最后发现索引失效的根源其实是字段类型和查询参数类型不一致方向定对了排障效率能提升一大截。4.6 建表时就把坑堵住的检查清单最后给一份可执行的自检清单每次建表前过一遍状态、标记类字段是否已经最小化能不能收敛到TINYINT标识类字段是否按“当前峰值乘10”估算过未来三年的量级主键是否考虑过二级索引带来的空间放大效应新项目是否还在写int(11)这种显示宽度语法如果是删掉。无符号字段会不会参与减法或负数运算如果有改成有符号或加保护逻辑。自增列的类型是否和上层语言Java、C#、Go的类型范围匹配表的注释里是否写清楚了TINYINT(1)这类字段的业务含义这条清单是我自己Review表结构时的底稿按它过一遍大部分整数类型相关的坑都能在建表前被挡住。配合前面讲的INFORMATION_SCHEMA查询批量扫描线上老表也能逐步清理。最后聊一点我自己的操作习惯。这几年经手过的表设计我基本遵循一条原则能用TINYINT表达的状态绝不上INT只要涉及分布式ID或者表量级有可能冲到千万以上主键直接BIGINT不在选型上反复纠结。还有一个习惯每次建完表我会顺手用INFORMATION_SCHEMA查一遍新表的全部字段类型和注释把所有“看着差不多就选了”的地方找出来改掉。整数类型本身不复杂但它挂在表设计、索引效率、业务增长、DDL变更这一整条链路上选错一次的代价远超选大一点。希望这篇内容能帮你把以后踩坑的时间省下来至少在写下一次CREATE TABLE时多花一分钟把字段类型算清楚。
彻底搞懂MySQL整数类型：TINYINT、INT、BIGINT选型避坑指南
实际项目里我见过不少因为整数类型选错而引发的线上事故。就拿主键来说某平台早期用INT自增业务跑起来之后主键一度逼近21亿新增记录直接报错紧急改表的那几个小时全组人都盯着监控屏。反过来我也见过状态字段明明只有0和1两个取值却用了BIGINT一张千万级表白白多占几十GB存储。TINYINT、INT、BIGINT是MySQL里最常用的三种整数类型但很多人对它们的理解只停留在“小号、中号、大号”这种模糊层面字节数、取值范围、有符号无符号、显示宽度这些底层细节如果不吃透建表时很容易拍脑袋决定。我先把选型思路、核心原理、实操步骤和复盘经验一条线讲清楚不只是让你知道这三个类型差多少更希望你在下一次写CREATE TABLE的时候能下意识地算清楚“这个字段到底该用什么类型”。1. 整数类型选型的整体思路拆解TINYINT、INT、BIGINT 到底怎么选1.1 为什么这三种整数类型值得单独掰开揉碎讲日常开发里表设计往往被压缩在几分钟内完成业务逻辑还没理清就开始写CREATE TABLE。等到线上数据量上来了慢查询、锁表、空间暴涨这些问题接踵而来回头看根因十有八九出在数据类型这一层。整数类型尤其容易被低估因为它看起来太简单了不过是一个装数字的容器很少有人认真想过这个容器到底多大、够不够用、会不会浪费。TINYINT、INT、BIGINT分别占1字节、4字节、8字节单看一条记录差距确实不大但数据库表就是拿来装海量数据的一行差几个字节千万行就是几十GB的差距更别说二级索引里还会冗余存储主键值。整数类型的选择直接决定了存储成本、索引效率、查询性能甚至决定了某个字段会不会在某一天突然溢出导致写入失败。先把这个“为什么要重视”的问题讲透后面的实操方案才有意义。1.2 三种类型的定位与适用场景一张表看清全局先上结论三种整数类型的核心参数对比如下。类型字节数有符号范围无符号范围典型场景TINYINT1-128 ~ 1270 ~ 255状态码、布尔开关、星级评分、年龄、性别编码INT4-2147483648 ~ 21474836470 ~ 4294967295常规主键、用户ID、订单流水、计数器BIGINT8-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615分布式全局ID、雪花ID、外部系统大数值ID从这张表可以清楚看到TINYINT适合小范围枚举和标记类字段它能让单行更紧凑同一条SQL扫描的数据页更少性能收益实实在在。INT是绝大多数单库单表业务的默认选择4字节对于现代CPU和索引结构来说很均衡。BIGINT则面向超大规模或必须保证绝对唯一的大数值场景虽然空间代价最高但可以为后续扩展留足余量。具体到业务场景性别、用户状态、订单状态、渠道编号、来源平台、星期几这类取值不超过几十个的字段用TINYINT就够了如果未来有对接外部系统的可能性外部协议里定义了三位数编号那就要评估SMALLINT而不是死守TINYINT。用户ID、订单ID这类会持续增长的业务标识常规场景用INT没问题但一旦涉及分布式环境多节点生成ID或者需要和Redis自增、消息队列的全局序号对齐直接BIGINT别想着以后改。1.3 选型背后的三个核心维度容量、性能、扩展性先讲容量。选整数类型不是看“现在够不够用”而是看“未来够不够用”。我常用的估算办法是拿当前峰值乘以10作为未来三年的安全余量再对照取值范围表去选。比如当前用户量10万三年后100万INT绰绰有余但如果你做的是物联网设备ID、埋点ID这类可能指数增长的数据就要把余量放大到百倍千倍。再讲性能。同样的记录数字段字节越少单行占用越小InnoDB缓冲池能缓存的页就越多B树索引的扇出越大全表扫描和范围查询的成本都会显著下降。一个只有小整数字段的表和一个塞满大整数字段的表在同样的硬件条件下跑聚合查询性能差距可能是量级的。最后讲扩展性。选类型的隐形代价是“以后改字段类型有多难”。INT升BIGINT在合适的MySQL 8.0环境里可以秒级完成但在低版本上往往要重建表数据量大了就是几十分钟甚至几小时的锁表窗口。所以决策时要提前把“未来改动成本”算进去能一步到位的别留尾巴。把这三个维度拆开想清楚比单纯背类型范围表要实用得多。2. 字节、范围与显示宽度三种整数类型的核心细节2.1 字节、位与取值范围的内在逻辑很多人记不住三种类型的取值范围其实背后就是一个简单的二进制换算。1字节等于8位每一位是一个二进制位能表示0或1所以8位总共是2的8次方共256种组合。有符号类型需要拿最高位当符号位正数最大值就是2的7次方减1等于127负数最小值则是负的2的7次方等于-128加加减减正好覆盖256个值。把这个逻辑推广到所有整数类型就完全不用死记硬背。INT占4字节也就是32位有符号最大是2的31次方减1等于21亿多BIGINT占8字节64位有符号最大是2的63次方减1大概是9.22乘以10的18次方。中间档位的SMALLINT2字节最大32767MEDIUMINT3字节最大8388607也都能靠这个公式心算出来。再补充无符号的逻辑。UNSIGNED的意思是把原本用来表示符号的那一位也拿来存数据所以正数上限会变成原来的两倍减一。TINYINT UNSIGNED最大255INT UNSIGNED最大4294967295BIGINT UNSIGNED最大18446744073709551615。什么时候用无符号确认数据不可能为负并且需要更多正数空间时。最典型的场景是自增主键、计数器、各种ID字段。注意无符号不是“默认更安全”它只是把负数空间让给了正数选择时要想清楚业务里到底会不会出现负数。2.2 显示宽度与ZEROFILL历史上误导最多人的概念MySQL建表时经常能看到int(11)、bigint(20)、tinyint(4)这样的写法括号里的数字叫“显示宽度”它在存储和取值上没有任何作用。int(1)和int(11)底层完全一样都是4字节取值范围完全相同。显示宽度仅在配合ZEROFILL属性时会影响查询展示比如int(5) ZEROFILL存储的值为1查询出来会显示00001本质是给数字补零让报表对齐更美观。这个概念的误导性极强网上大量老教程还在教int(11)新手很容易把int(1)理解成“只能存一位数”。实际上int(1)一样能存21亿字段里出现上亿的数字完全不奇怪。更关键的是MySQL 8.0.17开始显示宽度语法已经被标记为废弃特性新版本里写int(11)不会报错但官方不再推荐。新项目里我建议直接写int不写括号里的数字干净且没有歧义。2.3 有符号与无符号选对了省空间选错了出怪Bug有符号和无符号的选择经常被忽视默认情况下MySQL整数都是有符号的。无符号使用得当可以扩大正数范围但也藏着两个典型坑必须提前知道。第一个坑是写入负数直接报错。MySQL 8.0对UNSIGNED列的约束比老版本严格往无符号字段写入负数会直接报ERROR老版本可能只是警告然后截断行为不一致容易让跨版本迁移的项目出问题。第二个坑非常隐蔽无符号字段参与减法运算时如果结果小于0MySQL不会返回负数而是可能返回一个巨大的无符号整数。比如在MySQL 5.7上执行SELECT CAST(1 AS UNSIGNED) - CAST(2 AS UNSIGNED)返回的不是-1而是18446744073709551615到了8.0的严格模式下甚至可能直接抛错。库存扣减、余额计算、年龄差计算这些场景里这种溢出最容易爆炸。所以我的个人习惯是常规业务字段大多数保持有符号只有确认数据不会为负且需要更多正数空间时才用UNSIGNED。自增主键用UNSIGNED可以延长寿命这是比较合理的场景但如果有任何一个无符号列要在SQL里做减法一定要显式CAST成SIGNED再计算。2.4 布尔值在MySQL里的真面目MySQL没有内置的布尔类型写BOOL或BOOLEAN最终都会被转换成TINYINT(1)。很多ORM框架看到TINYINT(1)会自动映射成布尔类型前端框架也经常把它渲染成勾选框这带来一个认知错位TINYINT(1)本质上是一个1字节整数它完全可以存127只是习惯上只用0和1来表示布尔语义。如果业务要求严格布尔属性不能让应用层随意写入其他值就需要在建表时加CHECK约束MySQL 8.0.16开始CHECK才真正强制生效。CREATE TABLE user ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), CONSTRAINT chk_is_active CHECK (is_active IN (0, 1)) ) ENGINEInnoDB;这样做的价值是数据库层面兜底应用层就算传了2进来也会被拒绝。如果只是普通枚举状态字段也建议用语义更明确的TINYINT而不是裸写TINYINT(1)避免客户端和ORM误判。2.5 别把SMALLINT和MEDIUMINT这两个中间档位忘了虽然这篇文章的主角是TINYINT、INT、BIGINT但选型时MySQL的整数家族里还有两个中间档SMALLINT占2字节有符号最大32767无符号最大65535MEDIUMINT占3字节有符号最大8388607无符号最大16777215。它们的存在价值是填补“TINYINT不够用INT又太大”的空档。举个例子省份编码、端口号、业务错误码这类取值在几百到几万之间的字段用SMALLINT比INT节省2字节一张千万级表就能省20MB以上。订单量在百万到千万级别的流水号MEDIUMINT也常常够用。不过要注意中间档位的类型在ORM和语言层的映射不如INT、BIGINT那么顺滑有些框架可能不支持MEDIUMINT所以要兼顾开发效率不能为了省空间把团队折腾得很难受。3. 建表实操从选型到改表让每个整数列都各归其位3.1 第一步明确字段语义给每个整数列打标签动手建表前我习惯把所有整数列先分类。一般分四类标识类指主键、外键、业务编号关注唯一性和未来总量枚举类指状态、类型、渠道、等级看枚举值个数和扩展空间度量类指数量、次数、年龄、金额的整数部分注意量级和运算方式标记类指布尔开关、有无标志能小则小。分类不是形式主义每类字段的选型逻辑完全不同后面的步骤都是在这个基础上展开。这个分类动作看起来简单但它直接决定选型方向。同一个值域很窄的字段如果它是枚举类TINYINT足够如果未来可能对接外部协议、取值范围被外部放大那就要宽容一些。给字段打标签的过程其实也是把业务约束重新梳理一遍的过程很多建表时的“随手拍”都是因为跳过了这一步。3.2 第二步估算数据量把上限算出来再选类型选类型最怕拍脑袋我通常按“当前峰值乘以10”作为未来三年的安全余量。当前用户量10万三年后即使涨到100万用户ID用INT也绝对够但如果做的是埋点明细表一天就是上千万的写入量一年之后总量就是几十亿那主键或业务ID就得认真考虑BIGINT了。估算还有一个细节不要只盯着行数要把索引因素算进来。比如一张表的主键用INT二级索引有5个那么每行存储时主键值会被聚簇索引和5个二级索引各记一份一亿行就是不小的索引空间。如果换成BIGINT这个数字会明显变大。所以“够用”的定义里除了直观看数据量的上限还要考虑它对整个索引体系的空间放大效应。3.3 第三步主键类型单独决策不要随大流主键是全表访问最频繁的字段InnoDB的聚簇索引就建立在主键上所有二级索引的叶子节点又都会冗余保存主键值所以主键类型的选择会影响整张表的存储和索引。常规单库单表业务INT无符号主键上限42亿对绝大多数用户、订单类应用都够用但一旦涉及分布式ID、雪花ID、跨系统传递ID或者明确知道表量级会冲到亿级、十亿级直接BIGINT不要犹豫。这里说一个常见的反面案例有些团队觉得“主键用INT够了以后真的爆了再改”可真到爆的那天改主键类型往往要重建整个索引体系在低版本MySQL上就是一次锁表时间极长的DDL。相比之下建表时直接选BIGINT的成本几乎可以忽略。所以我的经验是拿不准主键用INT还是BIGINT时优先BIGINT。3.4 第四步AUTO_INCREMENT与索引配合时的细节自增列必须定义在某个索引上通常就是主键。TINYINT、INT、BIGINT都可以作为自增类型自增上限和类型上限一致一旦撞上上限新增记录会报Duplicate entry。很多人只考虑数据库层的自增上限忽略了应用语言层的类型匹配MySQL的BIGINT上限是9223372036854775807超过Java的int范围如果Java代码里用int接收主键数据库层还没到上限应用层就先溢出了。另外InnoDB在MySQL 8.0下的默认innodb_autoinc_lock_mode2批量插入时一次性申请一段自增值如果事务回滚或中途失败这段值会被消耗掉导致自增ID出现空洞比如连续插入几条之后下一条直接跳到100。这是正常行为不要当成故障去排查。早期5.7版本对自增锁的持有更保守但8.0已经彻底优化掉了批量插入时的性能瓶颈。3.5 第五步用SQL把字段类型查个明明白白判断线上某张表的字段到底用的什么类型最直接的命令是SHOW CREATE TABLE要批量排查几十张表INFORMATION_SCHEMA是更好的选择。下面这个查询可以直接列出某个数据库里所有表的整数列详情SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA your_db AND DATA_TYPE IN (tinyint, smallint, mediumint, int, bigint) ORDER BY TABLE_NAME, ORDINAL_POSITION;在Navicat这类图形客户端里打开表设计器也能看到完整类型但手动一张张点效率太低。我排查“哪些表用了BIGINT却不合理”“哪些状态字段用了INT”这类问题时就是用上面的SQL跑一遍再结合业务逐张确认比界面操作高效得多。3.6 从INT升级到BIGINT的平稳迁移方案如果线上确实需要把INT主键升级成BIGINT方案要选对。MySQL 8.0提供了ALGORITHMINSTANT选项某些纯类型扩展在满足条件时不需要重建表可以做到元数据级修改速度很快。可以这样执行ALTER TABLE order_record ALGORITHMINSTANT, MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;不过INSTANT算法有前置条件比如表结构、版本、行格式都会影响是否支持。执行时如果MySQL弹出了类似“ALGORITHMINSTANT is not supported”的错误就说明这张表不能用这个方案会自动放弃而不是静默回退这时就要改用其他方式。在低版本或超大表场景我更推荐配合在线改表工具或者用影子表迁移建新表、导数据、切换应用连接。核心原则是大表DDL永远要在低峰期执行并且操作前必须留有可回滚的备份。4. 线上案例复盘那些年整数类型挖过的坑4.1 事故现场INT主键自增溢出前面提到的线上事故再展开讲讲。某平台订单表主键用INT默认有符号上限2147483647。业务跑了大概三年订单量持续增长某天下午日志里突然出现大量Duplicate entry 2147483647 for key PRIMARY新增订单全部失败用户下单入口直接瘫痪。排查步骤很明确先看SHOW TABLE STATUS里Auto_increment列发现已经逼近2147483647再查当前最大ID确认临界状态最后定位是主键类型太小导致的容量危机。处理方案是低峰期把主键改成BIGINT UNSIGNED合适的MySQL 8.0环境可以用INSTANT算法快速完成如果版本不够或表结构不支持就得走在线改表工具或者影子表方式整个流程可能要数小时。这个案例的教训是主键类型不是“先凑合后面再改”的决策尤其是订单、流水这类只增不减的表它的增长速度往往比业务预估快得多。4.2 隐式类型转换索引失效的隐形杀手慢查询排查中我碰到过不少“明明有索引却不用”的案例根因就是隐式类型转换。MySQL在比较不同类型值时会把一边转成数字或字符串一旦转换发生在索引列上索引就失效了。方向不同影响也不同。字段是BIGINT、查询条件是字符串比如WHERE id 100123MySQL通常会把字符串转成数字索引还能用反过来如果字段是varchar、存了一串数字条件是WHERE user_no 100123MySQL会把该列全部转成数字再比较索引基本失效全表扫描。这种情况用EXPLAIN一眼就能确认type列变成ALLpossible_keys里有索引但实际没用上。修复方式是让两边的类型统一最干净的是从应用层把参数写成字段相同的类型。我也用过SHOW WARNINGS来查看MySQL具体做了什么类型的转换定位更快。这个坑在接口层传参比较随意时特别常见排查慢SQL时多留个心眼。4.3 TINYINT(1)与布尔勾选框的坑图形化客户端打开带TINYINT(1)字段的表时经常会看到一个勾选框这是客户端根据显示宽度猜测的布尔渲染。这种视觉误导很危险DBA在表设计器里只是点了一下可能就把0改成了1或者把状态勾掉业务数据就变了。有些ORM看到TINYINT(1)也会自动映射成Boolean如果一个字段业务上实际有0、1、2三个状态映射到Boolean之后2就会被当成true数据语义直接错乱。避坑建议很明确真正存布尔值的字段才用TINYINT(1)并且要加注释说明需要存多状态枚举时用TINYINT(4)或者直接TINYINT不给客户端和ORM误解的机会。如果已经建立了大量TINYINT(1)表改结构成本又高那就在应用层做严格校验把非法值挡在业务入口。4.4 无符号相减出来的天文数字无符号整数做减法溢出这个问题我在报表SQL里踩过一次。当时是一张库存表current_stock和locked_stock都定义的UNSIGNED INT报表里直接算available_stock current_stock - locked_stock。平时没问题某次上游数据错误导致被减数小于减数结果不但没有出现负数反而出了一个40多亿的天文数字报表直接失真排查了半天才定位到这个溢出逻辑。从那以后凡是涉及减法、甚至任何可能产生负数的计算我都会在SQL里显式CAST成SIGNED或者用CASE WHEN做保护。比如SELECT CASE WHEN current_stock locked_stock THEN current_stock - locked_stock ELSE 0 END AS available_stock FROM inventory;这个教训提醒我无符号类型可以安全地存数据但它不适合参与默认的数值运算尤其不要让两个无符号字段直接做减法。4.5 整数类型问题速查表整理一个常见问题速查表线上遇到问题可以直接对号入座。现象根因排查命令解决方案新增记录报Duplicate entry 2147483647INT自增溢出SHOW TABLE STATUS LIKE order_record升级BIGINT或影子表迁移查询极慢EXPLAIN显示ALL隐式类型转换EXPLAIN SHOW WARNINGS统一字段与查询条件的类型表设计器出现布尔勾选框TINYINT(1)SHOW CREATE TABLE改用TINYINT(4)或加CHECK约束SQL减法结果出现天文数字UNSIGNED溢出SELECT直接复算表达式CAST为SIGNED或CASE WHEN保护批量插入后自增ID不连续innodb_autoinc_lock_mode2的正常行为SHOW VARIABLES LIKE innodb_autoinc_lock_mode属于预期行为无需处理这张速查表我是按线上真实排障顺序整理的每个案例背后都是一次完整的排查过程。遇到类似问题时建议先别急着优化SQL先确认字段类型定义是否合理。我见过太多人花一下午调SQL最后发现索引失效的根源其实是字段类型和查询参数类型不一致方向定对了排障效率能提升一大截。4.6 建表时就把坑堵住的检查清单最后给一份可执行的自检清单每次建表前过一遍状态、标记类字段是否已经最小化能不能收敛到TINYINT标识类字段是否按“当前峰值乘10”估算过未来三年的量级主键是否考虑过二级索引带来的空间放大效应新项目是否还在写int(11)这种显示宽度语法如果是删掉。无符号字段会不会参与减法或负数运算如果有改成有符号或加保护逻辑。自增列的类型是否和上层语言Java、C#、Go的类型范围匹配表的注释里是否写清楚了TINYINT(1)这类字段的业务含义这条清单是我自己Review表结构时的底稿按它过一遍大部分整数类型相关的坑都能在建表前被挡住。配合前面讲的INFORMATION_SCHEMA查询批量扫描线上老表也能逐步清理。最后聊一点我自己的操作习惯。这几年经手过的表设计我基本遵循一条原则能用TINYINT表达的状态绝不上INT只要涉及分布式ID或者表量级有可能冲到千万以上主键直接BIGINT不在选型上反复纠结。还有一个习惯每次建完表我会顺手用INFORMATION_SCHEMA查一遍新表的全部字段类型和注释把所有“看着差不多就选了”的地方找出来改掉。整数类型本身不复杂但它挂在表设计、索引效率、业务增长、DDL变更这一整条链路上选错一次的代价远超选大一点。希望这篇内容能帮你把以后踩坑的时间省下来至少在写下一次CREATE TABLE时多花一分钟把字段类型算清楚。
