两年前我第一次把一个维护了快六年的 MySQL 业务库迁到 PostgreSQL（下面统称 PG），心里想得很简单：把表结构倒过去、改一下连接串，SQL 应该大差不差。真正开工之后才发现，卡住我的不是数据量，不是服务器配置，而是一堆看起来很小的语法差异。这篇文章就把我当时逐个踩过的“鸿沟”整理出来，重点放在语法迁移上：自增主键、引号与大小写、UPSERT、GROUP BY、NULL 排序、DDL 类型映射、日期函数和正则这些最容易让 MySQL 老手懵掉的地方。如果你也要做类似迁移，或者只是想在两种数据库之间切换不踩坑，可以直接参照这些对照关系来改。
1. 为什么语法迁移不能靠“搜索-替换”解决
1.1 两种数据库的“性格”差很远
MySQL 在很长一段时间里主打“好用、够用”，很多 SQL 写法是宽松的；PG 则更像一个严守 SQL 标准的“考官”，你写得不规范，它真的会报错。举个最简单的例子，下面这条 SQL 在很多老 MySQL 实例上能跑：
SELECT name, age, COUNT(*) FROM users GROUP BY name;但在 PG 里，它会直接告诉你：users.age必须出现在 GROUP BY 子句中或用于聚合函数。原因很本质：当你按name分组时，age到底取哪一行，SQL 标准没有定义。MySQL 的宽松模式默认给了你一个“任意值”，PG 不允许这种模棱两可。
这类差异才是迁移成本的大头。它们不会在数据拷贝阶段暴露，而是在业务代码跑起来之后一个接一个蹦出来。你很难用“搜索替换”一次解决，因为问题不是某个关键字不同，而是背后逻辑不同。
1.2 从“宽松模式”到“严格模式”
MySQL 的sql_mode可以调节不少行为，比如是否允许GROUP BY选非聚合列、是否允许''转成0、除法精度如何处理。PG 没有一套等价的 session 开关，它的行为更像“标准模式常开”。
这意味着迁移时你要把那些年在宽松模式下写出来的“野 SQL”重新按标准写一遍。这不是坏事，但对排期来说确实是变量。我当时的做法是：把所有待迁移的 SQL 先在 PG 上跑一遍，让报错帮我们列清单，而不是靠肉眼 review。
1.3 迁移前先统一 SQL 规范
如果你跟我一样是从一个老项目开始，建议先别急着改代码，先在团队里把 SQL 规范统一一下：表名、列名统一小写下划线；普通字符串用单引号；查询显式列出列名；GROUP BY里的列和SELECT里的非聚合列严格对应。这个动作能省掉后面一大半问题。否则同样的坑会在不同模块里反复出现，你改完一个还有下一个。
2. 引号、大小写、反斜杠：最容易出“玄学报错”的入口
2.1 反引号换成双引号，但别滥用
MySQL 里最常见的写法是用反引号包裹表名和列名：
SELECT `id`, `name` FROM `user` WHERE `status` = 1;PG 不支持反引号，它是 SQL 标准那一套，用双引号：
SELECT "id", "name" FROM "user" WHERE "status" = 1;但这里有个容易误导人的点：在 PG 里，双引号会创建一个“区分大小写、保留原样”的标识符。如果你建表时字段叫Name，那每次查询都得写成"Name"，写name会找不到。所以我的建议是：迁移时尽量把标识符整理成小写下划线，能不加引号就不加引号。别把 MySQL 那种“每列都反引号包起来”的习惯带到 PG，否则你会被大小写问题折磨死。
2.2 未加引号的标识符会被折叠成小写
这是 PG 和 MySQL 一个非常隐蔽的区别。在 PG 里，不带引号的标识符会被自动转成小写。也就是说，你建表时写：
CREATE TABLE UserInfo (id int);PG 实际存的名字是userinfo。之后你写SELECT * FROM UserInfo，PG 会把它翻译成userinfo，所以能查到。但如果你建表时用了双引号：
CREATE TABLE "UserInfo" (id int);那表名就是确确实实的UserInfo，之后写SELECT * FROM UserInfo会因为被折叠成userinfo而报“表不存在”。从 MySQL 迁过来时，如果老表名里有大写，我建议统一改成小写，或者在第一次建表时就保持不带引号的小写命名。不要在迁移后还依赖大小写混用，PG 会让你很痛苦。
2.3 字符串常量里的反斜杠默认不再转义
MySQL 默认把\n、\'这类反斜杠序列当转义符处理；PG 默认standard_conforming_strings=on，普通字符串里反斜杠就是普通字符。举个例子，SELECT 'a\nb';在 MySQL 里返回带换行的两行文本，在 PG 里返回字面量a\nb。如果你老代码里大量用\n拼换行，迁移后要改成chr(10)或者用 PG 的转义字符串E'\n'。
这个坑特别隐蔽，因为通常不会报错，只是返回值变了。最典型的是路径字符串和正则表达式，原本“看起来对”的结果会悄悄变掉。
3. 自增主键迁移：从 AUTO_INCREMENT 到 SERIAL 与 IDENTITY
3.1 三种写法的对应关系
MySQL 的自增主键写法大家很熟：
CREATE TABLE users ( id BIGINT NOT NULL AUTO_INCREMENT, username VARCHAR(50) NOT NULL, PRIMARY KEY (id) );PG 有两条路可以走。老一点的方式是用SERIAL伪类型：
CREATE TABLE users ( id serial PRIMARY KEY, username varchar(50) NOT NULL );SERIAL本质上是自动帮你创建了一个序列（sequence），然后默认取下一个值。现代 PG 更推荐标准写法：
CREATE TABLE users ( id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, username varchar(50) NOT NULL );这里我特意用了BY DEFAULT而不是ALWAYS。原因很实际：MySQL 允许你显式插入自增列的值，老业务代码里很可能有这类操作。GENERATED ALWAYS默认会拦截显式插入，你得额外写OVERRIDING SYSTEM VALUE，迁移阶段没必要给自己加这种负担。等业务完全稳定，再考虑收紧成ALWAYS也不迟。
3.2 插入后拿新 ID 的方式变了
MySQL 老代码经常这样拿自增 ID：
INSERT INTO users (username) VALUES ('neo'); SELECT LAST_INSERT_ID();PG 更推荐直接用RETURNING：
INSERT INTO users (username) VALUES ('neo') RETURNING id;这种方式的好处是单条 SQL 就能拿到完整行数据，而且是事务内安全的，不容易出现连接串行执行时取错 ID 的问题。批量插入时也一样：
INSERT INTO users (username) VALUES ('a'), ('b'), ('c') RETURNING id, username;如果你用的是 ORM 或 JDBC 的getGeneratedKeys()，PG 驱动也支持，但底层实际上也是帮你走RETURNING。所以裸 SQL 场景里，直接用RETURNING最直观。
3.3 手动重置自增起点
老项目经常有“清掉测试数据后，把自增 ID 回到某个值”的操作。MySQL 写法是：
ALTER TABLE users AUTO_INCREMENT = 10000;PG 里分两种情况。如果是serial：
ALTER SEQUENCE users_id_seq RESTART WITH 10000;如果是GENERATED AS IDENTITY：
ALTER TABLE users ALTER COLUMN id RESTART WITH 10000;注意，serial自动创建的序列名一般是“表名_列名_seq”，这个命名容易被忽略，但重置时必须写对。
4. 几个高频 DML 语法差异，逐个对齐
4.1 UPSERT：ON DUPLICATE KEY UPDATE换成ON CONFLICT
MySQL 里很常见的写法：
INSERT INTO users (id, email, name) VALUES (1, 'a@example.com', 'A') ON DUPLICATE KEY UPDATE name = VALUES(name);PG 的等价写法是：
INSERT INTO users (id, email, name) VALUES (1, 'a@example.com', 'A') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;两个容易踩的差异点：
第一，PG 不认VALUES()函数，冲突行要引用EXCLUDED这个虚拟表。第二，ON CONFLICT DO UPDATE必须指定冲突目标。比如表上有两个唯一键，你只说ON CONFLICT DO UPDATE不写具体列名，PG 会报错，因为它在多个唯一索引下不知道该按哪个来判断冲突。MySQL 的ON DUPLICATE KEY UPDATE不需要指定，它是碰到任意唯一键冲突就触发。这个语义差异会导致同一个 SQL 在 PG 上必须补上明确的冲突目标。
如果你原本用的是INSERT IGNORE，PG 里对应的是ON CONFLICT DO NOTHING，这个可以不指定目标：
INSERT INTO users (id, email) VALUES (1, 'a@example.com') ON CONFLICT DO NOTHING;4.2 UPDATE/DELETE 后面接 LIMIT：PG 不直接支持
在 MySQL 里，分批更新或删除经常这么写：
DELETE FROM logs WHERE status = 0 LIMIT 500; UPDATE tasks SET retry_count = retry_count + 1 WHERE status = 0 LIMIT 100;PG 不支持UPDATE ... LIMIT，也不支持DELETE ... LIMIT。正确做法是用 CTE 先取出目标 ID，再做操作：
WITH ids AS ( SELECT id FROM logs WHERE status = 0 ORDER BY id LIMIT 500 ) DELETE FROM logs USING ids WHERE logs.id = ids.id;更新类似：
WITH ids AS ( SELECT id FROM tasks WHERE status = 0 ORDER BY created_at LIMIT 100 ) UPDATE tasks SET retry_count = retry_count + 1 FROM ids WHERE tasks.id = ids.id;这里多说一句：如果分批处理不关心“取前多少行里的哪些行”，确实可以只写LIMIT不写ORDER BY，但结果就不确定了。MySQL 和 PG 都是如此。而既然要对数据做更新/删除，我建议无论如何都加上ORDER BY，至少保证操作行为和预期一致。
4.3 严格 GROUP BY 与“每组取某一行”的改写
前面说过，老 MySQL 允许GROUP BY时直接选出不在分组里的列。这种 SQL 到了 PG 会直接被拒。正确的处理方式不是到处找ANY_VALUE平替，而是把业务需求问清楚：
如果只是想要分组后的某个聚合值，就写聚合：
SELECT user_id, MAX(age) FROM profile GROUP BY user_id;如果是要“每个用户的最近一条记录”，那用DISTINCT ON比聚合更合适：
SELECT DISTINCT ON (user_id) user_id, age, created_at FROM profile ORDER BY user_id, created_at DESC;DISTINCT ON是 PG 的特色语法，非常适合“每组取一行”的场景。迁移时如果遇到这类查询，别硬改成GROUP BY再在外面套子查询，DISTINCT ON会更清爽。
4.4 NULL 排序、整数除法与字符串拼接
这几个点看着小，真遇到时数据对不上最容易查半天。
先说 NULL 排序。MySQL 默认升序时 NULL 排最前，降序时 NULL 排最后；PG 默认升序时 NULL 排最后，降序时 NULL 排最前，因为 PG 把 NULL 默认当成“比任何值都大”。如果你想保持 MySQL 的排序结果，迁移时要显式写：
| 原 MySQL 写法意图 | PG 迁移写法 |
|---|---|
ORDER BY col ASC（NULL 在头部） | ORDER BY col ASC NULLS FIRST |
ORDER BY col DESC（NULL 在尾部） | ORDER BY col DESC NULLS LAST |
再说除法。MySQL 里SELECT 5 / 2;返回2.50这样的小数；PG 里整数相除默认得到整数，5 / 2是2。如果你需要保留小数，必须让其中一个操作数是浮点数或 numeric：
SELECT 5::numeric / 2; SELECT 5.0 / 2;这种差异会影响报表、统计类 SQL 的精度，迁移后要逐条检查。
最后说拼接。MySQL 老代码爱用CONCAT，PG 也有CONCAT，但两者对 NULL 的处理不一样。SELECT CONCAT(NULL, 'abc');在 MySQL 里结果是 NULL，在 PG 里结果是abc，因为 PG 的concat会跳过 NULL。如果你依赖“只要有一项为 NULL 结果就是 NULL”的业务逻辑，需要自己写CASE WHEN处理，或者用NULLIF包裹。用||拼接时也注意，PG 的||对非文本类型不一定会隐式转换，建议把数字先转成text再拼。
5. DDL 建表语句与类型映射，粘贴即报错的高发区
5.1 没有 ENGINE，也没有 DEFAULT CHARSET
MySQL 建表时常见的尾巴：
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;在 PG 里这两个选项都不存在。PG 表的默认存储引擎就是“事务性存储”，你不需要也不能指定ENGINE=InnoDB。字符集也不是表级别设置的，而是数据库集群在初始化时定好。所以迁移建表脚本时，直接删掉这两段即可。
这里要特别提醒一个性能语义变化：如果你的 MySQL 表之前是 MyISAM，SELECT COUNT(*)会非常快，因为它有表级计数。PG 没有这种“非事务表”，COUNT(*)需要扫描数据，大表上会明显变慢。迁移后如果业务对这个查询性能敏感，别硬扛，考虑用统计信息、物化视图或业务侧自增计数来替代。
5.2 常见类型对照表
| MySQL 类型 | PG 推荐映射 | 说明 |
|---|---|---|
TINYINT(1) | boolean或smallint | 如果只存 0/1，直接映射成 boolean |
INT UNSIGNED | bigint | 无符号 int 最大值仍然在 bigint 范围内 |
BIGINT UNSIGNED | numeric(20,0) | 超过bigint范围时用 numeric 兜底 |
DATETIME | timestamp without time zone | 原类型不带时区，保持语义 |
TIMESTAMP | timestamp with time zone | MySQL 的 timestamp 实际受会话时区影响 |
TEXT/BLOB | text/bytea | PG 的 text 可以存大对象，不需要长度限制 |
JSON | jsonb | 一般建议用 jsonb，性能更好、支持索引 |
ENUM('a','b') | text+ CHECK 约束 或 PG 内置 enum | MySQL 的 enum 变更成本低，PG 变更 enum 成本高，需权衡 |
建表时还常见VARCHAR(255)，这个两个数据库都支持，不用太担心。真正要留意的是UNSIGNED：PG 没有UNSIGNED选项。如果只是INT UNSIGNED，映射成bigint通常没问题；但遇到BIGINT UNSIGNED且业务确实会超过bigint上限时，就只能用numeric(20,0)了。
5.3 ALTER TABLE 的语法习惯要改
MySQL 改列常用MODIFY COLUMN：
ALTER TABLE users MODIFY COLUMN age INT NOT NULL;PG 用的是两步走：
ALTER TABLE users ALTER COLUMN age SET NOT NULL;改类型则是：
ALTER TABLE users ALTER COLUMN age TYPE bigint USING age::bigint;USING这一步很重要。当 PG 不能自动完成类型转换时（比如字符串转数字、字符串转日期），你必须告诉它怎么转。如果不加USING，很多TYPE操作会直接报错。
5.4 索引写法差异
MySQL 允许在建表语句里内联定义索引：
CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(50), KEY idx_name (name) );PG 通常把索引独立写在建表语句后面：
CREATE TABLE t ( id int PRIMARY KEY, name varchar(50) ); CREATE INDEX idx_name ON t (name);大表上加索引，建议用 PG 的CREATE INDEX CONCURRENTLY，避免长时间阻塞读写。这个关键词在 MySQL 里没有，算是一个迁移时需要顺手改掉的习惯。
6. 日期函数、聚合函数与正则表达式的“翻译表”
6.1 日期时间函数对照
| MySQL | PG | 说明 |
|---|---|---|
NOW() | now()或CURRENT_TIMESTAMP | 差异不大 |
CURDATE() | CURRENT_DATE | 返回当前日期 |
DATE_FORMAT(d, '%Y-%m-%d') | to_char(d, 'YYYY-MM-DD') | 格式符写法完全不同 |
STR_TO_DATE(s, '%Y-%m-%d') | to_date(s, 'YYYY-MM-DD') | 字符串转日期 |
DATEDIFF(a, b) | a::date - b::date | PG 日期相减就是整数天数 |
DATE_ADD(d, INTERVAL 1 DAY) | d + interval '1 day' | PG 的 interval 写法固定 |
UNIX_TIMESTAMP() | extract(epoch from now()) | 返回浮点秒数 |
FROM_UNIXTIME(ts) | to_timestamp(ts) | 注意返回 timestamptz |
DATE_FORMAT的格式符是最容易出错的。%Y-%m-%d %H:%i:%s在 PG 里对应YYYY-MM-DD HH24:MI:SS，很多报表 SQL 都被这个点卡过。
6.2 聚合与 NULL 处理相关函数
MySQL 的IFNULL(a, b)在 PG 里是COALESCE(a, b)；MySQL 的IF(condition, a, b)建议改成标准CASE WHEN condition THEN a ELSE b END。
GROUP_CONCAT是 MySQL 非常高频的函数，PG 里的等价物是string_agg：
-- MySQL SELECT user_id, GROUP_CONCAT(DISTINCT tag SEPARATOR ',') FROM user_tags GROUP BY user_id; -- PG SELECT user_id, string_agg(DISTINCT tag, ',') FROM user_tags GROUP BY user_id;如果原来的GROUP_CONCAT里有排序需求，比如GROUP_CONCAT(tag ORDER BY tag SEPARATOR ',')，对应 PG 的写法是：
SELECT user_id, string_agg(tag, ',' ORDER BY tag) FROM user_tags GROUP BY user_id;6.3 正则会话要换操作符
MySQL 用的是REGEXP关键字：
SELECT * FROM users WHERE email REGEXP '^[a-z]+@example\\.com$';PG 用的是一组操作符：
| 语义 | MySQL 写法 | PG 写法 |
|---|---|---|
| 匹配 | col REGEXP 'pattern' | col ~ 'pattern' |
| 不匹配 | col NOT REGEXP 'pattern' | col !~ 'pattern' |
| 忽略大小写匹配 | col REGEXP 'pattern'（非二进制字符串通常不区分大小写） | col ~* 'pattern' |
| 忽略大小写不匹配 | col NOT REGEXP 'pattern' | col !~* 'pattern' |
这里有一个来自 MySQL 的隐藏差异：MySQL 的REGEXP在普通字符串场景下大多不区分大小写，而 PG 的~是区分大小写的。迁移时如果你的老查询依赖“不区分大小写”，只把REGEXP换成~还不够，应该换成~*。
7. 迁移实操顺序与自查清单
7.1 先用工具把表结构和数据搬过来
最好的策略是先让“数据底座”跑起来，再逐条修 SQL。我在迁移时用的是pgloader，它可以连接 MySQL，自动完成建表、类型转换和数据导入，基本命令类似这样：
pgloader mysql://user:pass@127.0.0.1:3306/app postgresql://user:pass@127.0.0.1:5432/app但你要清醒一点：这类工具解决的是“表结构和数据”，不是“业务 SQL”。它不会帮你把ON DUPLICATE KEY UPDATE、GROUP_CONCAT、REGEXP翻译掉。工具只是减少体力活，改 SQL 仍然要人工做。
7.2 在代码仓库里 grep 这些关键词
迁移期我一般会在代码库全文搜索下面这些关键词，基本能定位九成以上的问题点：
- 反引号，例如
`id`这种写法 AUTO_INCREMENTENGINE=建表配置ON DUPLICATE KEY UPDATEINSERT IGNOREGROUP_CONCATDATE_FORMATUNIX_TIMESTAMPIFNULLREGEXPUPDATE ... LIMIT、DELETE ... LIMIT
连接串方面也别漏掉：驱动类要从com.mysql.cj.jdbc.Driver换成org.postgresql.Driver，端口从3306变成5432，URL 前缀从jdbc:mysql://变成jdbc:postgresql://。如果原来 URL 里带了一堆 MySQL 专属参数，比如useSSL、characterEncoding，基本都要删掉或换成 PG 对应配置。
7.3 回归验证时重点盯这几个场景
迁移完不是“能连上、能跑通”就完了。我当时的做法是专门列了一张测试 SQL 清单，每个场景都要人工对一遍结果：
- 插入后返回自增 ID
- 批量 INSERT 遇到唯一键冲突
- 包含 NULL 的排序结果
- 整数除法结果
GROUP BY查询在所有模块都能跑- 日期格式化输出
- 分页查询的 total 和当前页数据
- 正则匹配的命中列表
我建议你哪怕觉得很基础，也要逐条对。尤其是 NULL 排序和无符号类型这两个点，很多“线上数据对不上”的问题都出在这里。
最后再说一点个人体会：迁移过程中如果某条 SQL 在 PG 上改起来很麻烦，不要急着找一个“长得像”的函数糊弄过去。PG 之所以报错，往往是因为原来的 SQL 本身就有语义含糊的地方——它逼你把业务问题想清楚，这其实是件好事。凡是认真改过去的 SQL，后来都成了团队 SQL 规范里最好的反面教材。
