MySQL 到 PostgreSQL 迁移实战：从 jdbcType 映射到 SQL 方言兼容性

分类：数据库与中间件 · 作者：管理员 · 发布于 2026-09-21 · 阅读 0 · 标签：PostgreSQL / MySQL / 数据库迁移 / jdbcType / SQL方言

一、为什么值得迁

很多团队从 MySQL 转向 PostgreSQL 的核心驱动：PostgreSQL 对复杂查询的支持（CTE、窗口函数、GIN/GiST 索引）、更强的 JSON 处理能力、以及开源协议更友好的商业授权。但迁移过程绝非改个连接串那么简单 —— 两个数据库虽然都是关系型，SQL 方言和数据类型体系有显著差异。

本文将基于 Spring Boot + MyBatis-Plus 的真实迁移经验，逐项梳理关键差异与解决策略。

二、JDBC 连接与驱动

第一步替换驱动和连接串：

# application.yml
spring:
  datasource:
    # MySQL 原配置
    # url: jdbc:mysql://localhost:3306/app_db?useSSL=false
    # driver-class-name: com.mysql.cj.jdbc.Driver
    
    # PostgreSQL 新配置
    url: jdbc:postgresql://localhost:5432/app_db
    driver-class-name: org.postgresql.Driver
    hikari:
      pool-name: AppPool
      maximum-pool-size: 15
      connection-timeout: 30000

踩坑 1：PostgreSQL JDBC 驱动默认的 prepareThreshold 为 5 —— 即执行同一条 SQL 5 次后才真正使用服务端预编译。高并发场景下这个默认值可能导致数据库 CPU 飙升，建议显式设置为 1：

spring.datasource.hikari.data-source-properties.prepareThreshold=1

踩坑 2：MySQL 的 useSSL=false 在 PG 驱动中不存在，PG 默认禁用 SSL；如需加密需配置 ssl=true&sslmode=require 并配证书。

三、jdbcType 映射 —— 最隐蔽的坑

MyBatis 的 XML 或注解中声明的 jdbcType 在 MySQL 和 PostgreSQL 下含义不同，不改就会抛出异常或数据写入错误。

MyBatis jdbcType	MySQL 映射	PostgreSQL 映射	处理方案
INTEGER	INT	INTEGER	兼容，无需改动
BIGINT	BIGINT	BIGINT	兼容
VARCHAR	VARCHAR	VARCHAR	兼容
TINYINT	TINYINT(1)≈boolean	不存在，用 SMALLINT 或 BOOLEAN	改为 SMALLINT 或 BOOLEAN 类型
DATE	DATE	DATE（无零值支持）	PG 不支持 '0000-00-00'，插入前需处理零值
TIMESTAMP	DATETIME/TIMESTAMP	TIMESTAMP（带时区/不带时区）	MyBatis 默认 jdbcType=TIMESTAMP 映射为 `timestamptz`，建议统一用 `TIMESTAMP(0) WITHOUT TIME ZONE`
BLOB	BLOB	BYTEA	兼容，但 PG 的 BYTEA 无大小限制，查询效率略低于 TOAST 机制
CLOB/TEXT	TEXT/LONGTEXT	TEXT	兼容，PG 的 TEXT 无上限

最典型的是 TINYINT 问题：MySQL 的 TINYINT(1) 常用来存布尔值（0/1），但在 PG 中不存在 TINYINT 类型，MyBatis 的 jdbcType=TINYINT 会报 ERROR: column is of type smallint but expression is of type integer。解决方案：

方案 A：建表用 SMALLINT，MyBatis 保持 jdbcType=TINYINT 但参数会自动转 integer，PG 可隐式转换
方案 B：改用 PG 原生 BOOLEAN 类型，MyBatis 用 jdbcType=BOOLEAN，Java 字段保持 Boolean

// 方案 B 示例
public class Product {
    private Boolean enabled;  // PG 对应 BOOLEAN 类型
    // getter/setter 省略
}

四、SQL 方言差异

1. 分页语法

-- MySQL
SELECT * FROM product LIMIT 10 OFFSET 20;

-- PostgreSQL（语法完全相同）
SELECT * FROM product LIMIT 10 OFFSET 20;

好在这部分 MyBatis-Plus 的 Page 对象自动处理，无需改动。

2. 字符串拼接

-- MySQL
SELECT CONCAT(last_name, ', ', first_name) FROM users;

-- PostgreSQL
SELECT last_name || ', ' || first_name FROM users;
-- 或者兼容写法
SELECT CONCAT(last_name, ', ', first_name) FROM users;

PG 9.1+ 也支持 CONCAT()，所以这两个函数在 PG 中兼容。但 || 在 PG 中遇到 NULL 整体为 NULL，而 MySQL 的 CONCAT 会忽略 NULL，需特别注意。

3. GROUP BY 严格模式

MySQL 默认 ONLY_FULL_GROUP_BY 可关闭，允许 SELECT 非聚合列不写进 GROUP BY。PostgreSQL 则严格要求：SELECT 中所有非聚合列必须出现在 GROUP BY 中。

-- 在 MySQL 可以运行
SELECT order_id, user_id, SUM(amount)
FROM orders GROUP BY order_id;

-- PostgreSQL 会报错，必须写
SELECT order_id, user_id, SUM(amount)
FROM orders GROUP BY order_id, user_id;

4. UPDATE…JOIN 语法差异

-- MySQL 写法
UPDATE stock s JOIN order_item o ON s.sku_id = o.sku_id
SET s.quantity = s.quantity - o.quantity
WHERE o.order_id = ?;

-- PostgreSQL 必须用 FROM 子句
UPDATE stock s
SET quantity = s.quantity - o.quantity
FROM order_item o
WHERE s.sku_id = o.sku_id AND o.order_id = ?;

五、索引与性能调优

1. 索引命名冲突：MySQL 允许同一数据库下多个表使用同名索引；PostgreSQL 要求索引名在 schema 内全局唯一。迁移时如果脚本建同名索引会报错，需要在迁移 SQL 中为每个索引加表名前缀。

2. 全文索引：MySQL 用 MATCH...AGAINST 配合 FULLTEXT 索引；PG 用 tsvector + tsquery + GIN 索引，功能强大得多但不兼容，需要改写查询：

-- PG 全文搜索
SELECT * FROM articles
WHERE to_tsvector('simple', title || ' ' || content)
      @@ plainto_tsquery('simple', '分布式锁');
-- 需配合 GIN 索引使用

3. VACUUM 机制：PG 的 MVCC 实现依赖 VACUUM 回收死元组，新建表必须配置 autovacuum 参数，否则表会持续膨胀。迁移后第一个月应监控表大小变化，必要时调高 autovacuum_vacuum_scale_factor。

六、迁移工具与流程

推荐用 pgloader（可读取 MySQL 元数据自动化迁移 schema 和数据）：

# 安装 pgloader
sudo apt install pgloader

# 执行迁移（从 MySQL 到 PG）
pgloader mysql://user:pass@mysql_host/app_db \
         pgsql://user:pass@pg_host/app_db

# 只迁移结构不带数据
pgloader --dry-run mysql://... pgsql://...

迁移后的验证脚本：逐表对比行数、主键自增值、关键字段最大值/最小值，确保数据完整。

七、总结

MySQL 到 PostgreSQL 迁移并不可怕，但需要逐个击破以下要点：

JDBC 驱动与连接池参数：prepareThreshold 务必调整为 1
jdbcType 映射：TINYINT 换 SMALLINT/BOOLEAN，TIMESTAMP 明确时区
SQL 方言：GROUP BY 严格模式、UPDATE JOIN 换 FROM 子句、NULL 拼接行为
索引与维护：索引全局唯一命名、全文索引改写、VACUUM 监控

建议先在 Staging 环境全量迁移并运行至少一周的业务压测，逐一修复不兼容点后再切生产。对于大型表，可以使用 pg_transport（PostgreSQL 14+）或逻辑复制实现无缝割接。