MySQL 迁移 PgSQL：难点在语义，不在语法

一句话：迁移的难点不是把 LIMIT a,b 改成 LIMIT b OFFSET a，而是类型语义、大小写、自增/序列、事务和分词行为的差异；先证明收益，再渐进切流。

适用 MySQL 5.7/8.0 → PostgreSQL 16/17。迁移前先在测试环境跑完整链路，不要直接在生产改语法。

为什么需要

常见迁移动机有三类：需要 JSONB/全文/向量/地理等 PostgreSQL 能力；复杂查询和扩展生态；统一技术栈。也可能只是「想换」。

如果没有明确的业务收益，迁移的成本很难回收：双写、数据校验、SQL 改写、运维重建、团队学习，都是持续投入。先回答「当前 MySQL 具体疼在哪，PostgreSQL 怎么解决」，再决定是否迁移。

核心机制

类型映射

MySQL	PostgreSQL	注意
BIGINT AUTO_INCREMENT	bigint GENERATED BY DEFAULT AS IDENTITY	PostgreSQL 10+ 推荐；bigserial 是旧写法
TINYINT / TINYINT(1) / BOOLEAN	smallint / boolean	TINYINT(1) 按布尔处理
INT UNSIGNED	integer + CHECK (x >= 0) 或 bigint	无 UNSIGNED 类型
DATETIME / TIMESTAMP	timestamp / timestamptz	跨时区业务用 timestamptz
JSON	jsonb	二进制、可索引；需要保留原文才用 json
TEXT / BLOB	text / bytea	大值会走 TOAST
ENUM('a','b')	varchar + CHECK 或原生 enum	见下
SET('a','b')	text[] + CHECK 或关联表	PostgreSQL 无 SET 类型

ENUM 怎么映射

MySQL 的 ENUM 是列定义里的字符串集合，存储为序号，排序按声明顺序。PostgreSQL 有两种做法：

• varchar + CHECK：适合值经常变、需要跨库兼容的场景。加值只改约束，不锁类型。
• 原生 enum：适合值稳定、需要类型安全的场景。ALTER TYPE ... ADD VALUE 在事务里有版本限制（PostgreSQL 12+ 可在事务内执行，但新值提交后才可用；以官方文档为准），删除值更麻烦。

不要直接把 MySQL 的 ENUM 序号当业务值迁移；迁移后按字符串重新落库，并验证排序语义。

自增与序列

GENERATED BY DEFAULT AS IDENTITY 允许显式插入 id，适合数据迁移；GENERATED ALWAYS 会拒绝显式值（除非 OVERRIDING SYSTEM VALUE）。

数据导入后，序列不会自动追上最大 id，必须手动同步：

SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  (SELECT max(id) FROM orders)
);

否则下一条 INSERT 会撞主键。

大小写与排序规则

• MySQL 的表名大小写取决于 lower_case_table_names 和操作系统；列名通常不区分大小写。
• PostgreSQL 把未加引号的标识符折叠成小写；加双引号才保留大小写。迁移时不要给表名/列名加引号，除非确实需要。
• MySQL 的字符串比较常受 _ci 排序规则影响（不区分大小写/重音）；PostgreSQL 默认排序规则通常区分大小写。需要不区分大小写时，用 citext 扩展、lower() 表达式索引，或 ILIKE。
• 索引的排序规则必须与查询一致；lower(email) 查询要用 lower(email) 表达式索引。

事务与锁

维度	MySQL InnoDB	PostgreSQL
默认隔离级别	Repeatable Read	Read Committed
DDL	隐式提交，不能回滚	事务内可回滚（大部分 DDL）
幻读防护	next-key lock	快照隔离；Serializable 用 SSI
并发更新冲突	等待/死锁	RR 下可能直接报序列化失败，需要重试
自增锁	有	序列不参与事务，回滚会留下空洞

迁移后要检查应用是否依赖 MySQL 的 RR 行为；PostgreSQL 默认 RC，同一事务内两次查询可能看到不同结果。需要可重复读时显式设置隔离级别，并处理 40001 序列化失败。

SQL 语法差异

-- 分页
-- MySQL: SELECT * FROM t LIMIT 10, 20;
SELECT * FROM t LIMIT 20 OFFSET 10;

-- upsert
-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE ...
INSERT INTO t (id, name) VALUES (1, 'x')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;

-- 忽略冲突
-- MySQL: INSERT IGNORE ...
INSERT INTO t (id, name) VALUES (1, 'x') ON CONFLICT DO NOTHING;

-- 返回自增 id
-- MySQL: LAST_INSERT_ID()
INSERT INTO t (name) VALUES ('x') RETURNING id;

其他常见替换：DATE_FORMAT → to_char，STR_TO_DATE → to_date，FIND_IN_SET → = ANY(string_to_array(...))，GROUP_CONCAT → string_agg，IFNULL → COALESCE，REGEXP → ~，反引号 → 双引号。MySQL 的 REPLACE INTO 是「删+插」，会触发删除触发器、级联外键，不等价于 upsert；用 ON CONFLICT。

最小上手

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE orders (
  id         bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  status     varchar(20) NOT NULL
             CHECK (status IN ('new', 'paid', 'cancelled')),
  email      citext,
  created_at timestamptz NOT NULL DEFAULT now(),
  payload    jsonb NOT NULL DEFAULT '{}'::jsonb
);

INSERT INTO orders (id, status, email, payload) VALUES
  (1, 'paid', 'User@Example.com', '{"source":"mysql"}')
ON CONFLICT (id) DO UPDATE
SET status = EXCLUDED.status,
    email = EXCLUDED.email,
    payload = EXCLUDED.payload;

INSERT INTO orders (status, email) VALUES ('new', 'a@example.com')
RETURNING id;

-- 数据导入后同步序列
SELECT setval(
  pg_get_serial_sequence('orders', 'id'),
  (SELECT max(id) FROM orders)
);

用 pgloader 做初步搬迁：pgloader mysql://user:pass@127.0.0.1/appdb postgresql://user:pass@127.0.0.1/appdb。它能处理大部分类型和自增，但 ENUM、全文检索、存储过程、触发器等仍要人工核对。

生产动作

• 四步迁移：新表先建在 PostgreSQL → 双写 + CDC 增量同步 → 读流量灰度切 → 历史表逐张迁移并下线 MySQL。每一步都保留回退能力。
• 数据校验：行数、主键范围、金额/时间字段抽样比对、JSON 字段规范化后比对；不要只看「导入成功」。
• 序列同步：所有导入过数据的表都要 setval；这是迁移后最常见的主键冲突来源。
• 大小写与排序：用真实数据测 WHERE email = ?、ORDER BY name、LIKE，确认结果与 MySQL 一致；不一致时用 citext、lower() 或显式 COLLATE 修正。
• 中文全文检索：MySQL 的 FULLTEXT 不能直接翻译成 to_tsvector('chinese', ...)；先安装 zhparser 或 pg_jieba，建配置、建生成列和 GIN 索引，再用 ts_debug 验证分词。
• 事务行为：把默认隔离级别、DDL 回滚、SELECT ... FOR UPDATE、死锁重试写进迁移检查表；应用层要能处理 40001（序列化失败）和 40P01（死锁）。
• 性能回归：导入后 ANALYZE，对核心查询逐条 EXPLAIN (ANALYZE, BUFFERS)，对比 MySQL 的扫描行数和回表次数；不要用「SQL 能跑」作为验收标准。
• 回退预案：MySQL 保留只读副本或继续双写；写清楚切回步骤、数据补偿方式和责任人。

边界

• 没有收益不要迁：简单 CRUD、团队熟悉 MySQL、云 RDS 成熟，迁移的收益可能覆盖不了成本。
• MySQL 8.0 不是旧版本：CTE、窗口函数、CHECK、函数索引、Hash Join 都已具备；用 5.7 的印象做判断会失真。
• pgloader 不是全自动：ENUM、SET、触发器、存储过程、全文索引、字符集/排序规则都可能需要手工处理。
• 事务语义不能假设一致：MySQL 默认 RR、PostgreSQL 默认 RC；MySQL DDL 隐式提交、PostgreSQL 可回滚；依赖这些行为的代码要改。
• 大小写和排序是最隐蔽的坑：上线前用真实数据验证，不要只在测试库用英文小写数据。
• 中文分词不是装个扩展就结束：词典、停用词、分词粒度都会影响召回；需要持续评估。

讲清楚

MySQL 迁 PostgreSQL，语法改写只是表面工作。真正会出事的是语义：ENUM 的排序和加值方式不同；自增要用 identity/序列，导入后必须 setval；未加引号的标识符在 PostgreSQL 会折叠成小写，字符串比较默认区分大小写；MySQL 默认 RR、PostgreSQL 默认 RC，MySQL 的 DDL 隐式提交、PostgreSQL 的 DDL 可以回滚；中文全文检索在 PostgreSQL 需要 zhparser/pg_jieba，不是内置能力。迁移策略应该是渐进的：新表先上、双写 + CDC、读流量灰度、历史表逐张迁，全程保留回退。验收不要只看「SQL 能跑」，要对比数据、执行计划和事务行为。