MySQL → PostgreSQL 迁移 Skill#

概览#

本 Skill 总结了将 Java（Spring Boot + MyBatis/MyBatis-Plus）项目从 MySQL 迁移到 PostgreSQL 的完整经验，涵盖五类核心改动，以及各类运行时报错的根因与修复方法。

---

一、JDBC 连接配置#

application.yaml 改动#

yaml

# MySQL（原）
spring.datasource.url: jdbc:mysql://host/dbname
validation-query: SELECT 1 FROM DUAL

# PostgreSQL（改后）
spring.datasource.url: jdbc:postgresql://host/dbname?currentSchema=your_schema
validation-query: SELECT 1

关键点：

• currentSchema=your_schema 指定默认 schema，避免每条 SQL 都加 schema 前缀
• SELECT 1 FROM DUAL 是 MySQL/Oracle 方言，PG 直接用 SELECT 1

---

二、SQL 方言转换速查表（MyBatis XML）#

类别MySQL 写法PostgreSQL 写法备注空值处理IFNULL(a, b)``COALESCE(a, b)标准 SQL，推荐日期格式化DATE_FORMAT(d, fmt)``TO_CHAR(d, fmt)格式符略有差异日期加减DATE_ADD(d, INTERVAL 1 DAY)``d + INTERVAL '1 day'PG 用运算符当前日期CURDATE()``CURRENT_DATE当前时间NOW()``CURRENT_TIMESTAMPPG 也支持 NOW()，可保留类型转换-时间CAST(x AS DATETIME)``CAST(x AS TIMESTAMP)PG 无 DATETIME 类型类型转换-日期DATE(x)``CAST(x AS DATE)Upsert-忽略INSERT IGNORE INTO``INSERT INTO ... ON CONFLICT DO NOTHINGUpsert-更新INSERT ... ON DUPLICATE KEY UPDATE``INSERT INTO ... ON CONFLICT (col) DO UPDATE SET ...多表更新UPDATE t1 INNER JOIN t2 ON ... SET t1.col=...``UPDATE t1 SET col=... FROM t2 WHERE ...见下方详解

UPDATE JOIN → UPDATE FROM 详解#

sql

-- MySQL
UPDATE orders o
INNER JOIN users u ON o.user_id = u.id
SET o.name = u.name
WHERE o.status = 1;

-- PostgreSQL
UPDATE orders o
SET name = u.name
FROM users u
WHERE o.user_id = u.id
  AND o.status = 1;

规律： PG 把 JOIN 的表放到 FROM 子句， WHERE 里写关联条件。

---

三、数据类型陷阱#

BIT(1) 类型与逻辑删除#

MySQL 的 bit(1) 迁移到 PG 后仍是 BIT / BIT(1) 类型，但 PG 严格禁止 BIT 与整数直接比较。

报错：

text

operator does not exist: bit = integer

两处修复：

① MyBatis-Plus 逻辑删除配置（application.yaml）：

yaml

# 原（MySQL 兼容）
mybatis-plus.global-config.db-config.logic-delete-value: 1
mybatis-plus.global-config.db-config.logic-not-delete-value: 0

# 改后（PG BIT literal）
mybatis-plus.global-config.db-config.logic-delete-value: "B'1'"
mybatis-plus.global-config.db-config.logic-not-delete-value: "B'0'"

② XML 手写 SQL 中的 deleted 字段：

sql

-- MySQL / 错误写法
WHERE deleted = 0
WHERE deleted = 1

-- PostgreSQL 正确写法
WHERE deleted = B'0'
WHERE deleted = B'1'

⚠️ 注意： MyBatis-Plus 自动生成的 deleted=0 和 XML 里手写的都要改，两处独立，不要遗漏。建议用全局搜索： deleted = 0 / deleted = 1，统计 XML 文件中所有出现位置后批量替换。

---

四、自增主键 / Sequence#

MySQL 的 AUTO_INCREMENT 在 PG 中对应 SEQUENCE。

MyBatis-Plus @KeySequence#

java

// Java DO 类上标注
@KeySequence("demo_entity_seq")
public class DemoEntityDO { ... }

运行时 MyBatis-Plus 会执行：

sql

SELECT nextval('demo_entity_seq')

常见报错：

text

ERROR: relation "demo_entity_seq" does not exist

修复：在对应 schema 下创建 Sequence：

sql

-- 对齐现有数据的最大 ID，避免主键冲突
CREATE SEQUENCE your_schema.demo_entity_seq
  START WITH 10001      -- 从当前最大 id + 1 开始
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

最佳实践： 迁移数据后，用 SELECT MAX(id) FROM table 确认最大 ID，再设置 Sequence 的起始值。

---

五、GROUP BY 严格模式#

PG 严格遵循 SQL 标准： SELECT 中所有 非聚合列 必须出现在 GROUP BY 中。MySQL 可通过 sql_mode 去掉 ONLY_FULL_GROUP_BY 来绕过，但 PG 没有对应的宽容模式。

报错：

text

ERROR: column "d.flag_col" must appear in the GROUP BY clause
or be used in an aggregate function

修复策略：

情况解决方案列的值在组内唯一（由业务保证）加入 GROUP BY列是枚举/状态值，取任意值均可MAX(col) 或 MIN(col)列是标志位，有任意一行满足即为真BOOL_OR(col) 或 MAX(col)sql

-- 原 MySQL 写法（SELECT 了非聚合列）
SELECT o.id, d.flag_col, d.status_col,
       MIN(d.amount_col) as amount_col
FROM demo_order o
LEFT JOIN demo_order_detail d ON o.id = d.order_id
GROUP BY o.id;

-- PG 修复写法
SELECT o.id,
       MAX(d.flag_col) as flag_col,      -- 取最大值
       MAX(d.status_col) as status_col,  -- 有任意在售即为在售
       MIN(d.amount_col) as amount_col
FROM demo_order o
LEFT JOIN demo_order_detail d ON o.id = d.order_id
GROUP BY o.id;

---

六、包名 / Namespace 重构（MyBatis）#

迁移时建议将 MyBatis 的包路径和 namespace 从 dal.mysql 改为 dal.pg，保持语义清晰。

涉及的修改点：

1. src/main/java 下的目录： dal/mysql/ → dal/pg/
2. XML 文件中的 namespace： dal.mysql.XxxMapper → dal.pg.XxxMapper
3. Java 文件中的 import 语句
4. application.yaml 中的日志监控包名配置

---

七、迁移检查清单#

text

[ ] JDBC URL 改为 PG 格式，指定 currentSchema
[ ] validation-query 去掉 FROM DUAL
[ ] IFNULL → COALESCE（全局搜索替换）
[ ] DATE_FORMAT → TO_CHAR
[ ] DATE_ADD(...INTERVAL) → date + INTERVAL '...'
[ ] CURDATE() → CURRENT_DATE
[ ] CAST(x AS DATETIME) → CAST(x AS TIMESTAMP)
[ ] DATE(x) → CAST(x AS DATE)
[ ] INSERT IGNORE → INSERT ... ON CONFLICT DO NOTHING
[ ] UPDATE ... JOIN → UPDATE ... FROM ... WHERE
[ ] deleted = 0/1 → deleted = B'0'/B'1'（XML 手写 SQL）
[ ] MyBatis-Plus logic-delete-value 改为 B'1' / B'0'
[ ] 为每个 @KeySequence 在 PG 中创建对应 SEQUENCE
[ ] 确认所有 SEQUENCE 起始值 > 表中当前最大 ID
[ ] 检查 GROUP BY：SELECT 中非聚合列必须在 GROUP BY 或用聚合函数包裹
[ ] 包名 / namespace 从 dal.mysql 改为 dal.pg（可选）

---

八、常见运行时报错速查#

报错关键词根因修复operator does not exist: bit = integerBIT 列与整数比较改为 B'0' / B'1'``relation "xxx_seq" does not existSequence 未创建在对应 schema 建 Sequencemust appear in the GROUP BY clauseSELECT 非聚合列未在 GROUP BY加入 GROUP BY 或用聚合函数SELECT 1 FROM DUAL 报错DUAL 是 Oracle/MySQL 方言改为 SELECT 1``function ifnull does not existIFNULL 是 MySQL 方言改为 COALESCE``function date_format does not existDATE_FORMAT 是 MySQL 方言改为 TO_CHAR