开发者日记

外观

MySQL 迁 PG 避坑实录：unsigned、时区与自增序列 ​

为什么类型映射是 MySQL 迁 PG 的第一坑 ​

很多团队把 MySQL 到 PostgreSQL 的迁移理解成“换个驱动、改改分页语法”，真正跑通后才发现：数据能导进去不等于语义一致。MySQL 的 UNSIGNED、DATETIME/TIMESTAMP 隐式时区行为、AUTO_INCREMENT 自增机制，在 PostgreSQL 里都没有一一对应的语法糖。一旦 schema 转换阶段偷懒，问题往往在上线后才以“主键冲突”“时间差 8 小时”“数值溢出”的形式爆发。

本文聚焦三类最容易踩坑的字段：无符号整数、时间类型与时区、自增序列，给出可直接运行的 PostgreSQL DDL、迁移期校验 SQL，以及生产环境的避坑清单。

三类核心差异与映射方案对比 ​

UNSIGNED 整数无原生无符号类型TIMESTAMP/DATETIME时区语义不同AUTO_INCREMENT升位整数 + CHECKBIGINT 约束非负timestamptz 统一写入即转 UTC 存储GENERATED AS IDENTITY

UNSIGNED：PostgreSQL 没有无符号整数 ​

MySQL 常用 INT UNSIGNED 表达 0 到 42 亿的主键或外键，而 PostgreSQL 的整数类型只有 smallint、integer、bigint，全部带符号。常见的偷懒做法是直接映射成 integer，结果当 MySQL 里的 ID 超过 21.4 亿时，数据迁移直接报 integer out of range。

推荐映射策略：

MySQL 类型取值上限PostgreSQL 推荐说明
TINYINT UNSIGNED255smallint + CHECK空间与范围平衡
SMALLINT UNSIGNED65535integer + CHECK升一位最稳妥
INT UNSIGNED约 42.9 亿bigint + CHECK主键外键必须升位
BIGINT UNSIGNED约 1.8×10^19numeric(20,0)PG bigint 装不下，慎用

不要为了“模拟无符号”去自定义 uint 类型，那会让驱动、ORM、备份恢复都踩兼容坑。用更大的内置类型加 CHECK (id >= 0)，既保留约束又保证生态兼容。

时间类型：DATETIME 与 TIMESTAMP 的时区陷阱 ​

MySQL 里 TIMESTAMP 会做时区转换（存入转 UTC、读取按会话时区还原），而 DATETIME 原样存字面值。PostgreSQL 对应两类：timestamp without time zone（简称 timestamp）只存裸字面值，timestamp with time zone（timestamptz）内部以 UTC 存储、按会话 TimeZone 展示。

迁移原则是：业务时间、创建时间、订单时间一律用 timestamptz，避免跨时区部署时出现“同一时刻不同字面值”。只有“生日”“每天凌晨 2 点执行”这种与物理时刻无关的挂钟时间，才用 timestamp。

自增：AUTO_INCREMENT 换成 identity 列 ​

MySQL 的 AUTO_INCREMENT 是表属性，自增值跟表绑定；PostgreSQL 早期用 SERIAL（背后自动建 sequence），SQL 标准写法是 GENERATED ... AS IDENTITY。新系统推荐 identity，因为它能防止手动往 sequence 表里乱插导致关系断裂，权限与属主也更清晰。

实操：可运行的 schema 转换 SQL ​

下面以一张典型 MySQL 订单表为例，演示转换前后的完整 DDL。

MySQL 原始定义：

sql
-- MySQL：无符号主键、TIMESTAMP 自动更新、AUTO_INCREMENT
CREATE TABLE orders (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  amount      INT UNSIGNED NOT NULL,
  remark      VARCHAR(200),
  created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

对应的 PostgreSQL 建表语句：

sql
-- PostgreSQL：升位 bigint + 非负约束 + identity + timestamptz
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id     bigint NOT NULL CHECK (user_id >= 0),
    amount      bigint NOT NULL CHECK (amount >= 0),
    remark      varchar(200),
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- PG 没有 ON UPDATE 自动刷新时间，需要触发器实现
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_updated
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- 建议按外键建索引（PG 不会自动给外键建索引）
CREATE INDEX idx_orders_user_id ON orders(user_id);

数据导入后，最关键的一步是把序列推进到当前最大 ID，否则后续插入会主键冲突：

sql
-- 数据批量导入后，必须把 identity 序列校准到 max(id)
SELECT setval(
    pg_get_serial_sequence('orders', 'id'),
    (SELECT COALESCE(max(id), 1) FROM orders),
    (SELECT max(id) IS NOT NULL FROM orders)
);

-- 校验：序列下一个值应大于表内最大 id
SELECT currval(pg_get_serial_sequence('orders', 'id'));

时间字段迁移时，务必在会话层统一时区，避免 DATETIME 裸值被错误解释：

sql
-- 导入会话统一设置为业务时区，保证 timestamptz 转换可预期
SET TIME ZONE 'Asia/Shanghai';

-- 迁移后抽样核对：同一行在两个库的 UTC 时刻应一致
SELECT id, created_at AT TIME ZONE 'UTC' AS created_utc
FROM orders
ORDER BY id
LIMIT 10;

迁移流程与校验节点 ​

读取源 schema类型映射转换unsigned/时区/序列生成 PG DDL全量+增量同步序列校准setval + 行数对账时间/范围校验UTC 时刻比对

避坑总结：来自生产的具体教训 ​

坑一：INT UNSIGNED 直接映射 integer，大 ID 导入溢出。 现象是数据同步到一半报 ERROR: integer out of range，任务中断且难以断点续传。修正方法是在 schema 转换阶段就把所有 *INT UNSIGNED 的主键、外键统一升为 bigint，并加 CHECK (col >= 0)；迁移前用 SELECT MAX(id) 预估每张表的实际值域，决定是否需要升位。

坑二：把所有时间都建成 timestamp，跨时区后时间错乱。 现象是应用服务器从 UTC+8 切到 UTC 部署后，历史时间字面值没变但代表的物理时刻整体平移，报表统计差 8 小时。修正方法是业务时刻统一用 timestamptz，并在连接串或会话中显式设置 TimeZone，不要依赖数据库默认值；只有挂钟语义的字段才保留 timestamp。

坑三：导入数据后忘记校准序列，上线即主键冲突。 现象是批量导入历史订单后，新订单第一次插入就报 duplicate key value violates unique constraint，根因是 identity 序列仍从 1 开始。修正方法是数据导入完成后、切流前执行前面的 setval(pg_get_serial_sequence(...))，并把“序列 nextval 大于 max(id)”纳入切换前的强制检查项。

坑四：误以为 PG 也支持 ON UPDATE CURRENT_TIMESTAMP。 现象是 updated_at 在更新后永远不变化，缓存失效与增量同步全部漏数。修正方法是用 plpgsql 触发器函数维护，或在应用层统一赋值；建触发器时记得用 BEFORE UPDATE FOR EACH ROW，并在双写切换阶段对两库的更新时间做抽样比对。

把这四类检查固化进 schema 转换脚本和上线 checklist，MySQL 到 PostgreSQL 的类型映射才算真正闭环：类型范围不丢、时间语义不偏、自增主键不断。

免责声明

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

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

免责声明

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