你写表结构的时候整数列是怎么选的我猜大多数人的肌肉记忆是——主键用 INT计数用 INT排序用 INT实在不行上 BIGINT再配一个 TINYINT 放状态。这套习惯在单一 MySQL 项目里能跑很久可一旦你开始用 PostgreSQL或者需要跨库迁移、数据量爬到亿级“INT 够用”这句话就会反噬。MySQL 和 PostgreSQL 对整数类型的设计思路差异比多数人想象中大得多一边是五种整数类型加上无符号、显示宽度这类扩展能力另一边是干干净净的三种有符号整数拒绝一切花活。这篇文章就把两边的整数家族从头到尾盘一遍讲清楚每种类型的范围、存储代价、索引影响和典型业务场景最后附上一份迁移清单帮你建表时一步选对。1. “默认 INT”为什么在两个数据库里都靠不住1.1 看似够用的 INT其实只是一张“五年期饭票”先聊一个反常识的点INT 的范围是 -2147483648 到 2147483647也就是大约 21.47 亿。听起来很大但主键不是这么算的要从“存量 新增速度 导入数据 压测数据”一起看。假设某张用户表已经存了 2 亿行业务每天新增 100 万行。上限减去存量还剩约 19.47 亿除以每天 100 万大约只能撑 1947 天也就是五年出头。这还没算你中途导入一次历史数据、压测脚本灌了几千万条、或者多环境合并数据的情况。真到了那一天MySQL 不会自动把 INT 扩成 BIGINT你得重建表、换主键、改外键代价非常大。PostgreSQL 这边同样如此。PG 虽然改列类型比 MySQL 更灵活但主键类型变更依然要重写表、重建索引而且所有引用它的外键列必须同步变更。所以“默认 INT”本质上是把风险推迟而不是消除。1.2 MySQL 是工具箱PostgreSQL 是标准派这是理解两库差异的钥匙。MySQL 的整数类型体系更像一个“务实工具箱”有 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 五档还提供UNSIGNED无符号、ZEROFILL零填充、显示宽度这些扩展能力目的就是让开发者在不同存储粒度间做精细选择。PostgreSQL 的态度完全不同它只提供 SMALLINT、INTEGER、BIGINT 三档且全部是有符号整数。PG 开发者认为“整数就是 2 字节、4 字节、8 字节三种规格”超过这个范围的语义约束比如非负应该交给 CHECK 约束或 DOMAIN而不是塞进类型系统里。这个理念差异直接导致了一个现象从 MySQL 迁到 PG 时很多整型列需要“升级”或“降级”比如 MEDIUMINT 要升成 INTEGERTINYINT 基本能平移成 SMALLINTINT UNSIGNED则必须升到 BIGINT。后面我会专门给一份映射表。1.3 三条选型原则先立起来结合这些年看过的表结构我把整数类型选择的判断标准压缩成三条建表时按顺序过一遍基本不会错范围优先。先算清当前最大值、未来增长倍数以及是否可能导入历史数据。选能容纳这些值的“最小合法类型”。有符号优先。UNSIGNED确实能白拿一倍范围但会把迁移、计算、类型兼容全搅进来。非负语义用 CHECK 表达更干净。迁移友好。只要有一点跨库迁移的可能性就优先选择两库都有的类型。BIGINT 两库都有TINYINT 和 MEDIUMINT 却不是 PG 的通用语言。2. 一张对照表看清两边的整数家族2.1 MySQL 的五种整数类型MySQL 的整型从 1 字节到 8 字节覆盖非常细。下面按字节数从小到大列全含范围类型存储字节有符号范围无符号范围TINYINT1-128 ~ 1270 ~ 255SMALLINT2-32768 ~ 327670 ~ 65535MEDIUMINT3-8388608 ~ 83886070 ~ 16777215INT / INTEGER4-2147483648 ~ 21474836470 ~ 4294967295BIGINT8-2^63 ~ 2^63-10 ~ 2^64-1每个字节多一倍的范围这句话在 MySQL 这边是字面意义上的。TINYINT适合特别小的枚举值MEDIUMINT是 MySQL 独有中间态省那 1 字节在一些统计表里能看到效果。2.2 PostgreSQL 的三种整数类型PG 这边少了两档范围相对朴素类型存储字节范围常用写法SMALLINT2-32768 ~ 32767int2INTEGER / INT4-2147483648 ~ 2147483647int4BIGINT8-9223372036854775808 ~ 9223372036854775807int8没有 MEDIUMINT没有无符号也没有显示宽度。如果你是 MySQL 背景直接转 PG刚开始会非常不习惯尤其是看到user_id这类列在 MySQL 是INT UNSIGNEDPG 里却写成了BIGINT。2.3 关键差异速查表能力MySQLPostgreSQL整数类型数量5 种3 种是否有 MEDIUMINT有3 字节无是否有 UNSIGNED支持不支持是否有显示宽度 INT(11)有8.0.17 之后废弃不支持布尔语义BOOLEAN 是 TINYINT(1) 的别名原生 BOOLEAN1 字节自增实现AUTO_INCREMENTSERIAL / GENERATED AS IDENTITY非负约束无符号CHECK 约束 / DOMAIN这张表基本就是两库整数差异的“全部真相”。接下来把每条差异背后的逻辑拆开你就知道设计时该站在哪边。3. 无符号、显示宽度和 MEDIUMINTMySQL 的“特色功能”到底怎么消化3.1 UNSIGNED 那多出来的一倍范围真的值吗MySQL 的UNSIGNED很好理解就是把符号位也用来存数据范围直接翻倍。所以INT UNSIGNED上限是 4294967295比有符号的 21.47 亿多一倍。但这个“红利”在跨库场景里是个大坑。PostgreSQL 不认无符号于是INT UNSIGNED不能直接映射成 PG 的INTEGER因为上限超了。你只能选BIGINT。换句话说为了省 4 字节你换来了一个迁移时必须升级成 8 字节的定时炸弹。更隐蔽的问题是数据语义。很多业务会把-1当“未设置”或“未知”存量数据里多多少少有点脏数据。原表定义了UNSIGNED后这些负数早期是插不进去的但如果你当初用的是无符号那么负值在业务层会被绕过去比如先转成字符串再插最后表里值域混乱。迁到 PG 后普通的BIGINTCHECK (col 0)会直接拒绝这些历史脏数据你不得不先清洗。这也是我为什么一直强调有符号优先——工程上的脏数据处理成本远大于那 1 bit。3.2 INT(11) 显示宽度是个历史包袱早年 MySQL 的显示宽度是为了终端对齐输出INT(10)配合ZEROFILL能输出0000001234这种固定宽度的数字。它本质上只是个显示属性完全不限制存储范围。也就是说INT(1)照样能存 1000000能存 21 亿。但就是这个小细节坑过不少人。我遇到过某项目里开发看到字段定义是INT(1)以为只能存 0—9于是在应用层做了一个截断把大于 9 的值直接切成个位数等到数据对不上账才发现问题。MySQL 官方在 8.0.17 已经开始废弃显示宽度语法MySQL 8.0.19 之后甚至不允许在整数类型定义里使用显示宽度只允许 ZEROFILL 场景。如果你的库还在用老版本新表就别再写这种语法了影响不大但误导性极强。PostgreSQL 从第一天就没有显示宽度对齐是客户端界面的事数据库不该管。这种“少即是多”的设计反而避免了一整类误解。3.3 MEDIUMINT 省的那 1 字节多数项目不值得MEDIUMINT是 MySQL 的中间档3 字节有符号上限 838 万无符号上限 1677 万。典型场景是某些中间统计表、短生命周期表的自增 ID或者 CDN 类的节点编号。但到了 PostgreSQL没这个类型只能升成INTEGER。1000 万行数据每行多 1 字节也就多 10MB几乎可以忽略。谈不上伤筋动骨。真正要注意的只有一点如果你的表将来要跨库或者你的团队同时维护 MySQL 和 PG 两套库就别单纯为了省那 1 字节把结构设计成“MySQL 专属形状”。反过来如果确定长期留在 MySQL并且列的上限能压进 1677 万以内用MEDIUMINT UNSIGNED是合理的节俭。关键判断依据是“是否确定”而不是“目前看着够”。3.4 PG 侧的非负约束CHECK 与 DOMAINPG 不支持无符号替代方案通常是 CHECK 约束ALTER TABLE t ADD CONSTRAINT chk_col_nonnegative CHECK (col 0);如果要复用可以定义 DOMAINCREATE DOMAIN uint2 AS SMALLINT CHECK (VALUE 0); CREATE DOMAIN uint4 AS INTEGER CHECK (VALUE 0); CREATE DOMAIN uint8 AS BIGINT CHECK (VALUE 0);不过使用 DOMAIN 有个隐形成本某些 ORM 和 JDBC 驱动对 DOMAIN 类型的识别不如基础类型那么稳定比如 Hibernate 可能会把 DOMAIN 映射为 Serializable 或 Object反而让查询参数绑定报错。我实际用下来的经验是除了非常标准的“非负整数”场景与其用 DOMAIN不如直接普通整数类型加 CHECK 约束让整条工具链都把列当成普通整数看。约束是额外的一层保护而不是类型系统的一部分。4. 主键与自增策略这是两个数据库最该分开想的地方4.1 MySQL 的 AUTO_INCREMENT 和 BIGINT UNSIGNEDMySQL 主键最常见写法是INT AUTO_INCREMENT PRIMARY KEY。但如果你问我在新项目里建业务主键我的默认推荐是BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY。理由很简单MySQL 改主键类型的成本非常高高到很多团队宁可写脚本拆表也不愿意原地 ALTER。主键一旦定下来会牵动外键、分区、二级索引叶子节点、二进制日志解析等一堆东西。BIGINT UNSIGNED 上限约 1844 亿亿2^64-1对绝大多数业务来说这是“一台服务器报废了都到不了”的量级可以让你彻底忘掉容量焦虑。当然这不是说所有表都得上 BIGINT。像字典表、配置表行数稳定在几百几千用 INT 完全合理。判断标准我之前说过有没有导入历史数据的可能有没有跨库合并的可能有没有长期高增长的可能。三个“没有”才轮得到 INT。补充一个经验AUTO_INCREMENT的递增和事务回滚不是一回事。一个事务插入了数据但回滚了自增值通常不会回退所以主键出现空洞是正常的。别写程序去依赖“主键必须连续”那是给自己找麻烦。4.2 PostgreSQL 的 SERIAL 与 GENERATED AS IDENTITYPG 有两条路来自增。老办法是SERIAL伪类型CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT );SERIAL其实是语法糖等价于INTEGER DEFAULT nextval(users_id_seq)。它好用但有个缺点序列和表的耦合比较弱你到\d里看到的是一个默认值表达式不是列属性。新项目我更推荐 PG 10 之后引入的标准写法CREATE TABLE users ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT );GENERATED ALWAYS AS IDENTITY是标准 SQL默认情况下不允许手工插入 ID需要用OVERRIDING SYSTEM VALUE才能指定值。这对数据迁移非常友好防止出现导入时主键错乱。它还允许你控制序列的所有权权限比裸SERIAL严谨。无论哪种写法底子都是序列。序列有两个特点要注意序列不随事务回滚所以一定有空洞。序列默认不会自动重置迁移后不手动 setval很可能从 1 开始插入然后撞主键。4.3 迁移后忘了重置序列就是事故把数据从 MySQL 迁到 PG或者给 PG 现有表补数据拍平序列这一步千万不能漏SELECT setval(pg_get_serial_sequence(users, id), (SELECT MAX(id) FROM users), true);pg_get_serial_sequence会自动找到列背后绑定的序列名。第三参数传true表示下一个值取 max1。如果忘了跑这条 SQL新插入的数据会撞原有主键轻则报错重则数据错乱。这个坑我在多个迁移现场见过属于“必须写进检查清单”的那种。4.4 什么时候坚持 INT 才是对的说到底不是所有主键都该 BIGINT。下面这些情况用 INT 就足够甚至更合适表是配置类、字典类行数上限几百几千永不导入历史数据。当前最大值离 21.47 亿还差两个数量级以上且没有长期高速增长预期。明确不跨库合并、不参与分库分表。在这些场景里INT 的 4 字节优势能省一点索引空间代价也更低。过度设计成 BIGINT 不是错误只是不必要的成本。最怕的是反过来该用 BIGINT 时克扣成了 INT最后重建表。5. 状态、计数、排序字段不要只看范围要看语义5.1 布尔与状态枚举TINYINT(1)、SMALLINT 还是 BOOLEANMySQL 没有真正的布尔类型BOOLEAN只是TINYINT(1)的别名。写flag TINYINT(1)还是flag BOOLEAN存储上完全一样都是 1 字节。问题在于 MySQL 默认不阻止你往 TINYINT(1) 里塞 2、塞 42。8.0.16 之后 CHECK 约束终于强制执行但在 5.7 及更早版本CHECK 只是语法接受、实际不生效需要触发器或靠应用层保证。如果有条件建议对状态字段加上显式约束CREATE TABLE orders ( status TINYINT UNSIGNED NOT NULL DEFAULT 0, CONSTRAINT chk_orders_status CHECK (status IN (0, 1, 2, 3)) );PG 这边更简单纯“是否”语义直接用原生BOOLEAN状态枚举建议用SMALLINT或INTEGER配合 CHECK。注意不要把布尔字段和状态字段混在一个类型里状态码未来很可能从 0/1 扩到 0/1/2布尔就扛不住了。5.2 计数器用 20 倍预留来判断点赞数、阅读数、库存、在线人数这类高频 UPDATE 的计数器类型选小了真的会爆。SMALLINT 上限 65535听着够小但如果一个热帖一天涨 1 万赞6 天半就能顶到上限。这种风险完全没必要冒。我给团队定的习惯是“20 倍预留”算当前业务峰值的 20 倍再找能装下这个值的“最小类型”。比如预期某计数器峰值 5 万20 倍是 100 万INT 绰绰有余如果预期峰值 1000 万20 倍是 2 亿仍然装得进 INT 的 21.47 亿如果预期峰值是 3 亿那就老实上 BIGINT。还有一点容易忽略计数器常常要支持累加、扣减甚至可能出现负数兜底。PostgreSQL 里没有无符号纯非负计数器用INTEGER加 CHECK 没问题如果业务允许负值直接普通BIGINT就行别画蛇添足。5.3 外部标识符别把“看起来像数字”当数值这类字段是最容易踩坑的手机号 11 位超过 INT 上限不少人随手写了 BIGINT某第三方单据号 18 位数字也塞 BIGINT然后遇到前导零就翻车。比如字符串形式的007123被 MySQL 翻译成 BIGINT 会变成7123原始语义没了。判断一个列该用整型还是字符串只看一点它会不会参与算术运算会累加、会比较大小、会用作主键递增那才是数值只是“一个用数字表示的标识符”应该用 VARCHAR 或 UUID。手机号是标识符不是数值订单号如果是纯数字且长度可控BIGINT 能存但前导零与格式展示都很尴尬稳妥方案还是 VARCHAR。5.4 排序权重整数的朴素方案比浮点稳做列表拖拽排序时常见做法是给每条记录一个order_num新增记录插在中间取(prev next) / 2。用 DOUBLE 算一两次没事算几十次后就出现精度耗尽产生0.00000011920928955078125这种尾巴逼得最后重排。换成整数同样会遇到“中点间距耗尽”问题但至少整数耗尽时你可以明确感知范围比如用 BIGINT每个间隔初始放宽到 2^30插入多次后仍有足够空间。更彻底的方案是间隔权重分裂加定期重算或者用带层次的字符串排序键。但至少从类型层面用整数做排序字段比浮点稳健得多也更利于索引。6. 性能真相字节数少一点到底能换来什么6.1 行大小与缓存命中率的关系先给结论整型宽度影响的是存储密度不是计算速度。CPU 算一个 4 字节整数和 8 字节整数差距可以忽略真正受影响的是数据页能放下多少行、Buffer Pool 能缓存多少热点数据。粗算一下一个 INT 比 BIGINT 少 4 字节。假设某表一行约 600 字节16KB 的数据页能放 27 行左右如果把所有整型列都从 BIGINT 换成 INT这行可能降到 500 字节一页能放 32 行以上。表扫描和热点查询时缓存命中率会有可感知的差别。现实里行里还有变长字段、NULL bitmap、事务 ID 这些开销但数量级概念是这样。全表 BIGINT 不是免费的尤其是有多个整型列和复合索引的表。6.2 索引扇出与树高B 树索引里键越窄一个页能存的索引项越多树越高层时 IO 越少。主键索引的叶子直接存整行数据主键宽度基本被数据行吸收但二级索引的叶子存的是主键值这意味着主键越宽每个二级索引项越宽。比如一个二级索引列是 INT页里能放约 1000 个索引项主键换成 BIGINT 后可能降到 800 多个。对几百万行的表树高基本没变化到几亿行的量级树高和分支跳转可能差一层。这层差别在常规查询里可能只有零点几毫秒但在全表范围的大量随机读里会被放大。我经过多次项目复盘得到的体会是性能优化不要指望靠类型宽度救命。索引扇出带来的收益远小于用错类型导致的重建成本。该 BIGINT 就 BIGINT别为了省 4 字节每天提心吊胆。6.3 一个观察量级仅供参考以前在某分析库里见到过一张 3000 万行的宽表大概有 8 个整型列从 INT 改成 BIGINT 后表文件总体大约膨胀了 10% 出头三个列的复合索引膨胀接近 20%全表扫描的 IO 请求数有明显上升。单行点查的延迟基本没有变化。所以我的倾向是普通项目范围正确比微优化重要真正的高并发核心表才值得把每一列都“斤斤计较”。7. 从 MySQL 迁到 PostgreSQL整型迁移清单7.1 一张迁移映射表照着做就行MySQL 源类型PG 目标类型注意事项TINYINTSMALLINT范围含得下直接映射TINYINT(1) 布尔用BOOLEAN应用层 SQL 要从 0/1 改成 TRUE/FALSETINYINT UNSIGNEDSMALLINT CHECK (col 0)或 SMALLINT DOMAINSMALLINTSMALLINT直接映射SMALLINT UNSIGNEDINTEGER CHECK (col 0)65535 能被 21 亿轻松覆盖MEDIUMINTINTEGER升一档MEDIUMINT UNSIGNEDINTEGER CHECK (col 0)1677 万 21 亿安全INTINTEGER直接映射INT UNSIGNEDBIGINT上限 42.95 亿大于 INTEGER 上限必须升级BIGINTBIGINT直接映射BIGINT UNSIGNEDNUMERIC(20) 或 BIGINT 强约束校验2^64-1 超出 BIGINT 有符号上限多数业务实际值不超过 2^63-1需先摸底最后一个BIGINT UNSIGNED是迁移里的深水区。MySQL 的BIGINT UNSIGNED上限是 2^64-1而 PG 的 BIGINT 是有符号的上限 2^63-1。如果老表里没有超过 9.22e18 的大值用BIGINT再加CHECK (col 0)就行如果有超大腿数据只能升到NUMERIC(20)。7.2 自增主键的复制与序列重置实操步骤大致是在 PG 建新表主键用GENERATED ALWAYS AS IDENTITY。从 MySQL 复制数据插入时用OVERRIDING SYSTEM VALUE指定原有 IDINSERT INTO new_users (id, name, created_at) OVERRIDING SYSTEM VALUE SELECT id, name, created_at FROM legacy_users;重置序列到最大 ID 的下一个值SELECT setval(pg_get_serial_sequence(new_users, id), (SELECT MAX(id) FROM new_users));顺序绝对不能反先复制数据再重置序列。一旦忘记最后一步新插入数据的 ID 会和已有行冲突。还要检查所有引用该表的外键列。比如 users 表 ID 迁成 BIGINTorders 表的 user_id 如果还是 INTEGERJOIN 时轻则执行计划变差重则直接报类型不匹配。外键列必须跟着主键一起升。7.3 隐式转换和 ORM 类型断言的坑MySQL 的隐式转换非常宽松WHERE id 123能跑甚至WHERE id abc也只是把字符串转成 0不报错。PG 不行integer text这种比较直接抛operator does not exist。所以从 MySQL 迁到 PG 后大量手写 SQL 会在上线第一天连环报错。另一个坑在 ORM。Java 项目里字段类型定义成Integer但 PG 的 BIGINT 返回的是Long反序列化时可能直接ClassCastException。迁移前最好先扫一遍实体类、Mapper 文件、DTO 类型的映射把INT-INTEGER的类型声明改成LONG-BIGINT对齐。7.4 迁移前的五问自查最后分享一个我每次迁移前都会做的“五问自查”直接决定整型迁移风险有没有无符号列有就按照上面的升级规则处理。有没有 MEDIUMINT有统一升 INTEGER。有没有 BIGINT UNSIGNED有先跑一遍SELECT MAX(col)摸清真实值域。有没有 AUTO_INCREMENT 与序列偏移有补 setval。有没有依赖隐式转换的 SQL有提前改成显式CAST。这五个问题跑完整数迁移这部分基本不会出事故。值此一表不只是为了应付迁移更是逼自己把“类型到底包住了什么语义”想清楚。我个人的体会是整数类型的选择从来不只是性能和空间问题而是容量规划与迁移成本问题。最后再分享一个实用小技巧建表时把每个整数列的选择理由写进注释比如id BIGINT PRIMARY KEY -- 预留跨库合并、status SMALLINT NOT NULL DEFAULT 0 -- 0待支付 1已支付 2超时。半年后你自己回看表结构不用翻需求文档也能一眼明白当初为什么这么设计。真正需要花时间重选类型的时候往往就是当初“随手写”留下的债。