MySQL 迁 PostgreSQL：MyBatis 类型映射与方言差异的七个实战坑

分类：数据库与中间件 · 作者：管理员 · 发布于 2026-10-16 · 阅读 0 · 标签：PostgreSQL / MyBatis / MySQL迁移 / 类型映射 / 踩坑实战

一、迁移日的噩梦：代码一行没改，接口全红

把一个跑了三年的电商中台从 MySQL 迁到 PostgreSQL，最痛苦的不是搬数据，而是迁移后第一天线上疯狂报错：有的接口参数为 null 直接抛 Error setting null for parameter，有的插入语句主键回填拿不到 ID，有的批量更新整段失败。代码一行没改，为什么换个数据库就崩？因为 MyBatis 的 SQL 与类型映射，比你想的更依赖具体数据库。

本文以一套典型的 Spring Boot + MyBatis 服务为例，梳理 MySQL 迁 PG 过程中最容易踩的七个类型与语法坑，并给出可直接落地的写法。

二、七个坑与对应解法

坑 1：参数为 null 时，jdbcType=OTHER 在 PG 驱动下报错

MyBatis 默认的 jdbcTypeForNull=OTHER。MySQL 驱动对 setNull(i, Types.OTHER) 容忍度很高，但换到 PostgreSQL 驱动后，部分场景会抛：

Error setting null for parameter #6 with JdbcType OTHER.
Try setting a different JdbcType for this parameter or a different
jdbcTypeForNull configuration property.
Cause: java.sql.SQLException: 无效的列类型：1111

解法 A（全局兜底）：在 application.yml 中把 null 的默认 JDBC 类型改为 NULL：

mybatis:
  configuration:
    jdbc-type-for-null: 'NULL'

解法 B（局部显式）：对确定可空的参数逐个标注 jdbcType，比全局配置更可控：

<insert id="createOrder">
  INSERT INTO app_order (order_no, user_id, remark)
  VALUES (#{orderNo,jdbcType=VARCHAR}, #{userId,jdbcType=BIGINT}, #{remark,jdbcType=VARCHAR})
</insert>

建议：新项目直接配 jdbc-type-for-null: NULL；老项目若不想动全局行为，优先在批量插入/更新这类高频 SQL 上显式标注。

坑 2：TINYINT(1) 换成 PG 后，Boolean 读写错位

MySQL 里 TINYINT(1) 既是布尔又是小整数。迁移到 PG 后，按语义映射为 boolean 或 smallint 都可能落地，但你的实体字段可能还是 Integer：

// 实体字段
private Integer deleted; // 原 MySQL TINYINT(1)，PG 建表成了 boolean

此时查询会报 column "deleted" is of type boolean but expression is of type integer，或映射结果全是 0/1 错乱。统一口径：PG 侧布尔列就用 Boolean 字段；无法改表的情况下，在 resultMap 里对列做转换：

<resultMap id="orderMap" type="OrderDO">
  <result property="deleted" column="deleted"
          typeHandler="org.apache.ibatis.type.BooleanTypeHandler"/>
</resultMap>

同时注意：PG 的 boolean 不接受 0/1 字面量，SQL 里写 WHERE deleted = 1 会直接报 operator does not exist: boolean = integer，必须写成 WHERE deleted = true 或 WHERE deleted。

坑 3：自增主键回填，useGeneratedKeys 仍可用但要注意序列

MySQL 的 AUTO_INCREMENT 在 PG 里有 serial 与 identity 两种建法。MyBatis 的 useGeneratedKeys="true" keyProperty="id" 两种都兼容，无需改 XML。但有两个新坑：

PG 序列是独立对象，INSERT 显式给了 id 时不会自动推进序列，后续自增会主键冲突。迁移数据后务必执行 SELECT setval('app_order_id_seq', max(id)) FROM app_order; 对齐序列。
批量插入返回主键：MySQL 习惯 foreach 一条 SQL 插多行，PG 同样支持多行 values，但主键回填依赖驱动正确实现，建议实测：

<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO app_order (order_no, user_id)
  VALUES
  <foreach collection="list" item="item" separator=",">
    (#{item.orderNo}, #{item.userId})
  </foreach>
</insert>

坑 4：ON DUPLICATE KEY UPDATE 语法不兼容

MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE 在 PG 中不存在，等价写法是 INSERT ... ON CONFLICT (uk_code) DO UPDATE SET ...。迁移时逐条改写：

-- MySQL 原写法（不可直接用）
INSERT INTO app_user (mobile, nickname) VALUES ('13800000000', 'alice')
ON DUPLICATE KEY UPDATE nickname = VALUES(nickname);

-- PG 等价写法
INSERT INTO app_user (mobile, nickname) VALUES ('13800000000', 'alice')
ON CONFLICT (uk_mobile) DO UPDATE SET nickname = EXCLUDED.nickname;

注意两点：ON CONFLICT 必须指定冲突的唯一约束名；VALUES() 函数要换成 EXCLUDED 伪表。MyBatis 里这段直接写在 XML 即可，参数占位符写法不变。

坑 5：日期时间默认值，CURRENT_TIMESTAMP 牛头不对马嘴

MySQL 的 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 习惯很普遍，PG 的对应写法是 DEFAULT now()，且没有「更新时自动刷新」的列属性，需要触发器或在应用层显式 set。DATETIME 类型在 PG 中推荐映射为 timestamp，Java 侧统一 LocalDateTime，避免 java.util.Date 时区换算带来 8 小时偏移。

CREATE TABLE app_order (
  id         bigserial PRIMARY KEY,
  created_at timestamp NOT NULL DEFAULT now(),
  updated_at timestamp NOT NULL DEFAULT now()
);

MyBatis 中对应实体字段保持 LocalDateTime，插入时由应用层统一赋 LocalDateTime.now()，不要在 SQL 里拼时间函数，便于测试与日志排查。

坑 6：字符串函数分家：GROUP_CONCAT 与 REGEXP

这两组函数迁移时最容易漏，SQL 平时跑得少，一上线报表就炸：

GROUP_CONCAT(x) → string_agg(x, ',')，注意排序写法从 GROUP_CONCAT(x ORDER BY id) 变成 string_agg(x, ',' ORDER BY id)。
REGEXP '^abc' → ~ '^abc'；REGEXP_REPLACE 两库都有但参数略有差异。
IFNULL(a, b) → COALESCE(a, b)（PG 也支持 COALESCE，迁移时统一即可）。
LIMIT a, b 分页语法 → LIMIT b OFFSET a。MyBatis 分页插件（如 PageHelper）内部已适配，但手写分页 SQL 必须改。

坑 7：统计聚合返回类型，Long 别接成 Integer

MySQL 的 COUNT(*) 返回 BIGINT，PG 同样返回 bigint，正常情况没问题。真正容易翻车的是聚合列：SUM(amount) 在 MySQL 里 DECIMAL 直接映射 BigDecimal，PG 则可能被驱动映射为 Numeric，只要 resultType 写成 map 或字段类型不明确，取值时就容易出现 ClassCastException。建议：统计类查询显式指定 resultType DTO，字段用 BigDecimal/Long，并在 SQL 里显式 cast：SUM(amount)::numeric AS total_amount。

三、附：类型映射速查表

MySQL	PostgreSQL	Java 推荐类型
BIGINT	bigint	Long
INT / INTEGER	integer	Integer
TINYINT(1)	boolean / smallint	Boolean / Short
DECIMAL(p,s)	numeric(p,s)	BigDecimal
DATETIME / TIMESTAMP	timestamp	LocalDateTime
VARCHAR / TEXT	varchar / text	String
BLOB	bytea	byte[]（配合自定义 TypeHandler）
JSON	jsonb	String 或 Jackson TypeHandler

JSON 列是另一个大坑：PG 的 jsonb 比较、索引、更新操作符（->>、#>）与 MySQL 完全不同，建议先用 varchar 过渡或自定义 TypeHandler 序列化，后续再专项优化。

四、落地思路：先类型审计，再 SQL 扫描，最后灰度

类型审计：导出两库元数据比对，把 TINYINT(1)、DATETIME、BLOB、无符号整型逐列过一遍，确定 PG 目标类型与 Java 字段类型。
SQL 扫描：全局搜索 ON DUPLICATE KEY、GROUP_CONCAT、REGEXP、IFNULL、LIMIT a, b、FOR UPDATE（PG 锁语义略有差异）等关键字，逐条改写。
配置修改：jdbc-type-for-null 全局兜底，nullable 参数显式标注 jdbcType。
回归验证：重点回归插入主键回填、批量写入、唯一键冲突更新、统计报表四个场景，必要时在测试环境用双库并行跑同一批用例对比结果。

迁移从来不是「换连接串 + 导数据」这么简单。把类型映射和方言差异提前列成清单，能省掉上线后一整周的排查时间。