搞数据库的人十有八九都遇到过这种场景建表时图省事顺手写了个INT看着挺正常结果业务量上来之后主键突然撞到天花板或者磁盘空间莫名暴涨。选整数类型这件事在 MySQL 和 PostgreSQL 里看起来就是INT、BIGINT几个关键词的差别但真正踩过坑之后才会明白这几个字母背后藏着的是存储成本、性能边界、业务容量和迁移兼容性的一整套账。这篇文章就从实际选型的角度把两个数据库里整数类型的差异、主键与自增场景的取舍、时间戳/金额/状态码/位运算这类高频场景的决策思路以及真实线上问题的排查方法完整梳理一遍。不管你是刚接触数据库的新人还是已经在生产环境里维护了几年表的负责人这份内容都能帮你少走弯路。1. 为什么整数类型的选择值得认真对待1.1 存储空间与磁盘IO的真实成本很多人觉得整数类型选大一点没关系最多就是“多几个字节”。但你要把“几个字节”放到千万行、上亿行的表里去算就不是小数目了。以 MySQL InnoDB 为例TINYINT 占 1 字节SMALLINT 占 2 字节MEDIUMINT 占 3 字节INT 占 4 字节BIGINT 占 8 字节。假设一张订单表有 1 亿行一个状态字段本来用 SMALLINT2字节后来因为要兼容更多状态改成 INT4字节每行多 2 字节光这一列就多了 200MB 的存储。如果这一列上还有索引那索引也要多占空间InnoDB 从磁盘加载数据页时能做有效工作的数据量也会降低扫描和回表时的 IO 成本都被放大了。PostgreSQL 的存储机制和 MySQL 有差异但“大类型占空间更多”的基本逻辑是一样的。PG 的堆表不会把主键复制到每个二级索引里但索引本身仍然保存键值所以主键选 BIGINT 而不是 INT所有索引的叶子节点都会变大缓存命中率和写入放大一样受影响。换句话说选整数类型不是“够用就行”这么简单而是要综合看数据量、索引数量、查询频率和写入模式提前算清楚这笔存储账。我的习惯是在需求评审阶段就画一张“每行字节增量 × 预计行数”的估算表别等到表大了再回头改。1.2 类型溢出引发的线上事故整数溢出的问题不是理论上的“可能性”而是真实发生过的生产事故。最经典的场景就是自增主键达到上限。INT 有符号的最大值是 2,147,483,647大约 21 亿。对一个订单系统来说如果上线初期数据量不大用 INT 存订单 ID看起来没问题但一旦遇到大促、刷单、异常重试订单号生成速度会远超预期。主键到达上限之后MySQL 插入数据会直接报Out of range value for column这条 SQL 失败应用层如果没有做好重试隔离很快会把数据库连接池打满最终整个服务雪崩。有符号 INT 的 21 亿上限之外还要注意 MySQL 的 TIMESTAMP 也有 2038 年问题。如果当初为了省空间或者为了跨平台兼容用 INT 存 Unix 时间戳秒级那么 2038 年 1 月 19 日凌晨 3 点 14 分 07 秒之后32 位有符号秒数就会溢出。到这个时间点基于 INT 时间戳的排序、比较、计算全部会出问题。虽然这个时间看起来还很远但业务系统可能要运行几十年没有任何理由为一个省 4 字节的决定埋这么一颗雷。哪怕不是主键只要字段的实际值可能逼近类型上限就应该在前期选更大的类型。1.3 跨数据库迁移时的兼容性陷阱MySQL 和 PostgreSQL 的整数类型并不是一一对应的。MySQL 有 TINYINT、MEDIUMINTPostgreSQL 没有PostgreSQL 有 SMALLINT、INTEGER也就是 INT、BIGINT但不支持无符号整数。很多迁移工具会把 MySQL 的TINYINT(1)映射成 PostgreSQL 的 BOOLEAN因为 MySQL 里很多人拿 TINYINT(1) 存 0 和 1 表示布尔值。如果你的业务里TINYINT(1)其实存的是“类型编号”比如 0/1/2/3那么迁移到 PostgreSQL 后语义就完全变了应用层如果把它当成 BOOLEAN 处理会静默地产生错误结果。另外MySQL 里的INT(11)这种带显示宽度的写法在 PostgreSQL 中不存在。INT(11)并不限制存储范围纯粹是旧客户端展示用但很多开发者在看建表语句时会误以为它能限制数值位数。迁移时这类误解会导致校验逻辑写错比如把 MySQL 的INT(10) UNSIGNED当成只能存 10 位以内的数实际上它的最大值是 42 亿远超 10 位数的范围。跨库迁移前一定要先跑一个“类型映射差异清单”把每个整数列的原始类型、实际取值范围、是否有无符号属性、是否承担布尔语义都列出来再决定目标库里用什么类型。2. MySQL与PostgreSQL整数类型全景对比2.1 内置整数类型一览TINYINT到BIGINT把两边的基础整数类型放在一张表里看最直观类型MySQL 字节数MySQL 有符号范围MySQL 无符号范围PostgreSQL 对应情况TINYINT1-128 ~ 1270 ~ 255无对应类型可用 SMALLINTSMALLINT2-32768 ~ 327670 ~ 65535SMALLINT范围一致2字节MEDIUMINT3-8388608 ~ 83886070 ~ 16777215无对应类型可用 INTEGERINT / INTEGER4-2147483648 ~ 21474836470 ~ 4294967295INTEGER / INT范围一致4字节BIGINT8-2^63 ~ 2^63-10 ~ 2^64-1BIGINT范围一致8字节PostgreSQL 缺了 TINYINT 和 MEDIUMINT很多人一开始不习惯。实际上 PostgreSQL 的设计哲学是尽量向 SQL 标准靠拢类型种类不必刻意追求“最小化”。在 PG 里如果你需要一个只存 0-255 的字段通常直接用 SMALLINT 就够了反正 2 字节和 1 字节的差距微乎其微。MySQL 保留 TINYINT 和小型类型是为了精细化控制行大小让 InnoDB 页能容纳更多行。所以两个数据库的整数选型策略本质上是不同的MySQL 可以在“够用”的前提下尽量省PG 则更重视类型语义的清晰和跨数据库一致性。2.2 无符号与有符号MySQL的独特性MySQL 允许给整数类型加UNSIGNED属性这是 PostgreSQL 完全没有的特性。无符号最大的价值在于用同样的 4 字节把范围从有符号的 21 亿扩展到 42 亿。所以很多 MySQL 老项目的主键喜欢用INT UNSIGNED本质上是想延长 INT 的寿命又不想升级成 BIGINT 多花 4 字节。这个思路本身没错但它引入了不少隐含问题。首先如果主键是INT UNSIGNED所有外键关联列也必须定义成INT UNSIGNED否则 MySQL 会报错或产生隐式转换导致索引失效。其次无符号整数做减法时非常容易踩坑。比如执行SELECT id - 1 FROM t WHERE id 0如果 id 是无符号类型结果应该是 -1但无符号类型根本存不了负数MySQL 会直接报BIGINT UNSIGNED value is out of range。这种错误通常只会在真实数据触达边界时出现简直防不胜防。我的实践建议是如果确实需要 4 字节能存到 42 亿那就明确评估无符号带来的限制如果业务可能超过 21 亿但不太可能超过 42 亿用INT UNSIGNED也可以如果超过 42 亿只是时间问题直接上 BIGINT别折腾。2.3 PostgreSQL的SERIAL、BIGSERIAL与IDENTITYPostgreSQL 没有 AUTO_INCREMENT 关键字传统做法是用SERIAL、BIGSERIAL这类伪类型。所谓伪类型是因为SERIAL并不是一种真正的存储类型而是INTEGER加一个默认的序列sequence组合出来的语法糖。比如id SERIAL PRIMARY KEY实际展开后是id INTEGER NOT NULL DEFAULT nextval(table_id_seq)同时创建一个隐式的序列。BIGSERIAL对应BIGINT。现代 PG 项目我更推荐使用GENERATED BY DEFAULT AS IDENTITY或GENERATED ALWAYS AS IDENTITY这是 SQL 标准的写法比 SERIAL 更规范。区别在于ALWAYS模式下应用层不能显式插入该列的值必须让数据库生成这能避免很多“手动插入后序列不同步”的问题BY DEFAULT则允许应用层指定值灵活性更高但需要你自己维护序列。如果你的序列是 INTEGER 类型并且当前值已经接近 21 亿那么除了把表字段改成 BIGINT还必须把序列本身也改成 BIGINTALTER SEQUENCE table_id_seq AS BIGINT。只改列不改序列下一个nextval仍然可能在序列内部溢出这是 PG 迁移中特别容易漏掉的一步。2.4 类型别名和标准兼容性差异两个数据库在别名上有一些容易混淆的点。MySQL 中INTEGER是INT的别名BOOL/BOOLEAN是TINYINT(1)的别名PostgreSQL 中INT是INTEGER的别名BOOLEAN是真正的布尔类型。MySQL 的SERIAL伪类型实际上是BIGINT UNSIGNED AUTO_INCREMENT UNIQUE而 PostgreSQL 的SERIAL只是INTEGER 序列。如果你在 MySQL 和 PG 之间同步表结构直接复制 DDL 一定会出错。还有一个坑是 MySQL 的INT(11)。从 MySQL 8.0 开始INT 的显示宽度已经被废弃但很多旧建模工具生成的语句还是带括号数字。这个括号和存储范围没有任何关系不要试图用INT(4)来限制数字位数。如果要做跨库兼容更安全的做法是在应用层定义统一的“类型映射标准”比如 TINYINT 统一映射为 PG 的 SMALLINTTINYINT(1) 需要单独判断是否代表布尔INT(10) UNSIGNED 映射为 PG 的 BIGINT因为 PG 没有无符号 INT直接映射成 INT 会有溢出风险然后在迁移核对清单里逐项打勾。3. 主键与自增场景下的选型实操3.1 主键类型INT还是BIGINT的经典纠结主键选型是整数类型决策里最重要的一环因为主键不仅影响行存储还影响所有二级索引。以 MySQL InnoDB 为例InnoDB 是聚簇索引表主键值会复制到每一个二级索引的叶子节点中。如果表上有 5 个二级索引主键从 INT 变成 BIGINT每行在索引中增加的额外开销就不只是 4 字节而是 4 × (1 5) 24 字节。1 亿行的表相当于多出 2.4GB 的索引空间这还没有计算 B 树分裂和页填充带来的放大。PostgreSQL 堆表不会把主键复制到每个索引但索引键值本身变大的影响依然存在。那什么时候用 INT什么时候用 BIGINT我的判断标准很简单看这个表在业务生命周期内会不会超过“千万到亿”这个量级。普通配置表、字典表、用户留言表用 INT 足够订单流水、操作日志、消息记录、埋点数据这种高速增长的中间表直接用 BIGINT。不要指望“先 INT 后改 BIGINT”因为大表改主键类型的代价极高期间可能涉及锁表、重建索引、校验外键很多团队根本不敢在线上做。前期多花 4 字节后期能省掉一次大迁移这笔账很划算。3.2 MySQL AUTO_INCREMENT与PostgreSQL序列的核心差异MySQL 的AUTO_INCREMENT是表级属性规则比较特殊。第一自增列必须被定义为某个索引的一部分通常直接设为主键。第二事务回滚不会回退自增值也就是说你插入一条数据后回滚了这个自增 ID 就永久浪费了。第三MySQL 8.0 之前自增值保存在内存里重启后可能复用之前已经分配过的最大值导致插入冲突8.0 开始 InnoDB 会把自增值持久化到 redo log解决了这个重启回退问题。如果还在维护 MySQL 5.7 以下版本要注意这个行为差异。PostgreSQL 的序列机制更独立。序列是数据库对象nextval每次调用都会递增不管事务是否提交。默认情况下序列也不会回退所以同样存在“空洞”和“跳号”。PG 支持序列缓存比如CREATE SEQUENCE ... CACHE 100应用端会一次性拿 100 个号到本地数据库重启或会话异常退出时这些号会丢失产生更大空洞。如果业务对编号连续有强要求PostgreSQL 的自增机制天然不满足需要自己实现“连续号段生成器”。无论 MySQL 还是 PG我的建议都是不要把主键 ID 当成业务连续编号来使用它有空洞才是正常状态。3.3 分布式与分表场景下的整数主键困境分库分表之后单表单库的自增就没法用了。多个节点同时插入如果不做特殊处理会出现主键冲突。行业里常见的做法有三种一是设定不同节点的初始值和步长比如 4 个分片节点 A 的初始值是 1步长是 4节点 B 的初始值是 2步长是 4这样每个节点生成的 ID 取模分片数后稳定落到某个区间但缺点是后续扩容分片时步长调整非常麻烦。二是使用中心化的号段服务应用每次从号段服务申请一批 ID用完再申请这样能保证全局唯一也方便分片路由。三是使用雪花算法Snowflake之类的分布式 ID 算法生成的 ID 是 64 位整数带有时间戳、机器编号、序列号等信息。这三种方案生成的 ID 都远超 INT 的容量。雪花算法是 64 位落在 BIGINT 范围内所以分布式系统的业务主键必须用 BIGINT。你可能会想能不能把雪花 ID 截断成 INT绝对不要那会破坏唯一性和时间前缀。即使做分表分库数据库表里的主键类型也要预留足够的容量。MySQL 里直接定义BIGINTPG 里定义BIGINT或BIGSERIAL然后应用层在插入前给主键显式赋值不使用数据库自增。3.4 剩余主键容量估算方法既然担心主键溢出就不要等到报错再处理应该把“主键余量”纳入日常巡检。估算逻辑很简单先确认当前自增值或当前最大值再算出剩余可用值最后除以预估日增行数得出大概剩余天数。MySQL 中查询当前自增值可以用SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db AND TABLE_NAME orders;PostgreSQL 中查询序列当前值SELECT last_value FROM orders_id_seq;更准确的是直接查表里的实际最大值因为序列可能落后于实际数据比如手动插入过更大值或者自增值已被消耗但实际数据回滚删除SELECT MAX(id) FROM orders;建议写一个监控脚本每天对关键大表跑一次最大值扫描计算当前类型上限与最大值的差值并生成一个“剩余可用天数”指标。当剩余天数低于警戒线比如 180 天时自动告警提醒 DBA 开始规划 BIGINT 迁移。这个方法比看自增值可靠得多因为它是基于真实数据分布而不是序列的计数。4. 其他核心场景的整数选型决策4.1 状态码、枚举与布尔替代场景业务表里最常见的整数用途之一就是状态码。比如订单状态待支付、已支付、已发货、已完成、已取消分别用 0、1、2、3、4 表示。在 MySQL 中状态码字段用 TINYINT 就够因为状态不会超过 127 个。但如果状态码包含“负数表示异常类状态”的设计那就要选有符号类型并且注意默认值。在 PostgreSQL 中没有 TINYINT最小整数是 SMALLINT状态码用 SMALLINT 也很合适。很多人会把“是否删除”“是否生效”这类布尔语义也用整数存。MySQL 里最常见的做法是TINYINT(1)注意(1)只是显示宽度并不是“只能存 1”。如果你在应用层写死了只传 0 和 1没问题但如果某天有人传了 2数据库不会拒绝这就会造成数据歧义。更好的做法是加上CHECK (status IN (0,1))约束或者在应用层用枚举严格限制。PostgreSQL 直接使用原生BOOLEAN类型存储空间也是 1 字节语义比整数清晰得多强烈建议 PG 环境不要再用 SMALLINT 模拟布尔值。4.2 时间戳存储INT/BIGINT还是TIMESTAMP把时间存成整数在很多旧系统和跨语言接口里很常见。早期的做法是用 INT 存 Unix 时间戳秒级因为 4 字节比多数数据库的时间类型更省空间。但前面说过INT 存秒级时间戳会在 2038 年溢出。如果项目生命周期不短这个风险不能忽视。如果用 INT 存毫秒级时间戳问题更严重因为 INT 最大值 21 亿毫秒只相当于 24.8 天第一天就溢出了。所以毫秒级时间戳至少要用 BIGINT 存。现代数据库的时间类型已经很成熟MySQL 的 DATETIME 在 5.6 之后存储优化为 5 字节TIMESTAMP 占 4 字节但范围只到 2038 年PostgreSQL 的 TIMESTAMP / TIMESTAMPTZ 占 8 字节范围远超 32 位整数。我的建议是如果只是存储“业务时刻”直接用原生时间类型如果确实需要整数形式做跨系统协议对接或者要把时间嵌入到某个复合业务编号里那么毫秒级用 BIGINT秒级用 BIGINT 也完全可以别为了省 4 字节给自己挖坑。在 PG 中整数时间戳和原生时间类型都能用to_timestamp和EXTRACT互相转换从检索和排序看差异不大但从可读性、时区处理、内置日期函数上原生类型更省心。4.3 金额与精确计数的整数用法数据库里的金额字段教科书推荐用DECIMAL或NUMERIC这没错。但到了高并发交易系统里很多团队为了性能和避免精度歧义会把金额全部换算成“最小货币单位”存整数。比如人民币按“分”存美元按“cent”存一些超高精度场景按“厘”或“微单位”存。这种方案下的金额字段是 BIGINT而不是 INT原因很简单一个交易流水表累计交易金额可能很快超过 21 亿元如果按“分”存INT 最大只能表示 2147 万元左右21 亿分完全不够看用 BIGINT 存“分”最大约 9.22 亿亿元级别货币总量都不可能超过它。整数不是只用来做状态和时间还有很多“计数”语义的字段库存、余额、下载量、点赞数、积分等。这类高频更新的字段优先考虑 BIGINT 还是 INT取决于这个数值是否可能超过 21 亿。比如单条视频的点赞数INT 往往够用但如果是一个平台的累计播放总量就必须 BIGINT。尤其要注意 MySQL 的UPDATE counter SET value value 1 WHERE id ?这种指令本身没有类型问题真正的瓶颈是行锁竞争。不要为了省空间把计数器定义成INT UNSIGNED还忘记在应用层处理溢出最后加出一个负数或报错。4.4 位运算与标志位设计整数还有一个容易被忽视的用途位掩码bitmask。一堆开关项可以压缩到一个整数里比如用二进制第 0 位表示“是否允许登录”第 1 位表示“是否允许发帖”第 2 位表示“是否允许评论”。MySQL 和 PostgreSQL 都支持位运算、|、、这些运算符都能用。选型原则是根据需要的位数量TINYINT 只能放 8 个位SMALLINT 放 16 个位INT 放 32 个位BIGINT 放 64 个位。生产环境里我见过有人用 TINYINT 存权限位结果权限项超过 8 个之后不得不升级成 SMALLINT然后又超过 16 个再升 INT。每次升级都是 ALTER TABLE费力费时。如果一开始就预计权限项会超过 8 项直接上 INT 或 BIGINT 更省事。位掩码方案写入时有一个坑如果你想“追加”一个标志位必须读取原始值再和当前值做按位或后写回这个“读改写”在高并发下可能丢更新所以高频变更的位掩码建议直接拆成多个布尔列而不是堆在一个整数里。同理如果只是存 8 个以内的固定标志用 TINYINT/SMALLINT 没问题但每个位的含义必须写在注释里否则三个月后你自己也看不懂那串 0 和 1。5. 常见问题与排查技巧实录5.1 线上问题速查表症状可能原因处理建议插入时报Out of range value for column数值超出当前整数类型的上限扩大类型范围一般直接改 BIGINT主键 ID 达到 21 亿后插入失败有符号 INT 自增耗尽容量使用在线工具或低峰期执行 ALTER 改 BIGINT无符号整数减法报out of rangeMySQL 无符号类型无法表达负数改用有符号类型或先 CAST 成 SIGNEDPG 中手动插入序列列后后续自增冲突序列值没有同步到新插入值手动setval(seq, 当前最大值)同步序列MySQL 中TINYINT(1)被其他团队误当布尔显示宽度 1 造成语义误解在注释说明业务语义或改用显式布尔枚举迁移 PG 后TINYINT(1)被映射成 BOOLEAN迁移工具按 MySQL 习惯猜测语义迁移前定制类型映射规则逐列确认主键与外键类型/无符号不一致导致 JOIN 慢隐式转换导致索引失效保证关联列类型、无符号属性完全一致序列到达 INT 上限但表字段已是 BIGINTPG 中序列类型没一并扩展执行ALTER SEQUENCE seq AS BIGINT5.2 如何快速找出全库最危险的整数列不要只查主键很多普通整数列也一样可能溢出。一个实用的巡检 SQLMySQL是扫描所有整型列结合表行数、当前最大值估算距离上限的比例。但生产中更稳妥的做法是分两步先用元数据找出所有整数列再在业务表上执行SELECT MAX(column)获取真实最大值。对于无法全表扫描的超大表可以只抽查分片或结合索引求最大值。PostgreSQL 则可以利用pg_attribute和information_schema查询所有整数列的类型、长度、最大值把结果输出成报表。如果你有权限访问生产元数据还可以把这些整数列按“风险等级”分组当前最大值已经超过类型上限 80% 的列为极高风险在 50%-80% 之间的为高风险低于 50% 的为中低风险。每月跑一次纳入容量管理台账。这个习惯能让你在任何潜在溢出发生前 1-2 年就开始准备迁移而不是半夜被监控告警吵起来。5.3 迁移与类型变更的最佳实践从 INT 改成 BIGINT 不是一句ALTER TABLE就能轻松完成的至少在千万级以上表里不是。MySQL 8.0 支持的ALGORITHMINPLACE, LOCKNONE允许在线修改表结构但修改主键类型依然需要重建聚簇索引底层会扫描全表并生成新的索引页磁盘 IO 和临时空间占用非常可观。如果表有外键约束或处于主从复制环境建议使用 pt-online-schema-change 或 gh-ost 这类在线变更工具并且提前评估工具对触发器、外键的限制。PostgreSQL 的ALTER TABLE ... ALTER COLUMN ... TYPE BIGINT在数据量大时同样会重写整张表且会拿到ACCESS EXCLUSIVE锁期间表不可读写。为避免长时间阻塞常用的方案是新建一个 BIGINT 类型的列然后分批回填数据最后加约束并切换读写。整个过程需要应用层配合做短暂停写或灰度切换。如果你要改的是自增主键PG 里记得同时扩序列否则下一个值还是会按旧序列生成并失败。无论使用哪种数据库改动前都要先检查所有关联视图、触发器、存储过程、外键列。最稳妥的顺序是先把子表外键列和目标关联列都扩到 BIGINT再改主表的主键最后重建相关索引和约束。这样可以尽量减少中间状态不匹配带来的锁冲突。5.4 实操心得与避坑清单多年维护这两类数据库我形成了一套自己的整数选型“默认值”凡是表的主键MySQL 里直接用 BIGINT不用 INT UNSIGNED 玩心跳PG 里用 BIGINT 或 GENERATED ALWAYS AS IDENTITY。凡是状态、枚举、标志位MySQL 里选 TINYINT/SMALLINTPG 里选 SMALLINT但每个取值的含义必须写注释。凡是金额、计数、分布式 ID、毫秒时间戳直接用 BIGINT不要省。凡是需要与企业外部系统交互的编号比如业务单号、流水号一定用 BIGINT 或字符串坚决不用自增主键当业务号。还有一个容易被忽略的点MySQL 隐式类型转换。如果两个表的关联列一个是 INT一个是 VARCHARMySQL 可能把字符串转成数字比较这时索引依然可能失效但如果一个是 INT UNSIGNED另一个是 INT也会发生隐式转换。所以建表时统一“同义字段的类型和长度”比如用户 ID在所有表里都是 BIGINT这就是最简单的性能保障。PostgreSQL 在类型行为上更严格一旦发现类型不匹配会直接拒绝比较表面上看是“不够方便”实际上能提前暴露问题反而是好事。数据库里不存在“完美的整数类型”只有“匹配业务模型的整数类型”。选类型这件事本质上是在存储空间、可扩展性、运维成本和跨库兼容性之间做权衡。回头再看那些在线上因为 INT 溢出导致事故的案例几乎都不是技术难题而是当初建表时少问了一句“这表三年后会有多少行这个字段最大可能到多少”。希望这篇梳理能让你在动手建表的那一刻多想一步把后面的坑提前填平。