开发者日记

外观

MySQL 自增 ID 迁 PG 序列踩坑实录 ​

问题背景：自增 ID 不是简单替换成序列 ​

很多团队从 MySQL 迁移 PostgreSQL 时，会把 AUTO_INCREMENT 机械改写为 SERIAL 或 GENERATED ... AS IDENTITY，然后在压测或双写阶段才发现问题：主键冲突、序列落后、回滚后出现空洞、批量写入两边 ID 不一致。根因不是 PostgreSQL 序列不好用，而是 MySQL 与 PostgreSQL 对“自增列”的语义边界不同。

MySQL 的 AUTO_INCREMENT 行为还受 innodb_autoinc_lock_mode、批量插入、主从复制等因素影响；PostgreSQL 的 SEQUENCE 则是独立对象，取值与事务隔离，核心保证是“并发安全、递增分配”，并不保证连续。迁移时如果仍按“自增 ID 应该连续、插入失败不跳号”的假设设计系统，就会在切换期踩坑。

典型故障现象包括：

  • 数据搬迁后，新写入立即报 duplicate key value violates unique constraint；
  • 应用回滚事务后发现 ID 不连续，误以为数据丢失；
  • MySQL 与 PG 双写时，一边由数据库生成 ID，另一边无法关联同一条业务记录；
  • 批量导入显式指定 ID，但没有推进序列，后续默认值仍从旧值开始；
  • 分表或多实例写入时，只迁移单表最大值，忽略整体 ID 分配策略。

MySQL 与 PostgreSQL 的 gap 语义差异 ​

MySQL AUTO_INCREMENT 的常见认知 ​

在 MySQL InnoDB 中，自增值一旦在插入过程中被分配，事务回滚后通常也不会回退计数器，否则高并发下必须长时间持有自增锁，严重影响写入吞吐。也就是说，MySQL 在现代配置下同样可能产生 gap。但不少历史系统依赖过早期版本或特定锁模式下的表现，形成了“回滚不耗号”的错误经验。

MySQL 还允许通过 ALTER TABLE ... AUTO_INCREMENT = ? 调整计数器，但在正常运行的高并发系统中频繁重置并不是可靠方案。

PostgreSQL SEQUENCE 的明确语义 ​

PostgreSQL 序列调用 nextval() 后，事务回滚不会把已分配的值塞回序列。序列操作具有事务独立性，多个会话可以并发放号，互不等待。因此 gap 是设计使然，不是异常。

下面的示例可以直接在 PostgreSQL 中运行：

sql
-- 创建测试表，id 由序列生成默认值
CREATE TABLE IF NOT EXISTS demo_order (
    id          bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    order_no    text NOT NULL,
    amount      numeric(12,2) NOT NULL DEFAULT 0
);

-- 第一个事务分配一个 ID，随后回滚
BEGIN;
INSERT INTO demo_order(order_no, amount)
VALUES ('ROLLBACK-001', 10.00)
RETURNING id;
ROLLBACK;

-- 第二个事务成功插入
INSERT INTO demo_order(order_no, amount)
VALUES ('OK-001', 20.00);

-- 可以看到：回滚不会归还序列值，ID 天然可能出现空洞
SELECT id, order_no, amount
FROM demo_order
ORDER BY id;

如果业务把 ID 连续性用于对账、排名、总数判断，必须立即改造。正确做法是：主键只保证唯一标识，连续性需求使用独立流水号、日期单号或审计表实现。

迁移方案对比：数据库序列、身份列与应用发号 ​

MySQL AUTO_INCREMENT库内生成 IDPG IDENTITY独立 SEQUENCE应用统一发号适合单库迁移兼容旧对象权限适合双写与分片ID 语义最可控

方案一：GENERATED AS IDENTITY ​

IDENTITY 是 SQL 标准写法，也是新系统首选。PostgreSQL 会在底层创建并管理序列，表结构表达更清晰，还可以通过 GENERATED ALWAYS 防止应用误写 ID。但数据迁移阶段经常需要显式导入历史主键，此时要使用 OVERRIDING SYSTEM VALUE。

sql
CREATE TABLE member (
    id        bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    username  text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

-- 历史数据显式导入 ID：必须声明覆盖系统生成值
INSERT INTO member(id, username)
OVERRIDING SYSTEM VALUE
VALUES (1001, 'alice'), (1002, 'bob');

-- 迁移完成后，把身份列关联序列推进到最大 ID 之后
SELECT setval(
    pg_get_serial_sequence('public.member', 'id'),
    (SELECT COALESCE(max(id), 1) FROM member)
);

注意 setval(seq, max(id)) 后，下一次 nextval 会从 max(id) + 1 开始。如果表为空，建议先执行一次 reset，避免把序列初始化到不符合预期的状态。

方案二：显式 SEQUENCE ​

显式序列适合老系统兼容，例如原 SQL 大量使用 nextval('xxx_seq')，或者多个表共享一个发号器。它比 SERIAL 伪类型更透明，也便于权限治理。

sql
CREATE SEQUENCE IF NOT EXISTS order_id_seq
    AS bigint
    INCREMENT BY 1
    MINVALUE 1
    NO CYCLE;

CREATE TABLE IF NOT EXISTS biz_order (
    id bigint PRIMARY KEY DEFAULT nextval('order_id_seq'),
    biz_no text NOT NULL
);

-- 回收历史表占用的 ID 段后，按当前最大值推进序列
SELECT setval(
    'order_id_seq',
    GREATEST((SELECT COALESCE(max(id), 0) FROM biz_order), 1),
    (SELECT count(*) > 0 FROM biz_order)
);

第三个参数表示 is_called。表非空时传 true，下一个值为当前值加步长；空表时传 false，下一次取号为序列起始值。

方案三：应用统一发号 ​

双写切换、分库分表、多活部署时，不建议让 MySQL 与 PG 各自生成主键。更稳妥的是由应用或独立发号服务生成全局 ID，再同时写入两端。常见选择包括 Snowflake、Leaf、号段模式或数据库号段表。

其优势是：

  • 双写阶段同一业务实体在两个库里主键一致，方便校验与回滚；
  • 不依赖任一目标库的自增实现；
  • 可以预留迁移批次号段，避免存量导入和增量写入冲突；
  • 后续分库分表时不用再次重构主键。

代价是引入发号组件的时钟、workerId、可用性与排序问题。如果使用 Snowflake，要严格管控机器编号；如果依赖时间趋势递增，要评估时钟回拨；如果使用号段服务，要设计号段续租与丢失策略。号段丢失只会产生 gap，不应影响唯一性。

存量迁移与双写切换的实操步骤 ​

冻结 ID 边界存量批量导入增量 CDC 同步序列安全推进双写一致性校验灰度读 PG停旧库写入

第一步，在存量任务开始前记录 MySQL 侧 ID 上界，并为增量流量预留足够空间。不要只在导入结束后取一次 max(id)，因为批量导入期间业务仍可能写入。

第二步，存量数据显式携带 ID 导入 PG，保留原主键可以降低外键、订单号、URL 链接和缓存 key 的改造成本。

第三步，通过 CDC 或双写同步增量数据。双写期间推荐由应用统一生成 ID；如果暂时无法改造，也要保证 MySQL 继续作为唯一发号源，PG 不自行分配冲突 ID。

第四步，切读前检查所有表的序列位置。下面的 SQL 可以批量生成对齐语句，执行前务必人工核对 schema、表名和列名：

sql
-- 为 PostgreSQL 12+ 的身份列或 serial 列生成 setval 语句
SELECT format(
    'SELECT setval(%L, COALESCE((SELECT max(%I) FROM %I.%I), 1), (SELECT count(*) > 0 FROM %I.%I));',
    pg_get_serial_sequence(ns.nspname || '.' || cls.relname, att.attname),
    att.attname,
    ns.nspname,
    cls.relname,
    ns.nspname,
    cls.relname
) AS fix_sql
FROM pg_class cls
JOIN pg_namespace ns ON ns.oid = cls.relnamespace
JOIN pg_attribute att
  ON att.attrelid = cls.oid
 AND att.attidentity IN ('a', 'd')
WHERE cls.relkind = 'r'
  AND ns.nspname = 'public'
ORDER BY cls.relname;

如果存量与增量仍在并发写入，单次对齐后仍可能落后。常见做法是把序列直接推进到一个安全上界，例如当前最大值再加 10000 或迁移批次 buffer：

sql
-- 假设 member 表迁移期间最高可能写入到 5000000
SELECT setval(
    pg_get_serial_sequence('public.member', 'id'),
    GREATEST((SELECT max(id) FROM member), 5000000)
);

预留号段会制造 gap，但这是可接受成本。相比主键冲突，空洞只影响审美，不影响正确性。

避坑总结：至少检查这 5 个细节 ​

1. 只迁数据不推进序列，导致主键冲突 ​

错误现象：历史数据导入成功，应用一写入就报 duplicate key value violates unique constraint。原因是显式插入 ID 不会自动调用 nextval()，序列仍停留在初始值。

修正方法：每张含身份列、serial 或默认序列的表，在切流前执行序列对齐；上线脚本中保留检查 SQL，并把序列校验纳入验收清单。

2. 把 gap 当数据丢失，错误补号或重置序列 ​

错误现象：事务回滚、唯一键冲突、批量插入失败后，ID 出现跳号，运维尝试把序列回退到 max(id)。如果旧 ID 已被其他事务使用或存在未提交数据，就会制造重复主键风险。

修正方法：明确 PostgreSQL 序列“只保证唯一发号，不保证连续”的契约。任何需要连续编号的场景，都应建立独立流水号规则，不复用主键。

3. 双写时两端各自自增，造成同一业务两条 ID ​

错误现象：MySQL 写入订单 ID 为 8001，PG 默认序列生成 6502，补偿任务无法关联，外键子表也无法同步。

修正方法：双写期使用应用统一 ID，或指定一个主发号库；另一端只接收 ID，不再使用本地默认自增。校验工具应以业务唯一键和主键双重比对。

4. 忽略外键表的序列与显式插入权限 ​

错误现象：父表迁移完成后，子表导入报外键错误；或者使用 GENERATED ALWAYS AS IDENTITY 时，显式插入历史 ID 报错，提示不能插入身份列。

修正方法：按外键依赖顺序导入，或在会话级临时关闭触发器前评估约束风险；身份列使用 OVERRIDING SYSTEM VALUE，导入结束后恢复应用层不传 ID 的写入方式。

5. 序列类型、步长和缓存配置评估不足 ​

错误现象：MySQL 主键是 bigint，PG 误建成 integer，几年后溢出；多主架构使用相同步长和起始值导致冲突；高并发下误认为 CACHE 1 更安全，结果 WAL 和序列锁竞争升高。

修正方法：主键统一使用 bigint；多节点发号通过不同 INCREMENT、起始偏移或统一发号服务隔离；CACHE 造成的空洞可以接受，但数据库异常崩溃可能丢弃缓存号段，业务不能依赖连续值。

结语 ​

MySQL 自增 ID 迁移 PostgreSQL，本质上是一次 ID 生成语义的迁移，而不是字段类型替换。短期单库迁移可以选择 IDENTITY 或显式 SEQUENCE，关键是导入后正确推进序列；中长期双写、分片和多活系统，更推荐应用统一 ID 生成。只要接受 gap、保证唯一、提前预留增量边界，绝大多数主键冲突都可以在切流前被发现并修复。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。