坑你了)
2. 别再用int(11)一个套路走天下了——MySQL与PostgreSQL整数类型选型的那些门道先说我看到的现象很多人建表的时候根本不过脑子主键一律bigint(20)状态字段一律int(11)数量字段也int(11)。问为什么答大家都这么写的。但实际上整数类型这东西在MySQL和PostgreSQL里差别比想象中大得多。有人从MySQL迁到PostgreSQL发现UNSIGNED用不了有人一个SMALLINT状态字段跑了一阵子直接溢出报错还有人以为int(11)是限制长度结果存了一个12位的值进库一脸懵。这篇文章就把两个数据库中整数类型的选择问题从头到尾捋一遍包括各种类型的取值范围、存储代价、自增主键差异、无符号扩展的特殊性以及建表时怎么根据业务估算来选型。无论是新项目建表、老库整改还是跨数据库迁移都希望能帮你少踩几个坑。说实话整数类型选错很少立刻出问题基本都是数据量涨到某个临界点时突然炸掉的。所以这种东西靠临场反应是来不及的只能靠前期规划。1. 为什么要较真整数类型1.1 类型选错不是小事但往往被当成小事我先讲个真实经历。早年在某电商做订单库订单号用的INT当时表里也就几百万行看起来毫无压力。结果某次大促没到订单量直接冲到了20亿附近差点把自增主键干到上限。虽然最后在彻底炸掉之前做了分表但那一周运维和开发都差点没睡好。后来复盘就一句话当时哪怕稍微算一下增长曲线也不至于给自己埋雷。这其实就是整数类型选型的核心问题它不像某个查询慢那样马上能感知到而是种一颗定时炸弹。你以为INT上限21亿多呢可当主键用再遇到高频插入场景这个上限真的没有那么遥远。从存储成本上来说差距更实在。TINYINT占1字节SMALLINT占2字节INT占4字节BIGINT占8字节。单看一两个字段感知不强但如果是千万级、亿级行数的大表多出来的每个字节都是几GB甚至几十GB的存储开销。索引也是同样的逻辑B树每一层能容纳的索引键数量直接受键宽影响。键越宽树越高随机IO就越多查询性能自然下滑。我自己测过同样是千万级表用INT主键比BIGINT主键的单行查询快那么一丢丢虽然不明显但写入时的缓存友好度和页面分裂频率还是有差别的。所以随便选一个整数类型这个操作长期看一定是亏的。要么你在存储和性能上吃亏要么你在某天晚上收到报警然后半夜起来扩容。1.2 MySQL和PostgreSQL在整数设计上的思路差异先列个总览MySQL的整数类型有六种TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT还有基于它们的UNSIGNED变体。PostgreSQL就三种SMALLINT、INTEGER、BIGINT。这种数量差异本身就体现了两边设计哲学的不同。MySQL出身于互联网场景讲究灵活愿意给开发者更多精细化的选择甚至提供MEDIUMINT这种别的数据库不太常见的3字节类型而PostgreSQL更偏向SQL标准主要保证一致性和功能完整类型少但每一个都比较正统。我在实际使用中觉得这两者没有绝对优劣关键是你得知道自己在用哪种别把MySQL的惯性思维直接搬到PostgreSQL上。有一个特别重要的历史包袱需要单独提一下MySQL的int(11)。很多人以为括号里的11代表字段最大长度超过11位的数字就存不进去。这是天大的误解。那个数字只是显示宽度跟存储上限一点关系没有而且从MySQL 8.0.17开始这种显示宽度语法已经被废弃了。也就是说INT就是4字节不管括号里写几存储范围和性能完全一样。PostgreSQL里干脆没有这个写法你写INTEGER就是INTEGER干干净净。2. MySQL与PostgreSQL的整数类型全景2.1 全家桶对比存储大小、取值范围与适用场景我整理了一张表把两边常用的整数类型放在一起对比。类型MySQL有符号范围MySQL无符号范围PostgreSQL范围存储大小典型场景TINYINT-128 ~ 1270 ~ 255无1字节状态码、开关标志、短枚举SMALLINT-32768 ~ 327670 ~ 65535-32768 ~ 327672字节较小计数器、枚举值、省份编码MEDIUMINT-8388608 ~ 83886070 ~ 16777215无3字节中等范围的序号、消息数INT / INTEGER-2147483648 ~ 21474836470 ~ 4294967295-2147483648 ~ 21474836474字节主键小规模、常规业务编号、外键BIGINT-9223372036854775808 ~ 92233720368547758070 ~ 18446744073709551615-9223372036854775808 ~ 92233720368547758078字节大表主键、流水号、金额分、毫秒时间戳从这张表能看出几个关键信息。第一MySQL有UNSIGNED扩展可以把负取值范围让给正数从而把上限翻倍。比如INT UNSIGNED能存到42亿左右而PostgreSQL没有任何无符号类型。你从MySQL往PostgreSQL迁移如果用了UNSIGNED字段就势必要改成BIGINT或者靠CHECK约束模拟非负条件。否则直接降级成有符号INTEGER原来的数据超过21亿就会溢出。第二MEDIUMINT是MySQL独有。这个类型我其实不太推荐存关键业务数据因为3字节的存储让它在空间上比较讨巧但数据库工具链、ORM框架对它的支持普遍不如INT和BIGINT到位。实话说有些驱动返回结果集时对MEDIUMINT的处理比较怪异遇到一次你会记很久。第三PostgreSQL的INTEGER就是标准4字节整数而MySQL的INT也是4字节取值范围完全一样。所以如果两边都用有符号类型的对应档位数据迁移基本无障碍。差异主要在于MySQL多出来的那几种变体。这里再说一个容易被忽略的点MySQL的整数类型文档里还有一个内部别名比如INT4、INT8这是PostgreSQL里的叫法。两边都支持这种简短写法但我在规范里一律禁止使用因为可读性太差代码评审时还容易引起误会。2.2 显示宽度、零填充与无符号的真相MySQL的显示宽度问题值得单独拿出来讲。INT(11)里的11在5.7及之前版本的唯一作用是配合ZEROFILL属性比如你声明INT(5) ZEROFILL存一个42进去查询出来显示的是00042面子上好看但存储的其实还是整数42。如果你没有用ZEROFILL那么这个宽度定义对实际存储和查询完全没有任何影响。很多人踩坑是这样的建表时写INT(2)想当然地认为这个字段最多只能存两位数结果业务数据到了三位数也能正常写入。等到上线后有人问为什么超过范围还能写进去才意识到括号里的数字根本不是限制。在8.0中这个语法虽然不再推荐但老库的老表定义还在看到这种字段定义要心里有数。ZEROFILL本身还有一个副作用在MySQL 5.7及之前定义了ZEROFILL的整数列会被自动加上UNSIGNED属性。意味着原本想存负数的小字段一旦加了ZEROFILL就连负数都存不了了。我之前接过一个工单开发反馈某个字段之前能写负数后来改了显示格式之后写不进去了折腾半天才发现是这个自动加UNSIGNED的机制在作怪。无符号整数在单库里用着还行但一旦涉及跨库迁移、数据导出、或者应用层ORM跟数据库类型映射就容易出幺蛾子。比如某些ORM框架在映射INT UNSIGNED时会对应到编程语言里一个不存在的无符号整型于是只能向上映射到长整型一旦应用层计算时忘记做类型转换就可能出现负数。所以我的个人意见是新项目里尽量不用UNSIGNED宁可把字段定义为BIGINT也不要为了省那一点存储然后给自己挖跨系统兼容性的坑。对BIGINT的空间代价更大但你省下来的那点空间不值得在未来某次系统对接时用几个小时去排查一个隐晦的类型问题。2.3 自增与序列AUTO_INCREMENT、SERIAL和IDENTITY这一块是MySQL和PostgreSQL差异最大、也最值得详细说的地方。MySQL的自增主键用的是AUTO_INCREMENT列属性。一个表最多只能有一个AUTO_INCREMENT列它必须是索引的一部分而且通常是主键。它的工作机制是每次插入时如果该列没有显式赋值就在当前最大值基础上加1。注意MySQL不保证自增值严格连续中间可能有空洞比如事务回滚或者删除记录之后再插入的行并不会复用已经删除的编号。MySQL 8.0把自增值的持久化做进了redo log里重启之后不会再像5.7那样出现内存中自增值丢失、重启后可能复用之前删除的最大值的问题但也仅仅是保证单调递增不代表紧凑无洞。PostgreSQL这里有两种流派。老派写法是SERIAL伪类型建表时写id SERIAL PRIMARY KEY系统会自动创建一个名为表名_id_seq的序列对象底层用这个序列来生成值。这种写法看起来方便但有一个隐蔽问题SERIAL产生的列其实还是普通的INTEGER序列本身并不知道这个列的容量边界。如果序列的当前值已经超过INTEGER的上限插入时就会报integer out of range之类的错误但序列本身还在自增。等到你把这个列改成BIGINT之后序列的当前值可能早就跑飞了还得手动调序列。新写的代码里我更推荐用SQL标准的GENERATED AS IDENTITY声明。两种写法在PostgreSQL里的主要区别在于对手动插入值的控制。GENERATED ALWAYS AS IDENTITY默认不允许你显式往该列插入值必须由系统生成除非临时用OVERRIDING SYSTEM VALUE关键字强制插入而GENERATED BY DEFAULT AS IDENTITY则允许你显式指定值适合数据迁移时保留原始ID的诉求。从数据一致性的角度我比较倾向于ALWAYS因为主键这种核心字段就应该由数据库统一管理而不是让应用层随意指定。另外PostgreSQL的序列是独立对象这既是优点也是麻烦。优点在于你可以多张表共用一个序列甚至可以往不同表的同一个序列下游取值麻烦在于序列操作和数据表操作是两次独立操作一旦表结构被复制、迁移、恢复就容易出现序列值滞后于表中现有最大值的情况。我遇到过不止一次恢复备份后往表里插入数据时立刻撞上主键冲突排查下来就是序列没跟着数据走。而MySQL的AUTO_INCREMENT是跟着表走的备份恢复之后基本不会出现这种问题。3. 动手选型从日常建表到老库整改3.1 业务容量估算别凭感觉用简单算数说话选整数类型的核心动作之一是估算目标字段在未来若干年内的最大值。这件事不需要很复杂的模型一道乘法题就够了。假设你要设计一张用户行为日志表预估上线首年每天新增100万行业务增长导致第二年开始每年翻倍。那三年后的日增就是400万行三年总行数大约是100万×365 200万×365 400万×365 约2.55亿行这个量级用INT上限21亿其实还够。但如果你规划的是五年、十年或者更高增长的业务那还是直接上BIGINT。主键ID被大量外键引用、被缓存系统作为key使用一旦类型容量不够更换的代价是整个链路都要跟着改那种痛苦用过一次就有心理阴影。所以我个人的原则是核心实体主键一律BIGINT不管当前看起来多么杀鸡用牛刀。如果你觉得这个估算太保守可以再加20%的缓冲因为业务增长曲线往往超过预期。公式很简单预估最大行数 当前数据量 × (1 年增长率) ^ 年数 × 1.2把计算出来的数值跟在2.1那张表里的范围对一下直接选第一档满足条件的类型就行。当然不同的字段用途有不同的选型逻辑不只是主键一种场景。我总结了一个快速决策表字段用途推荐类型理由状态/标志位0、1、2TINYINTMySQL或SMALLINTPG能表示几十个状态未来加枚举也够用枚举值、分类IDSMALLINT2字节上限65535绝大多数分类远用不到中小业务外键INT21亿上限通常够用索引体积比BIGINT小一半核心业务主键BIGINT不赌未来8字节体积换长期省心流水号、全局唯一IDBIGINT可能被多系统拼拼凑凑做唯一值必须留足余量金额计算BIGINT以分为单位避免浮点误差且分单位在BIGINT范围内完全无压力秒级时间戳INT目前在2038年1月19日前都能存毫秒级/微秒级时间戳BIGINT毫秒时间戳已经超过INT存储上限别犹豫时间戳这个场景相当典型。32位系统的2038年问题大家多少听过数据库里同样存在。如果你在MySQL里用INT存储秒级时间戳那么在2038年1月19日凌晨3点14分07秒之后这个字段就会溢出。别觉得还远有些系统数据要继续存很多年到时候改类型是一件牵一发动全身的事情。如果你的字段需要存毫秒级时间戳那INT连现在都撑不过去因为当前毫秒时间戳已经接近17亿而且还在持续增长。3.2 建表语句逐段对比同一种需求两边写法不同我直接拿一个订单表来做对照把整数类型相关的写法都体现出来。MySQL版CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, shop_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0, total_amount_cents INT UNSIGNED NOT NULL DEFAULT 0, buyer_uid BIGINT NOT NULL, created_at_ms BIGINT NOT NULL, PRIMARY KEY (id), KEY idx_shop_id (shop_id), KEY idx_buyer_uid (buyer_uid) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;PostgreSQL版CREATE TABLE orders ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, shop_id INTEGER NOT NULL, status SMALLINT NOT NULL DEFAULT 0, total_amount_cents BIGINT NOT NULL DEFAULT 0, buyer_uid BIGINT NOT NULL, created_at_ms BIGINT NOT NULL ); CREATE INDEX idx_shop_id ON orders (shop_id); CREATE INDEX idx_buyer_uid ON orders (buyer_uid);上面的SQL里有几个细节是我在实际业务里总结出来的MySQL的主键带了UNSIGNEDPostgreSQL这边我其实不建议模仿因为PG没有无符号BIGINT本身就是足够大。MySQL的INT UNSIGNED最多能到42亿但PostgreSQL的INTEGER只有21亿如果同样按金额分为单位来算21亿分是2100万元很多电商一天的流水就可能超过这个数所以我直接用了BIGINT。这也回答了前面说的跨数据库迁移时UNSIGNED引发的隐式变化非常容易漏掉。status字段我这里用TINYINT和SMALLINT分别对应两边各自的日常写法。其实这类字段你完全可以用INT但没那个必要一个字节能装下的状态枚举用四个字节去存纯粹是浪费空间和索引宽度。我见过一些表动辄几十个TINYINT字段分别表示不同状态那索引、缓存、存储全都跟着变大数据库层的成本被白白抬高。3.3 老库里的定时炸弹怎么排查和整改如果你接手的项目是一个老系统不可能把所有表都推倒重来但至少要定期摸一下库里的整数类型分布看看有没有明显不合理的地方。最直接的办法是查information_schema.columns。MySQL下可以这样查SELECT table_schema AS db_name, table_name, column_name, column_type, data_type, numeric_precision, character_maximum_length FROM information_schema.columns WHERE table_schema NOT IN (mysql, information_schema, performance_schema, sys) AND data_type IN (tinyint, smallint, mediumint, int, bigint) ORDER BY table_schema, table_name, column_name;把结果导出之后重点看两类字段一是主键二是名称里带着count、num、amount、status、type这类关键字的字段。主键如果还在用INT但表行数已经超过几千万就应该考虑提前升级到BIGINT。状态和计数类字段如果用的是BIGINT但实际取值范围只有0到5那么该改成TINYINT/SMALLINT节省空间。整改的SQL并不复杂。MySQL改列类型用MODIFY COLUMNALTER TABLE orders MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;PostgreSQL换类型用ALTER COLUMN TYPEALTER TABLE orders ALTER COLUMN id TYPE BIGINT;但这里有一个非常现实的坑对大表做这种改动时MySQL的MODIFY COLUMN通常需要重建表期间会有锁表风险PostgreSQL的ALTER TYPE在某些情况下也需要全表重写。所以这种操作不能直接在生产环境上莽至少要按下面几步来走先在测试库用和生产一致的数据量验证耗时生产操作放在业务低峰期提前通知相关方大表优先用在线改表工具如社区常见的pt-online-schema-change这类思路把改动做成增量复制或者用新建临时表、双写、切换的方式改完后立刻用information_schema确认新类型已生效并抽查几条边界数据。还有一个小技巧如果只改一个字段的类型认定很安全可以先在知道当前表最大值的前提下用一条SQL验证边界值是否会溢出SELECT MAX(id) FROM orders;如果已经是21亿上下那改成BIGINT之前还要顺便确认你有没有分库分表、有没有多套环境共用一套自增偏移配置否则可能出现改完之后自增冲突之类的新问题。4. 常见事故与排查指南4.1 溢出的千层套路Out of range绝不是唯一的炸法我先列一下我实际遇到过的几种整数类型相关事故每个都是真实发生过的事情只是项目细节我模糊处理了。第一种是最直白的写入报错。字段是TINYINT某天业务加了第128个状态值insert直接报Out of range value for column。处理倒不难把字段改成SMALLINT就行但问题是报警发生在业务正常迭代的过程中新功能直接不可用上线窗口被硬生生推后。后来我们团队里的约定是状态类字段至少SMALLINT起步除非你能拍着胸脯保证未来永远不会增加位数。第二种是主键自增撞到天花板。这种最阴间因为MySQL的自增跳号本来就常见等到主键用完时插入怎么都失败主键冲突和Out of range两种报错反复横跳。排查的时候一看MAX(id)已经接近21亿人差点跟着心跳一起归零。这种场景只能通过分表、换新表或者重建ID生成策略来解过程极其痛苦。唯一的补救机会就在设计阶段。第三种是PostgreSQL的序列回卷。SMALLSERIAL的上限是32767有些系统图省事拿SMALLSERIAL做业务表主键跑了一段时间后插入时提示nextval: reached maximum value of sequence。这个错误信息比MySQL友好一点它清楚地告诉你序列到顶了但修复起来要同时处理序列和表结构。我一般遇到这种就直接改成IDENTITY并配合BIGINT一劳永逸。第四种是隐式类型转换导致索引失效。这个跟选错类型没关系而是字段类型和查询条件类型不匹配。比如一个用户ID字段在表里是INT但应用层传过来的参数是字符串或者字段本身被定义成了VARCHAR但里面存的全是数字。当你执行WHERE user_id 123456时MySQL可能会尝试把字符串转换成数字再比较如果字段上建了索引因为做了类型转换通常就没法走索引了。排查时用EXPLAIN看执行计划如果type列从ref变成ALL同时Extra里出现Using where那多半就是类型转换干的好事。解法很简单统一应用层传入参数的类型或者把字段本身改成数值类型别让数据库在暗地里做转换。4.2 快速排查速查表我把几种常见的状况整理成一张速查表可以直接截图放到团队文档里。症状可能原因快速处理方案插入时Out of range value for column字段类型上限小于写入值ALTER TABLE改大一级类型先在低峰期验证主键冲突持续出现自增序列/自增值落后于表内最大值先SELECT MAX(id)再调整序列/自增值到最大值之上插入数值为负时报错字段是UNSIGNED但传入负数确认业务是否需要负数若需要则改回有符号类型nextval: reached maximum value of sequencePostgreSQL序列达到当前类型上限修改序列类型并提升上限或更换为BIGINT IDENTITY查询很慢EXPLAIN显示全表扫描等值条件字段类型不匹配、隐式转换统一查询参数类型若字段本身类型选错了改字段类型数字字段显示被截断成0ZEROFILL把值包装成显示字符串去掉ZEROFILL必要时去掉UNSIGNED同时在应用层重新处理格式这张表里的每一行都是我处理线上问题后整理过的话术。实际情况中很多问题并不同时出现而是逐层叠加的。比如先遇到隐式转换修好之后又暴露出主键容量不足。所以我总建议搞运维和搞开发的同学在排查问题时别只盯着最表面的报错要顺着数据类型、索引、执行计划三个方向一起查。4.3 避免踩坑的团队约定关于整数选型我最终发现单靠个人自觉不可靠必须落到团队规范上。这里分享几条我们内部一直在执行的规矩。第一新表的字段评审必须包含容量评估。哪怕是实习生提的方案也需要写清楚这个字段的预期范围是多大、按年增长后多久会到上限。写不出来就说明没动脑子退回重写。第二凡是可以穷举的字段状态、类型、渠道、来源一律用短整数类型不允许直接INT起步。这里不是抠那点空间而是用类型本身约束代码的可读性让人一看字段就知道它代表一类穷举值。第三主键统一用BIGINT不要问为什么。不同团队如果有的用INT、有的用BIGINT将来一旦要做数据合并、跨库关联类型不匹配带来的转换损耗和隐式转换风险会突然冒出来。第四禁止在核心业务表里使用UNSIGNED。理由前面说过ORM映射、跨库迁移、数据导入导出都可能因为无符号而出现类型对不上的情况。PostgreSQL完全没有UNSIGNED保持两边类型语义一致可以减少大量迁移成本。第五时间戳统一用BIGINT存毫秒。如果老系统已经用INT存了秒级时间戳那么最晚在2038年之前要安排一次升级别拖到最后一年才想改。5. 写在最后的实操心得数字类型这个问题在教科书里一般只有几页纸但现实项目里能引申出这么多细节是因为它跟业务增长、存储成本、索引性能、跨系统兼容性全部挂钩。手动选类型这个习惯有则改之无则加勉。我个人的习惯是三个一定核心主键一定要给BIGINT计数和时间戳一定要留足余量枚举和状态字段一定要往小里压。用的时候可以稍微极端一点但遇到边界情况宁可往大选一档也不要为了省两字节赌业务不会增长。最后再分享一个小技巧。每次大版本迭代之前跑一遍4.3节里那个information_schema查询把整数类型清单导出来扫一眼重点看有没有哪张表的主键离上限只剩不到30%的余量。如果发现某个字段已经超过了类型上限的七成提前一个月安排整改绝对比线上炸掉之后再抢救要舒服得多。类型这件事看着小但早晚会以你想不到的方式来找你早点管住它不亏。
