引言

表结构迁完了、数据也灌进去了，应用一连数据库，报错像连珠炮一样蹦出来：column "user_id" does not exist（明明字段就在那）、operator does not exist: integer = character varying（参数类型不对）、syntax error at or near "LIMIT"（LIMIT 两个参数写反了）……我们迁第一个服务时，300 多条 MyBatis SQL 在测试环境跑出了 70 多个语法错误，全部集中在同一批高频差异上。

MySQL 的哲学是"我尽量帮你跑"，PostgreSQL 的哲学是"写错了我就报错"。这篇不讲选型（那是上一篇《为什么迁移》的事），只解决迁移动手时的具体问题——把返工率最高的 10 个语法差异逐个列透：每一个都给 MySQL 写法、PG 写法、错误现场和修复，MyBatis 项目额外标注 XML 适配要点。建议直接收藏，迁移时对照逐条改。

差异 1：自增主键 AUTO_INCREMENT → IDENTITY（SERIAL 能用但不推荐）

MySQL

CREATE TABLE t_order (
    id      BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PostgreSQL：两种写法

-- 写法 A：IDENTITY（SQL 标准，推荐）
CREATE TABLE t_order (
    id      BIGINT       GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL
);

-- 写法 B：SERIAL（PG 老式语法糖，自动建序列+设默认值）
CREATE TABLE t_order (
    id      BIGSERIAL    PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL
);

坑点

差异 2：分页 LIMIT m,n → LIMIT n OFFSET m（参数顺序相反！）

-- MySQL：跳过 20 条取 10 条（第 3 页）
SELECT * FROM t_order ORDER BY id LIMIT 20, 10;

-- PostgreSQL：LIMIT 是条数，OFFSET 是跳过数
SELECT * FROM t_order ORDER BY id LIMIT 10 OFFSET 20;

这是迁移中数量最多、最隐蔽的错误：语法都合法，不会报错，只是分页数据全错——第一页正常（offset=0），第二页开始每页取的条数和偏移完全对调，测试时一不小心就漏掉。

MyBatis 适配

<!-- MySQL 时代 -->
LIMIT #{offset}, #{pageSize}

<!-- PostgreSQL：注意参数位置对调 -->
LIMIT #{pageSize} OFFSET #{offset}

PageHelper 插件会自动按 DbType.POSTGRE_SQL 生成正确方言，纯手写分页必须全局搜索 LIMIT # 和 LIMIT \d+, 逐个核对。

顺带：深分页两边都慢

-- OFFSET 100 万在两种库都会扫 100 万行再丢弃
-- 通用优化：游标分页（keyset pagination），PG 语法
SELECT * FROM t_order WHERE id > #{lastId} ORDER BY id LIMIT 10;

差异 3：字符串拼接 CONCAT vs ||（MySQL 的 || 是 OR！）

-- MySQL
SELECT CONCAT(first_name, '-', last_name) FROM t_user;
SELECT CONCAT('a', NULL, 'b');          -- 结果 'ab'：CONCAT 忽略 NULL

-- PostgreSQL
SELECT first_name || '-' || last_name FROM t_user;
SELECT 'a' || NULL || 'b';             -- 结果 NULL！|| 遇 NULL 整串变 NULL

三个必踩点

1. MySQL 迁移过来的 SQL 里 || 是逻辑或，PG 里变成字符串连接——老代码如果用 a || b 写布尔条件（极少见但真有），语义全变。
2. NULL 行为相反：MySQL CONCAT 跳过 NULL，PG || 遇 NULL 返回 NULL。要模拟 MySQL 行为用 CONCAT() 函数（PG 也提供，且同样忽略 NULL），或先 COALESCE：

SELECT CONCAT('a', NULL, 'b');                    -- 'ab'，PG 的 CONCAT 与 MySQL 行为一致
SELECT COALESCE(nick, '') || '-' || COALESCE(mobile, '');

3. MyBatis 里拼模糊查询：

<!-- MySQL：CONCAT 在 PG 也能用，直接保留最省事 -->
WHERE name LIKE CONCAT('%', #{keyword}, '%')

-- PG 另一种写法 || ，注意 %% 是 MyBatis XML 转义
WHERE name LIKE '%' || #{keyword} || '%'

建议迁移期统一保留 CONCAT()——两边都支持、NULL 语义一致，改动最小。

差异 4：日期函数：DATE_FORMAT/INTERVAL 语法全换

-- MySQL
SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i'),
       DATE_ADD(created_at, INTERVAL 7 DAY),
       DATEDIFF(NOW(), created_at)
FROM t_order;

-- PostgreSQL
SELECT to_char(created_at, 'YYYY-MM-DD HH24:MI'),
       created_at + INTERVAL '7 days',
       EXTRACT(EPOCH FROM (now() - created_at)) / 86400
FROM t_order;

常用对照

最大的坑：INTERVAL 参数化

MyBatis 里"查询最近 N 天"在 MySQL 可以直接拼数字，PG 不行：

<!-- ❌ PostgreSQL 报错：INTERVAL 后面的数字不能直接用占位符拼成这样 -->
WHERE created_at > NOW() - INTERVAL #{days} DAY

-- ✅ 写法 1：make_interval 函数，参数可绑定
WHERE created_at > now() - make_interval(days => #{days})

-- ✅ 写法 2：数字直接乘 interval 字面量
WHERE created_at > now() - (#{days} || ' days')::interval

-- ✅ 写法 3（推荐）：用秒数参数，避免类型转换
WHERE created_at > now() - (#{days} * INTERVAL '1 day')

时间戳选择

MySQL 的 datetime 迁到 PG 推荐用 TIMESTAMPTZ（带时区），不要用 TIMESTAMP（不带时区）——跨时区部署时后者会埋坑。pgloader 用 cast 规则 datetime to timestamptz 自动处理。

差异 5：标识符大小写——PG 的"小写折叠"陷阱

这是迁移第一天 100% 会遇到的问题：

-- 你在 MySQL 里习惯的建表
CREATE TABLE UserOrder ( userId BIGINT );

-- PG 里执行后：未加引号的名字被折叠成小写！
-- 实际表名：userorder  实际列名：userid

SELECT UserId FROM UserOrder;
-- 报错：relation "userorder" 存在，但列 "userid"... 
-- 而你用双引号查 "UserId" 时又必须永远带引号

处置方案

-- ❌ 不要驼峰
CREATE TABLE "UserOrder" ( "userId" BIGINT );   -- 此后所有 SQL 必须带双引号，痛苦终身

-- ✅ 蛇形命名
CREATE TABLE user_order ( user_id BIGINT );
SELECT user_id FROM user_order;                 -- 干净

迁移时用 pgloader 或脚本把驼峰表名列名统一转蛇形；MyBatis XML 里所有带反引号的字段映射全量改写。JPA 项目配 @Column(name="user_id") 或全局命名策略。

两个相关坑：user、order、group 是 PG 保留字——MySQL 里能当表名（order 是常见表名！），PG 里必须改名或加双引号。建议直接把 order 改成 t_order 或 trade_order，别图省事加引号。

差异 6：反引号 ` → 双引号 "，字符串只能用单引号

-- MySQL：标识符用反引号
SELECT `user_id`, `order_no` FROM `t_order` WHERE status = 'PAID';

-- PostgreSQL：标识符用双引号（且按差异5，最好根本不用），字符串永远单引号
SELECT user_id, order_no FROM t_order WHERE status = 'PAID';

PG 的严格区分：

-- MySQL 能跑的：WHERE name = "张三"（双引号当字符串）
-- PostgreSQL 直接报错：column "张三" does not exist（它把"张三"当成列名了！）

这是 Java 代码拼接 SQL 里最隐蔽的错误来源——把所有字符串双引号改成单引号（MyBatis 参数用 #{} 不受影响，受影响的是手写字面量）。

差异 7：UPDATE ... JOIN → UPDATE ... FROM

MySQL 支持用 JOIN 直接驱动更新，PG 语法不同：

-- MySQL：用订单表关联，把用户表的最近下单时间刷回去
UPDATE t_user u
JOIN t_order o ON o.user_id = u.id
SET u.last_order_time = o.created_at
WHERE o.id = 100;

-- PostgreSQL：UPDATE ... SET ... FROM ... WHERE
UPDATE t_user u
SET last_order_time = o.created_at
FROM t_order o
WHERE o.user_id = u.id
  AND o.id = 100;

注意：多行会更新的陷阱

PG 的 UPDATE...FROM 要求 FROM 的每行唯一匹配目标行——如果 FROM 侧对同一用户命中多行，PG 不报错，随机用其中一行更新（MySQL 同样有未定义行为）。迁移这类 SQL 时先确认关联基数，必要时先聚合：

-- 安全写法：先聚合保证一对一
UPDATE t_user u
SET order_cnt = s.cnt
FROM (SELECT user_id, count(*) AS cnt FROM t_order GROUP BY user_id) s
WHERE s.user_id = u.id;

DELETE 同理

-- MySQL
DELETE o FROM t_order o JOIN t_user u ON o.user_id = u.id WHERE u.status = 9;

-- PostgreSQL：USING
DELETE FROM t_order o
USING t_user u
WHERE o.user_id = u.id AND u.status = 9;

差异 8：REPLACE INTO / INSERT IGNORE / ON DUPLICATE KEY → ON CONFLICT

这组差异语义最容易迁错，必须逐个核对业务意图：

8.1 三种 MySQL 写法对应的 PG

-- ① INSERT IGNORE：冲突就静默跳过
INSERT IGNORE INTO t_user(id, name) VALUES (1, '张三');
-- PG：
INSERT INTO t_user(id, name) VALUES (1, '张三')
ON CONFLICT (id) DO NOTHING;

-- ② ON DUPLICATE KEY UPDATE：冲突则更新（upsert）
INSERT INTO t_user(id, name, updated_at) VALUES (1, '张三', now())
ON DUPLICATE KEY UPDATE name = VALUES(name), updated_at = VALUES(updated_at);
-- PG：
INSERT INTO t_user(id, name, updated_at) VALUES (1, '张三', now())
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, updated_at = EXCLUDED.updated_at;
-- 注意：MySQL 用 VALUES(列名) 引用待插入值，PG 用 EXCLUDED.列名

-- ③ REPLACE INTO：冲突则"删旧行+插新行"
REPLACE INTO t_user(id, name) VALUES (1, '张三');

8.2 REPLACE INTO 没有等价物，且它本身就是危险写法

REPLACE INTO 的真实行为：
  主键/唯一键冲突 → DELETE 旧行 → INSERT 新行
后果：
  - 自增 id 会变（新行拿新 id）
  - 未在 REPLACE 语句里出现的列全部重置为默认值（旧值丢失！）
  - 级联删除/触发器被触发（外键 ON DELETE CASCADE 会删子表数据）

迁移时不能机械替换成语义接近的 ON CONFLICT DO UPDATE 然后只更新部分列吗？要逐业务确认：想保留的旧列必须在 SET 里显式保留（SET name=EXCLUDED.name，其他列不动），这反而修掉了 REPLACE INTO 偷偷丢列的历史坑。

8.3 ON CONFLICT 的冲突目标必须写清楚

-- PG 要求指定冲突基于哪个唯一约束/索引（MySQL 不用指定，自动猜）
ON CONFLICT (id) DO NOTHING
ON CONFLICT (uk_order_no) DO UPDATE SET ...
ON CONFLICT DO NOTHING;            -- 不指定目标=任意冲突都忽略，慎用

-- MyBatis 动态 SQL 里的 upsert 模板
INSERT INTO t_order (order_no, status, amount)
VALUES
<foreach collection="list" item="o" separator=",">
    (#{o.orderNo}, #{o.status}, #{o.amount})
</foreach>
ON CONFLICT (order_no) DO UPDATE
SET status = EXCLUDED.status, amount = EXCLUDED.amount;

MyBatis-Plus 的 saveOrUpdate/saveBatch 在 PG 方言下会自动生成 ON CONFLICT，但要确认实体类 @TableId 之外的唯一键需要用 @TableField + 自定义 SQL。

差异 9：GROUP_CONCAT → string_agg（还有个排序坑）

-- MySQL：把一个用户的订单号拼成逗号分隔字符串
SELECT user_id,
       GROUP_CONCAT(order_no ORDER BY created_at SEPARATOR ',')
FROM t_order
GROUP BY user_id;

-- PostgreSQL
SELECT user_id,
       string_agg(order_no, ',' ORDER BY created_at)
FROM t_order
GROUP BY user_id;

差异点：

-- bigint 列直接聚合报错：function string_agg(bigint, unknown) does not exist
SELECT string_agg(id::text, ',') FROM t_order;

MySQL 时代被 group_concat_max_len 悄悄截断过数据的团队，迁 PG 后这个坑自然消失，但要注意聚合结果可能很长，应用侧别再按 1024 长度分配缓冲区。

差异 10：条件与空值函数 + 布尔/严格类型（报错率最高的"隐形"差异）

这一类没有一个单独的语法点，却是运行时报错最密集的区域，合并为第 10 个差异讲透。

10.1 IFNULL / IF → COALESCE / CASE

-- MySQL
SELECT IFNULL(nick_name, '未设置'), IF(amount > 100, '大额', '普通') FROM t_order;

-- PostgreSQL：没有 IFNULL、没有 IF() 函数
SELECT COALESCE(nick_name, '未设置'),
       CASE WHEN amount > 100 THEN '大额' ELSE '普通' END
FROM t_order;

COALESCE 是 SQL 标准，MySQL 也支持——迁移时直接统一用 COALESCE，双向兼容。

10.2 TINYINT(1) → boolean

-- MySQL：布尔本质是 tinyint，0/1 与 true/false 混用
WHERE is_deleted = 0     WHERE is_deleted = false    -- 都能跑

-- PostgreSQL：boolean 是真类型，只接受 true/false（以及 't'/'f'、'yes'/'no' 文本）
WHERE is_deleted = false
-- WHERE is_deleted = 0  → 报错：operator does not exist: boolean = integer

MyBatis 里传 Integer 的 0/1 参数到 boolean 列会直接类型错误——Java 侧改用 Boolean 类型，或 SQL 里显式转换（不推荐，污染代码）。建表时把 TINYINT(1) 一律映射为 boolean；表示枚举状态的 TINYINT（0待支付/1已支付/2已取消）则映射为 SMALLINT/INT，不要用 boolean。

10.3 严格类型：隐式转换全部失效

-- MySQL：varchar 列和数字比，内部转一把，能跑（还经常导致索引失效而不自知）
WHERE phone = 13800000000
WHERE order_no = 20260919001

-- PostgreSQL：直接报错
-- operator does not exist: character varying = integer
WHERE phone = '13800000000'        -- 必须类型一致：字符串就传字符串
WHERE id = '123'                   -- 反方向：int 列 = 字符串，同样报错，传数字

MyBatis 排查方法：搜索 jdbcType 缺失的参数绑定、DTO 里 String/Integer/Long 与列类型不匹配的字段。报错信息 operator does not exist: xxx = yyy 一律按"两边类型不一致"处理。

严格类型短期是痛，长期是福：MySQL 里 varchar_col = 123 隐式转换会让索引失效（函数转换列），是线上慢 SQL 的著名来源；PG 逼你在编译/启动期就写对。

附：迁移执行建议（少返工的三条经验）

1. 先建规范文档再动手改：把这 10 条打印出来贴在迭代任务里，比边改边查效率高得多；MyBatis XML 按上述关键词全局搜索（反引号、LIMIT x,y、IFNULL、GROUP_CONCAT、DATE_FORMAT、ON DUPLICATE、REPLACE INTO、||、双引号字符串、= 0 布尔）。
2. PG 侧开一个"严格检查"测试库跑全量回归：所有 SQL 错误在测试环境集中暴露，别带到预发；重点覆盖分页（第二页以后）、upsert、日期参数化、批量插入 foreach。
3. 保留一份双跑对账清单：分页结果、upsert 后行数据（特别是 REPLACE 改造的表）、聚合拼接结果与 MySQL 逐字段比对。

常见问题

① 改完语法应用报 "relation does not exist" 但表确实建了？

90% 是差异 5：建表用了大写/驼峰被折叠成小写，查询带了双引号或大小写不一致。\dt（psql）看真实表名；根治方案是所有标识符蛇形小写。另外检查 search_path：表建在非 public schema 下，连接默认 schema 不包含它也会报这个错。

② MyBatis-Plus 分页插件要改什么？

配置里把 DbType 从 MYSQL 改成 POSTGRE_SQL：new MybatisPlusInterceptor().addInnerInterceptor(new PaginationInnerInterceptor(DbType.POSTGRE_SQL))。改完插件自动生成 LIMIT ? OFFSET ?；手写 XML 里的分页仍要人工按差异 2 核对。

③ upsert 报 "there is no unique or exclusion constraint matching"

ON CONFLICT (col) 要求 col 上必须有主键或唯一约束/唯一索引。MySQL 的 ON DUPLICATE 会自动检测任意唯一键冲突，PG 必须显式指定一个实际存在的约束。检查：\d t_order 看唯一索引是否建了、字段名是否一致、pgloader 是否漏迁了唯一索引（常见！）。

④ Boolean 字段在 JSON 接口里返回 0/1 还是 true/false，前端不兼容怎么办？

这是序列化层的事不是数据库层：Jackson 配置把 Boolean 序列化为 0/1（或全局 JsonFormat），DB 存 true/false、接口契约保持不变；更推荐推动前端接受标准 JSON 布尔类型。反过来，Java DTO 里该用 Boolean 的字段不要用 Integer，从源头类型对齐。

⑤ 还有哪些高频小差异容易漏？

• now() 没问题但 SYSDATE() 没有 → 换 clock_timestamp()（语句内实时）或 now()（事务开始时间）
• MySQL 反引号转义在 PG 用双引号，字符串里的单引号用 '' 转义（两边一致）
• MySQL AUTO_INCREMENT 手插后继续增长不冲突；PG IDENTITY 默认 ALWAYS 拒绝手插，订正数据用 BY DEFAULT
• LIMIT 不能用在 UPDATE/DELETE 里（MySQL 可以 UPDATE ... LIMIT 100），PG 用 WHERE id IN (SELECT id FROM ... LIMIT 100)
• 注释语法 MySQL 支持 #，PG 不支持，只认 --（后面要空格）和 /* */

⑥ 有没有自动化工具做 SQL 方言转换？

pgloader 负责 DML/数据和大部分 DDL；应用层 SQL 没有可靠的一键工具（语义差异如 REPLACE、UPDATE JOIN 必须人工判断），可以用 SQL 解析器（如 JSqlParser）写脚本做机械替换（反引号→去掉、LIMIT 顺序、函数名），再人工 review 语义类差异。我们的经验：机械替换能消掉 60% 工作量，剩下 40%（ON CONFLICT、INTERVAL 参数化、类型严格性）必须逐条测试。

总结

10 个差异速查卡

1 自增   AUTO_INCREMENT → GENERATED ALWAYS AS IDENTITY（灌完数据 setval 复位序列！）
 2 分页   LIMIT m,n → LIMIT n OFFSET m（参数相反，不报错位，重点测第2页+）
 3 拼接   CONCAT 两边通用直接保留；|| 遇 NULL 得 NULL（MySQL 里 || 是 OR）
 4 日期   DATE_FORMAT('%Y...') → to_char('YYYY...')；INTERVAL '7 days'；
          MyBatis 用 make_interval(days => #{n})
 5 大小写 未加引号折叠为小写 → 全部蛇形小写，order/user/group 保留字要改名
 6 引号   标识符 反引号 → 双引号(最好不用)；字符串只能单引号
 7 连表更新 UPDATE...JOIN → UPDATE...SET...FROM...WHERE（DELETE 用 USING）
 8 upsert INSERT IGNORE→ON CONFLICT DO NOTHING；
          ON DUPLICATE KEY→ON CONFLICT DO UPDATE SET col=EXCLUDED.col；
          REPLACE INTO 无等价物（删+插会丢列），按业务改成部分列更新
 9 聚合   GROUP_CONCAT(x ORDER BY y SEPARATOR ',')
          → string_agg(x, ',' ORDER BY y)；非文本列先 ::text
10 类型   IFNULL→COALESCE；IF()→CASE WHEN；TINYINT(1)→boolean；
          严格类型：varchar=数字 直接报错，参数类型必须对齐

互动话题：你们迁移时哪个差异坑得最久？是 LIMIT 顺序导致的分页错乱，还是 ON CONFLICT 的唯一索引问题？评论区聊聊。

参考资料

• PostgreSQL 官方文档：SQL 语法
• PostgreSQL 文档：INSERT（ON CONFLICT/EXCLUDED）
• PostgreSQL 文档：UPDATE（FROM 子句）
• pgloader 文档：MySQL 默认类型转换规则
• MyBatis-Plus 文档：分页插件 DbType
• PostgreSQL Wiki：标识符与关键字