做后端这几年我见过太多数据库表设计翻车的案例。比如订单表的主键有人上来就写INT(11)结果业务量一上来还没等产品提新需求插入就先报Out of range value for column id又比如用户状态字段非要存成VARCHAR(10)后面每次查询都要比对字符串paid和unpaid索引压不住查一次慢一次。这些问题很多都是最开始选TINYINT、INT还是BIGINT时没想清楚埋下的根。这篇文章就把 MySQL 里这三个整数类型掰开揉碎讲清楚。它们存储上差多少、有符号无符号怎么选、实际业务里什么时候该用哪个、建表和改表时怎么少踩坑我都会结合自己做过的项目展开说。适合刚入门的同学建立 schema 设计直觉也适合写了好几年 SQL 但没认真抠过底层细节的开发者。看完至少能让你下次建表的时候心里更有底。1. 为什么整数类型选型直接决定表结构质量1.1 从一张“看起来能用”的业务表说起之前接手过一个电商项目订单表主键是INT状态字段是VARCHAR(20)金额字段竟然是FLOAT。表面上看业务跑得挺好但问题全藏在后面订单量一旦过千万INT自增主键很快就会逼近上限状态字段做分组统计时字符串比对比整数慢一个量级金额用浮点类型更是灾难一分钱差错的排查能让人崩溃。我后来把表结构整个改了一遍订单 ID 换成BIGINT UNSIGNED状态改成TINYINT金额改成DECIMAL(10,2)。改动之后数据量翻了几倍也没再出现溢出问题查询速度也明显回升。这个案例给我们的第一课是字段类型不是“能存下就行”的细节而是直接决定表结构未来能否平稳扩展的根基。很多开发者在设计初期觉得“先上线再说”但数据库不像代码代码可以轻松重构一张线上大表的字段类型修改往往意味着锁表、停机、数据迁移代价是巨大的。1.2 三个整数类型在存储层的本质差异TINYINT、INT、BIGINT都属于 MySQL 的整数类型核心差异就三件事占用字节数、取值范围、存储密度。TINYINT占用 1 字节有符号范围-128 ~ 127无符号范围0 ~ 255。INT占用 4 字节有符号范围-2147483648 ~ 2147483647无符号范围0 ~ 4294967295。BIGINT占用 8 字节有符号范围-9223372036854775808 ~ 9223372036854775807无符号范围0 ~ 18446744073709551615。这些数字背不下来也没关系你需要记住的关键信息是BIGINT能覆盖绝大多数业务场景但它的存储成本是INT的两倍是TINYINT的八倍。在 InnoDB 存储引擎里主键是聚簇索引二级索引的叶子节点会冗余一份主键值。也就是说主键每增大 1 字节每个二级索引也跟着多存 1 字节这个空间代价会随着索引数量和数据行数成倍放大。用生活里的抽屉来类比TINYINT是床头柜的小抽屉INT是衣柜的中号抽屉BIGINT是整个储藏间。抽屉越大能装的东西越多但占用的房子面积也越大。选型不是越大越好而是“够用 留适当冗余”。1.3 为什么 MySQL 里没有独立 BOOLEAN 类型很多人会问数据库里存“是/否”为什么不单独做一个 Boolean 类型答案有点出乎意料——MySQL 里BOOLEAN其实是TINYINT(1)的别名本质上还是整数类型。你写CREATE TABLE t (flag BOOLEAN);实际创建出来的是TINYINT(1)。这样设计的好处是节省类型种类让优化器只处理数字即可坏处是容易让新手误以为可以像其他数据库一样单独使用布尔逻辑。实际开发中TINYINT(1)配合0和1两个值就是最常见的布尔字段写法。千万别把它存成字符串的true/false否则查询时为了兼容布尔逻辑还得做隐式转换白白增加开销。2. 别再被int(11)骗了三大整数类型的核心差异2.1 显示宽度是什么为什么 MySQL 8.0 移除了它以前建表经常能看到INT(11)这种写法很多初学者以为括号里的 11 代表能存 11 位数字INT(1)就只能存一位数。这是一个流传很久的误解。INT(11)里的 11 是“显示宽度”它只在客户端展示时起作用配合ZEROFILL属性可以做补零显示比如INT(4) ZEROFILL存 12查出来显示0012。但它完全不限制存储范围INT(1)和INT(11)能存的数字大小完全一样都是 4 字节的整型范围。MySQL 8.0 直接把显示宽度移除了现在INT就是INT不再有这类“装饰性”参数。如果你还在旧项目里看到INT(11)可以放心地把它当作普通INT处理别被它干扰。如果你自己建新表也完全没必要写INT(11)直接写INT就行。ZEROFILL这个属性我建议慎用。它会把负数变成正数显示还会隐式给字段加上UNSIGNED属性让本来有符号的字段变成无符号容易引发数据比较上的坑。除非你有非常明确的补零展示需求否则别加。2.2 一张表帮你快速搞定选型我根据自己的实际使用经验总结了一张选型对照表类型字节数有符号范围无符号范围典型场景TINYINT1-128 ~ 1270 ~ 255状态码、开关值、性别、年龄、枚举INT4-21亿 ~ 21亿0 ~ 42亿常规业务 ID、计数器、普通整数BIGINT8-9.2×10^18 ~ 9.2×10^180 ~ 1.8×10^19自增主键、雪花ID、毫秒时间戳、超大数值经验判断是状态和布尔字段优先TINYINT普通业务数值用INT基本够所有从 0 开始且有明确上限的表主键我建议直接用BIGINT UNSIGNED。我们还经常会遇到“业务上不可能是负数”的字段比如用户 ID、积分、库存等。此时可以加上UNSIGNED属性直接让取值范围往正方向翻倍。但要注意UNSIGNED并不是免费的午餐它会让字段不能参与某些需要负数的运算比如两个无符号字段相减可能报溢出。选择时要想清楚业务边界。2.3 千万别拿整数类型存电话号码和身份证号这是我在工作中见过最多的低级错误之一。有人图省事把手机号、身份证号、银行卡号直接塞进INT或BIGINT结果踩了一堆坑。手机号如果是 11 位INT最大只有 21 亿也就是 10 位数直接存不下BIGINT虽然存得下但开头的 0 会被丢掉比如区号或者某些特殊号码身份证号有 18 位BIGINT也会精度丢失因为超出 19 位后部分语言会转成浮点数。就算字段类型够大这些号码根本不需要参与数值运算导致你每次查询都要忍受类型转换的成本。正确做法是用VARCHAR存这类“看起来像数字但本质是字符串”的字段。同样道理IP 地址如果只在日志里做展示也建议用VARCHAR如果要做高效范围查询可以额外用INT UNSIGNEDINET_ATON()存储但这属于进阶优化不是默认选择。我一直强调类型选型要贴近业务语义。一个字段到底是“数值”还是“编码”决定了它应该用整数类型还是字符串类型。这个判断失误后面再想纠正要付出的成本远超你当初省下的那五分钟。3. 建表和改表类型选型的实操全流程3.1 新表设计一个可以直接抄作业的订单表光讲理论没用我直接给一张可以复用的建表 SQL并解释每个字段为什么这么设计。CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键雪花ID或者自增, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号唯一索引, user_id BIGINT NOT NULL COMMENT 用户ID从用户表同步, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0待支付1已支付2已取消, pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 支付金额单位元, pay_type TINYINT NOT NULL DEFAULT 0 COMMENT 支付方式1微信2支付宝3银行卡, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;逐行拆解id用BIGINT UNSIGNED是因为订单量环境下INT的 42 亿上限并不遥远尤其分布式系统里主键经常需要全局唯一BIGINT才能容纳雪花 ID 等 64 位整数。user_id用BIGINT因为用户量一旦过亿INT无符号上限 42 亿虽然看着够但在分库分表或与其他系统交互时BIGINT更稳妥。status和pay_type用TINYINT因为它们就是状态机和枚举值字段量小用大整数纯属浪费。pay_amount用DECIMAL而不是FLOAT避免金额计算精度丢失这个已经算行业共识了。DEFAULT 0的写法对应很多人搜索的mysql设置默认值为0这个点。业务上很多字段天然有零值语义比如状态为待支付、库存为零、次数为零显式给出DEFAULT 0并且加上NOT NULL能避免 NULL 值带来的模糊性也能让索引更高效。3.2 字段扩容从 INT 改成 BIGINT 的正确打开方式万一一开始用INT做自增主键后面发现快不够用了怎么办最常见做法是ALTER TABLE改列类型。ALTER TABLE order_info MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;看起来就一行但实际操作里有几个关键点第一MODIFY COLUMN会把原列定义里的其他属性也一并覆盖。如果你原来有UNSIGNED、DEFAULT、COMMENT改的时候必须重新写全否则会被重置。第二这个操作会重建整张表在千万级乃至亿级大表上执行可能锁表几十分钟甚至数小时。第三如果有外键约束ALTER可能会因为外键检查而失败需要先评估外键依赖。所以我的建议是小表可以直接ALTER TABLE凌晨低峰期跑一下没问题大表一定要用在线 DDL 工具比如pt-online-schema-change或gh-ost。它们会创建一张影子表把旧数据分批拷贝过去再在切换阶段短暂加锁对业务影响小得多。改完之后记得重建相关索引并检查所有引用这张表主键的关联表关联字段也要同步改成BIGINT。否则一张表的主键是BIGINT另一张表的外键还是INTJOIN 时就会发生隐式类型转换直接导致索引失效性能断崖式下跌。3.3 用系统表快速排查全库不合理的整数类型如果你接手了一个老项目想快速知道哪些表在“乱用类型”不需要手工一张张看。MySQL 的information_schema.COLUMNS表里存了所有字段信息一条 SQL 就能查出全库所有整数类型分布。SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND COLUMN_TYPE IN (tinyint, int, bigint, smallint, mediumint) ORDER BY TABLE_NAME, ORDINAL_POSITION;这个结果能帮你一眼发现很多问题。比如有个表里status字段竟然用的是BIGINT明显过度设计还有个表里age用了INT但是年龄最多到 120 岁TINYINT UNSIGNED就足够了。逐个确认后集中出一份改造方案比遇到报错再回头排查要高效得多。也可以用这条 SQL 做容量审计统计每个表里整数类型字段占比找出那些“该用TINYINT却用了BIGINT”的字段从源头上控制数据膨胀。4. 实战中绕不开的类型坑溢出、隐式转换与兼容性4.1 整数溢出与 sql_mode 的相爱相杀整数溢出的报错很简单就一行ERROR 1264 (22003): Out of range value for column status at row 1比如TINYINT字段里插入了 300严格模式下直接报错。但很多人不知道的是这个报错并不是所有环境都会触发。MySQL 有宽松模式和严格模式之分如果在宽松模式下插入超出范围的值不会报错而是被“静默修正”为最大值或最小值然后插入成功。这种静默错误是最危险的——你明明写了 300数据库里却存了 127后面所有统计结果都是错的而且极难排查。因此我强烈建议把sql_mode设置成严格模式至少包含STRICT_TRANS_TABLES。在 MySQL 配置文件里加一行[mysqld] sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION设置完之后用SELECT sql_mode;确认生效。这能保证写入数据时数据库主动拒绝非法值而不是默默帮你“纠正”。另一个容易被忽视的是无符号字段的减法溢出。比如UPDATE stock SET num num - 10 WHERE id 1如果num是INT UNSIGNED且当前值是 3严格模式下会报BIGINT UNSIGNED value is out of range。应对办法是要么把库存字段设计成有符号整数要么在应用层先判断是否足够扣减再执行更新。4.2 隐式类型转换索引失效的头号嫌疑犯每个优化器在比较两个不同数据类型时都会进行隐式类型转换。转换本身不可怕可怕的是转换发生在索引列上导致索引失效。举个例子假设有张用户表user_id是VARCHAR(64)你这条 SQL 看起来没什么问题SELECT * FROM user WHERE user_id 10086;你以为字段值是字符串10086用数字 10086 去查。但 MySQL 会判断你的字段是字符串类型于是把字段列里的每个值都转成数字再和 10086 比较。结果就是索引列上发生了函数操作优化器只能放弃索引走全表扫描。这张表一旦有几百万行查询就会像老牛拉车一样慢。反过来如果字段是INT你传字符串10086MySQL 会把字符串转成数字再和索引列比较这种情况下索引通常还能用。所以最稳妥的习惯是写 SQL 时保持字段类型和查询条件类型一致。WHERE user_id 10086就要确保user_id是字符串WHERE id 10086就要确保id是数字。更隐蔽的隐式转换发生在 JOIN 里。A 表user_id是BIGINTB 表user_id是INT两个字段类型不一致JOIN 时可能全表扫描。这也是为什么前面强调关联字段类型必须一致而且最好都统一成BIGINT一劳永逸。4.3 跨数据库迁移时最容易踩错的小类型现在很多项目要做国产化、上云或者从 Oracle 迁移到 MySQL类型映射是绕不开的坎。很多人直接“一对一搬”结果搬完数据全乱了。给你一张简单的对照表参考MySQLOracleSQL ServerPostgreSQLTINYINTNUMBER(3)TINYINT0~255SMALLINTSMALLINTNUMBER(5)SMALLINTSMALLINTINTNUMBER(10)INTINTEGERBIGINTNUMBER(19)BIGINTBIGINT这里有个特别容易翻车的点SQL Server 的TINYINT范围是0~255不含负数MySQL 默认TINYINT却是-128~127。如果你从 SQL Server 迁移一张“有TINYINT字段值为 200”的表到 MySQL原样建TINYINT字段插入 200 直接溢出。正确做法是先改成TINYINT UNSIGNED或者直接升到SMALLINT。Oracle 那边常见的是NUMBER不指定长度迁移时不要想都不想就转成DECIMAL要根据实际最大值决定用INT还是BIGINT。否则要么空间浪费要么数据溢出。4.4 编程语言映射不对接口数据悄悄变乱类型问题不只发生在数据库内部还会发生在 ORM 映射层。比如 Java 里Long对应 MySQL 的BIGINTInteger对应INTShort对应SMALLINTByte对应TINYINT这个映射如果反了问题就来了。我见过一个真实案例表字段明明是INT实体类却用了Long。日常数据量小看不出问题但某天一条超过 21 亿的 ID 被写入时数据库直接抛错而应用层日志里只看到一堆 500 错误。排查半天才发现是字段类型根本撑不住这个数值。反过来如果表字段是BIGINT实体类却用了IntegerJava 侧会发生精度丢失读取出来的数据可能是负数或者被截断的值这在传输层特别隐蔽。解决方法只有一条ORM 实体类字段类型和数据库字段类型严格对齐别偷懒。5. 资深工程师才会去想的“长期主义”类型设计不只是建表那一下5.1 空间与性能算一道 1000 万行的账很多人觉得多几个字节无所谓但一把账算下来你会发现差距惊人。假设你的表有 1000 万行主键从INT换成BIGINT每行多 4 字节光主键就多 40MB。如果表上有 3 个二级索引二级索引叶子节点会冗余主键值那么这些索引还要额外多 120MB。合计多出 160MB这还没算数据页分裂带来的碎片开销。如果是 10 亿行的大表差距就是 1.6GB 以上。虽然现在磁盘和内存都便宜但 InnoDB 的索引数据是要放进缓冲池的内存里每多 1MB 无用数据能缓存的热数据就少 1MB。所以类型大小的浪费最终会反映到内存命中率和查询延迟上。反过来也不能因为空间紧张就全都用TINYINT。我见过核心业务表为了防止溢出把所有数值字段全部改成BIGINT这虽然避免了溢出但纯属矫枉过正。合理做法是做一个 3~5 年的容量预估算一下每天大概产生多少条记录、每年增长率多少再乘以安全系数 0.7 或 0.8得出的值落在哪个范围就选哪个类型。比如日增订单 10 万一年就是 3650 万三年约 1.1 亿INT无符号完全够用但如果要做分库分表或者分布式 ID那主键就必须BIGINT。5.2 布尔值、状态位、计数器怎么设计最省心说实话市面上很多表设计指南没有把这几个“高频小字段”讲透我补一下个人经验。布尔值字段比如is_deleted、is_active直接用TINYINT(1) NOT NULL DEFAULT 0。注意不要用ENUM(Y,N)因为ENUM底层虽然是整数但变更枚举值需要重建表结构排序和比较规则也特殊维护成本高。状态位字段比如订单状态、支付状态用TINYINT存数字枚举比如 0、1、2。同时要用COMMENT把每个数字的含义写清楚。有人会觉得写注释麻烦但等你半年后回来看表一张没有注释的状态字段表能让你怀疑人生。计数器字段比如浏览量、点赞数、库存。如果业务上不可能是负数用INT UNSIGNED如果有扣减到负数场景用有符号INT并在应用层做校验。特别提醒不要用FLOAT或DOUBLE存计数精度会飘。我自己通常还会为每个表加上create_time、update_time并且用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP管理时间。这样在排查数据问题时至少知道一条数据的生命周期。5.3 一次两亿行大表改类型的复盘最后分享一个真实的全量改造案例。某个订单中心的主表user_id一开始用INT当时估算是 10 年量级没问题结果业务增长远超预期不到 3 年就逼近 5 亿。如果继续用INT再过半年就会溢出。当时有几个可选方案直接ALTER TABLE、用在线 DDL 工具、或者新建表迁移。这张表数据量已经到 2 亿行直接ALTER预计锁表超过 1 小时绝对不可接受。最后我们选了gh-ost。大致流程是预检通过information_schema检查所有外键、触发器、视图确认没有依赖陷阱。全量备份确保出问题能回滚。启动gh-ost创建影子表并开启 binlog 监听。影子表先接收一次全量数据拷贝再持续同步增量数据。等待追平后在流量低峰期执行切换把原表切到影子表。切换完成后重建二级索引并验证主从数据一致性。整个过程耗时大约 40 分钟业务侧只出现了一次极短暂的连接闪断算是把影响控制到了最小。但事后复盘所有人都承认如果当初建表时就用BIGINT这次折腾根本不会发生。这就是类型设计上“长期主义”的价值——它会在你几乎忘掉它的那天替你挡掉一个大坑。如果你现在正在规划一个新项目的库表结构我的建议很朴素主键默认BIGINT UNSIGNED状态和开关用TINYINT常规数值用INT精确数值用DECIMAL时间用DATETIME编码类字符串用VARCHAR。不确定该用哪个时先问自己三个问题这个字段会不会参与运算它的最大值是多少三年后它还会是这个量级吗想清楚这三件事你就能避开数据库设计里最让人头疼的一部分坑了。
MySQL整数类型选型：TINYINT、INT、BIGINT如何避坑？
做后端这几年我见过太多数据库表设计翻车的案例。比如订单表的主键有人上来就写INT(11)结果业务量一上来还没等产品提新需求插入就先报Out of range value for column id又比如用户状态字段非要存成VARCHAR(10)后面每次查询都要比对字符串paid和unpaid索引压不住查一次慢一次。这些问题很多都是最开始选TINYINT、INT还是BIGINT时没想清楚埋下的根。这篇文章就把 MySQL 里这三个整数类型掰开揉碎讲清楚。它们存储上差多少、有符号无符号怎么选、实际业务里什么时候该用哪个、建表和改表时怎么少踩坑我都会结合自己做过的项目展开说。适合刚入门的同学建立 schema 设计直觉也适合写了好几年 SQL 但没认真抠过底层细节的开发者。看完至少能让你下次建表的时候心里更有底。1. 为什么整数类型选型直接决定表结构质量1.1 从一张“看起来能用”的业务表说起之前接手过一个电商项目订单表主键是INT状态字段是VARCHAR(20)金额字段竟然是FLOAT。表面上看业务跑得挺好但问题全藏在后面订单量一旦过千万INT自增主键很快就会逼近上限状态字段做分组统计时字符串比对比整数慢一个量级金额用浮点类型更是灾难一分钱差错的排查能让人崩溃。我后来把表结构整个改了一遍订单 ID 换成BIGINT UNSIGNED状态改成TINYINT金额改成DECIMAL(10,2)。改动之后数据量翻了几倍也没再出现溢出问题查询速度也明显回升。这个案例给我们的第一课是字段类型不是“能存下就行”的细节而是直接决定表结构未来能否平稳扩展的根基。很多开发者在设计初期觉得“先上线再说”但数据库不像代码代码可以轻松重构一张线上大表的字段类型修改往往意味着锁表、停机、数据迁移代价是巨大的。1.2 三个整数类型在存储层的本质差异TINYINT、INT、BIGINT都属于 MySQL 的整数类型核心差异就三件事占用字节数、取值范围、存储密度。TINYINT占用 1 字节有符号范围-128 ~ 127无符号范围0 ~ 255。INT占用 4 字节有符号范围-2147483648 ~ 2147483647无符号范围0 ~ 4294967295。BIGINT占用 8 字节有符号范围-9223372036854775808 ~ 9223372036854775807无符号范围0 ~ 18446744073709551615。这些数字背不下来也没关系你需要记住的关键信息是BIGINT能覆盖绝大多数业务场景但它的存储成本是INT的两倍是TINYINT的八倍。在 InnoDB 存储引擎里主键是聚簇索引二级索引的叶子节点会冗余一份主键值。也就是说主键每增大 1 字节每个二级索引也跟着多存 1 字节这个空间代价会随着索引数量和数据行数成倍放大。用生活里的抽屉来类比TINYINT是床头柜的小抽屉INT是衣柜的中号抽屉BIGINT是整个储藏间。抽屉越大能装的东西越多但占用的房子面积也越大。选型不是越大越好而是“够用 留适当冗余”。1.3 为什么 MySQL 里没有独立 BOOLEAN 类型很多人会问数据库里存“是/否”为什么不单独做一个 Boolean 类型答案有点出乎意料——MySQL 里BOOLEAN其实是TINYINT(1)的别名本质上还是整数类型。你写CREATE TABLE t (flag BOOLEAN);实际创建出来的是TINYINT(1)。这样设计的好处是节省类型种类让优化器只处理数字即可坏处是容易让新手误以为可以像其他数据库一样单独使用布尔逻辑。实际开发中TINYINT(1)配合0和1两个值就是最常见的布尔字段写法。千万别把它存成字符串的true/false否则查询时为了兼容布尔逻辑还得做隐式转换白白增加开销。2. 别再被int(11)骗了三大整数类型的核心差异2.1 显示宽度是什么为什么 MySQL 8.0 移除了它以前建表经常能看到INT(11)这种写法很多初学者以为括号里的 11 代表能存 11 位数字INT(1)就只能存一位数。这是一个流传很久的误解。INT(11)里的 11 是“显示宽度”它只在客户端展示时起作用配合ZEROFILL属性可以做补零显示比如INT(4) ZEROFILL存 12查出来显示0012。但它完全不限制存储范围INT(1)和INT(11)能存的数字大小完全一样都是 4 字节的整型范围。MySQL 8.0 直接把显示宽度移除了现在INT就是INT不再有这类“装饰性”参数。如果你还在旧项目里看到INT(11)可以放心地把它当作普通INT处理别被它干扰。如果你自己建新表也完全没必要写INT(11)直接写INT就行。ZEROFILL这个属性我建议慎用。它会把负数变成正数显示还会隐式给字段加上UNSIGNED属性让本来有符号的字段变成无符号容易引发数据比较上的坑。除非你有非常明确的补零展示需求否则别加。2.2 一张表帮你快速搞定选型我根据自己的实际使用经验总结了一张选型对照表类型字节数有符号范围无符号范围典型场景TINYINT1-128 ~ 1270 ~ 255状态码、开关值、性别、年龄、枚举INT4-21亿 ~ 21亿0 ~ 42亿常规业务 ID、计数器、普通整数BIGINT8-9.2×10^18 ~ 9.2×10^180 ~ 1.8×10^19自增主键、雪花ID、毫秒时间戳、超大数值经验判断是状态和布尔字段优先TINYINT普通业务数值用INT基本够所有从 0 开始且有明确上限的表主键我建议直接用BIGINT UNSIGNED。我们还经常会遇到“业务上不可能是负数”的字段比如用户 ID、积分、库存等。此时可以加上UNSIGNED属性直接让取值范围往正方向翻倍。但要注意UNSIGNED并不是免费的午餐它会让字段不能参与某些需要负数的运算比如两个无符号字段相减可能报溢出。选择时要想清楚业务边界。2.3 千万别拿整数类型存电话号码和身份证号这是我在工作中见过最多的低级错误之一。有人图省事把手机号、身份证号、银行卡号直接塞进INT或BIGINT结果踩了一堆坑。手机号如果是 11 位INT最大只有 21 亿也就是 10 位数直接存不下BIGINT虽然存得下但开头的 0 会被丢掉比如区号或者某些特殊号码身份证号有 18 位BIGINT也会精度丢失因为超出 19 位后部分语言会转成浮点数。就算字段类型够大这些号码根本不需要参与数值运算导致你每次查询都要忍受类型转换的成本。正确做法是用VARCHAR存这类“看起来像数字但本质是字符串”的字段。同样道理IP 地址如果只在日志里做展示也建议用VARCHAR如果要做高效范围查询可以额外用INT UNSIGNEDINET_ATON()存储但这属于进阶优化不是默认选择。我一直强调类型选型要贴近业务语义。一个字段到底是“数值”还是“编码”决定了它应该用整数类型还是字符串类型。这个判断失误后面再想纠正要付出的成本远超你当初省下的那五分钟。3. 建表和改表类型选型的实操全流程3.1 新表设计一个可以直接抄作业的订单表光讲理论没用我直接给一张可以复用的建表 SQL并解释每个字段为什么这么设计。CREATE TABLE order_info ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键雪花ID或者自增, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号唯一索引, user_id BIGINT NOT NULL COMMENT 用户ID从用户表同步, status TINYINT NOT NULL DEFAULT 0 COMMENT 状态0待支付1已支付2已取消, pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 支付金额单位元, pay_type TINYINT NOT NULL DEFAULT 0 COMMENT 支付方式1微信2支付宝3银行卡, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;逐行拆解id用BIGINT UNSIGNED是因为订单量环境下INT的 42 亿上限并不遥远尤其分布式系统里主键经常需要全局唯一BIGINT才能容纳雪花 ID 等 64 位整数。user_id用BIGINT因为用户量一旦过亿INT无符号上限 42 亿虽然看着够但在分库分表或与其他系统交互时BIGINT更稳妥。status和pay_type用TINYINT因为它们就是状态机和枚举值字段量小用大整数纯属浪费。pay_amount用DECIMAL而不是FLOAT避免金额计算精度丢失这个已经算行业共识了。DEFAULT 0的写法对应很多人搜索的mysql设置默认值为0这个点。业务上很多字段天然有零值语义比如状态为待支付、库存为零、次数为零显式给出DEFAULT 0并且加上NOT NULL能避免 NULL 值带来的模糊性也能让索引更高效。3.2 字段扩容从 INT 改成 BIGINT 的正确打开方式万一一开始用INT做自增主键后面发现快不够用了怎么办最常见做法是ALTER TABLE改列类型。ALTER TABLE order_info MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;看起来就一行但实际操作里有几个关键点第一MODIFY COLUMN会把原列定义里的其他属性也一并覆盖。如果你原来有UNSIGNED、DEFAULT、COMMENT改的时候必须重新写全否则会被重置。第二这个操作会重建整张表在千万级乃至亿级大表上执行可能锁表几十分钟甚至数小时。第三如果有外键约束ALTER可能会因为外键检查而失败需要先评估外键依赖。所以我的建议是小表可以直接ALTER TABLE凌晨低峰期跑一下没问题大表一定要用在线 DDL 工具比如pt-online-schema-change或gh-ost。它们会创建一张影子表把旧数据分批拷贝过去再在切换阶段短暂加锁对业务影响小得多。改完之后记得重建相关索引并检查所有引用这张表主键的关联表关联字段也要同步改成BIGINT。否则一张表的主键是BIGINT另一张表的外键还是INTJOIN 时就会发生隐式类型转换直接导致索引失效性能断崖式下跌。3.3 用系统表快速排查全库不合理的整数类型如果你接手了一个老项目想快速知道哪些表在“乱用类型”不需要手工一张张看。MySQL 的information_schema.COLUMNS表里存了所有字段信息一条 SQL 就能查出全库所有整数类型分布。SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA your_database AND COLUMN_TYPE IN (tinyint, int, bigint, smallint, mediumint) ORDER BY TABLE_NAME, ORDINAL_POSITION;这个结果能帮你一眼发现很多问题。比如有个表里status字段竟然用的是BIGINT明显过度设计还有个表里age用了INT但是年龄最多到 120 岁TINYINT UNSIGNED就足够了。逐个确认后集中出一份改造方案比遇到报错再回头排查要高效得多。也可以用这条 SQL 做容量审计统计每个表里整数类型字段占比找出那些“该用TINYINT却用了BIGINT”的字段从源头上控制数据膨胀。4. 实战中绕不开的类型坑溢出、隐式转换与兼容性4.1 整数溢出与 sql_mode 的相爱相杀整数溢出的报错很简单就一行ERROR 1264 (22003): Out of range value for column status at row 1比如TINYINT字段里插入了 300严格模式下直接报错。但很多人不知道的是这个报错并不是所有环境都会触发。MySQL 有宽松模式和严格模式之分如果在宽松模式下插入超出范围的值不会报错而是被“静默修正”为最大值或最小值然后插入成功。这种静默错误是最危险的——你明明写了 300数据库里却存了 127后面所有统计结果都是错的而且极难排查。因此我强烈建议把sql_mode设置成严格模式至少包含STRICT_TRANS_TABLES。在 MySQL 配置文件里加一行[mysqld] sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION设置完之后用SELECT sql_mode;确认生效。这能保证写入数据时数据库主动拒绝非法值而不是默默帮你“纠正”。另一个容易被忽视的是无符号字段的减法溢出。比如UPDATE stock SET num num - 10 WHERE id 1如果num是INT UNSIGNED且当前值是 3严格模式下会报BIGINT UNSIGNED value is out of range。应对办法是要么把库存字段设计成有符号整数要么在应用层先判断是否足够扣减再执行更新。4.2 隐式类型转换索引失效的头号嫌疑犯每个优化器在比较两个不同数据类型时都会进行隐式类型转换。转换本身不可怕可怕的是转换发生在索引列上导致索引失效。举个例子假设有张用户表user_id是VARCHAR(64)你这条 SQL 看起来没什么问题SELECT * FROM user WHERE user_id 10086;你以为字段值是字符串10086用数字 10086 去查。但 MySQL 会判断你的字段是字符串类型于是把字段列里的每个值都转成数字再和 10086 比较。结果就是索引列上发生了函数操作优化器只能放弃索引走全表扫描。这张表一旦有几百万行查询就会像老牛拉车一样慢。反过来如果字段是INT你传字符串10086MySQL 会把字符串转成数字再和索引列比较这种情况下索引通常还能用。所以最稳妥的习惯是写 SQL 时保持字段类型和查询条件类型一致。WHERE user_id 10086就要确保user_id是字符串WHERE id 10086就要确保id是数字。更隐蔽的隐式转换发生在 JOIN 里。A 表user_id是BIGINTB 表user_id是INT两个字段类型不一致JOIN 时可能全表扫描。这也是为什么前面强调关联字段类型必须一致而且最好都统一成BIGINT一劳永逸。4.3 跨数据库迁移时最容易踩错的小类型现在很多项目要做国产化、上云或者从 Oracle 迁移到 MySQL类型映射是绕不开的坎。很多人直接“一对一搬”结果搬完数据全乱了。给你一张简单的对照表参考MySQLOracleSQL ServerPostgreSQLTINYINTNUMBER(3)TINYINT0~255SMALLINTSMALLINTNUMBER(5)SMALLINTSMALLINTINTNUMBER(10)INTINTEGERBIGINTNUMBER(19)BIGINTBIGINT这里有个特别容易翻车的点SQL Server 的TINYINT范围是0~255不含负数MySQL 默认TINYINT却是-128~127。如果你从 SQL Server 迁移一张“有TINYINT字段值为 200”的表到 MySQL原样建TINYINT字段插入 200 直接溢出。正确做法是先改成TINYINT UNSIGNED或者直接升到SMALLINT。Oracle 那边常见的是NUMBER不指定长度迁移时不要想都不想就转成DECIMAL要根据实际最大值决定用INT还是BIGINT。否则要么空间浪费要么数据溢出。4.4 编程语言映射不对接口数据悄悄变乱类型问题不只发生在数据库内部还会发生在 ORM 映射层。比如 Java 里Long对应 MySQL 的BIGINTInteger对应INTShort对应SMALLINTByte对应TINYINT这个映射如果反了问题就来了。我见过一个真实案例表字段明明是INT实体类却用了Long。日常数据量小看不出问题但某天一条超过 21 亿的 ID 被写入时数据库直接抛错而应用层日志里只看到一堆 500 错误。排查半天才发现是字段类型根本撑不住这个数值。反过来如果表字段是BIGINT实体类却用了IntegerJava 侧会发生精度丢失读取出来的数据可能是负数或者被截断的值这在传输层特别隐蔽。解决方法只有一条ORM 实体类字段类型和数据库字段类型严格对齐别偷懒。5. 资深工程师才会去想的“长期主义”类型设计不只是建表那一下5.1 空间与性能算一道 1000 万行的账很多人觉得多几个字节无所谓但一把账算下来你会发现差距惊人。假设你的表有 1000 万行主键从INT换成BIGINT每行多 4 字节光主键就多 40MB。如果表上有 3 个二级索引二级索引叶子节点会冗余主键值那么这些索引还要额外多 120MB。合计多出 160MB这还没算数据页分裂带来的碎片开销。如果是 10 亿行的大表差距就是 1.6GB 以上。虽然现在磁盘和内存都便宜但 InnoDB 的索引数据是要放进缓冲池的内存里每多 1MB 无用数据能缓存的热数据就少 1MB。所以类型大小的浪费最终会反映到内存命中率和查询延迟上。反过来也不能因为空间紧张就全都用TINYINT。我见过核心业务表为了防止溢出把所有数值字段全部改成BIGINT这虽然避免了溢出但纯属矫枉过正。合理做法是做一个 3~5 年的容量预估算一下每天大概产生多少条记录、每年增长率多少再乘以安全系数 0.7 或 0.8得出的值落在哪个范围就选哪个类型。比如日增订单 10 万一年就是 3650 万三年约 1.1 亿INT无符号完全够用但如果要做分库分表或者分布式 ID那主键就必须BIGINT。5.2 布尔值、状态位、计数器怎么设计最省心说实话市面上很多表设计指南没有把这几个“高频小字段”讲透我补一下个人经验。布尔值字段比如is_deleted、is_active直接用TINYINT(1) NOT NULL DEFAULT 0。注意不要用ENUM(Y,N)因为ENUM底层虽然是整数但变更枚举值需要重建表结构排序和比较规则也特殊维护成本高。状态位字段比如订单状态、支付状态用TINYINT存数字枚举比如 0、1、2。同时要用COMMENT把每个数字的含义写清楚。有人会觉得写注释麻烦但等你半年后回来看表一张没有注释的状态字段表能让你怀疑人生。计数器字段比如浏览量、点赞数、库存。如果业务上不可能是负数用INT UNSIGNED如果有扣减到负数场景用有符号INT并在应用层做校验。特别提醒不要用FLOAT或DOUBLE存计数精度会飘。我自己通常还会为每个表加上create_time、update_time并且用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP管理时间。这样在排查数据问题时至少知道一条数据的生命周期。5.3 一次两亿行大表改类型的复盘最后分享一个真实的全量改造案例。某个订单中心的主表user_id一开始用INT当时估算是 10 年量级没问题结果业务增长远超预期不到 3 年就逼近 5 亿。如果继续用INT再过半年就会溢出。当时有几个可选方案直接ALTER TABLE、用在线 DDL 工具、或者新建表迁移。这张表数据量已经到 2 亿行直接ALTER预计锁表超过 1 小时绝对不可接受。最后我们选了gh-ost。大致流程是预检通过information_schema检查所有外键、触发器、视图确认没有依赖陷阱。全量备份确保出问题能回滚。启动gh-ost创建影子表并开启 binlog 监听。影子表先接收一次全量数据拷贝再持续同步增量数据。等待追平后在流量低峰期执行切换把原表切到影子表。切换完成后重建二级索引并验证主从数据一致性。整个过程耗时大约 40 分钟业务侧只出现了一次极短暂的连接闪断算是把影响控制到了最小。但事后复盘所有人都承认如果当初建表时就用BIGINT这次折腾根本不会发生。这就是类型设计上“长期主义”的价值——它会在你几乎忘掉它的那天替你挡掉一个大坑。如果你现在正在规划一个新项目的库表结构我的建议很朴素主键默认BIGINT UNSIGNED状态和开关用TINYINT常规数值用INT精确数值用DECIMAL时间用DATETIME编码类字符串用VARCHAR。不确定该用哪个时先问自己三个问题这个字段会不会参与运算它的最大值是多少三年后它还会是这个量级吗想清楚这三件事你就能避开数据库设计里最让人头疼的一部分坑了。
