MySQL 迁到 PostgreSQL 时踩到的类型与语法坑

2026-10-15 数据库阅读约 7 分钟

起因很简单：一个系统里同时跑着 MySQL 和 PostgreSQL 两套库，部署到新环境就得多装一个组件、多维护一套备份。干脆把小的那个合并到 PG 里去。

一开始以为只是换个 JDBC 驱动、改个连接串的事。实际做下来，建表语句几乎每一处都需要改写。

1. 自增主键

MySQL 用 AUTO_INCREMENT，PG 对应的是 BIGSERIAL：

-- MySQL
id BIGINT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL
id BIGSERIAL PRIMARY KEY

BIGSERIAL 本质上是个语法糖，会顺带创建一个 bigint 列、 bigint 序列，以及一个 DEFAULT nextval(...)。所以它只能用在建表语句里，不能给已有列「加」上去。

2. 小心序列没跟上数据

这一条不是语法问题，是迁移时最容易踩的数据问题： 如果用显式 id 导入历史数据，序列是不会自动前进的。 之后第一条 INSERT 就会撞主键冲突。

-- 导入数据后必须手动对齐序列
SELECT setval(
    pg_get_serial_sequence('your_table', 'id'),
    GREATEST((SELECT COALESCE(MAX(id), 1) FROM your_table), 1)
);

我当时的做法是把这段写进迁移脚本，对每张表都执行一遍。忘了这一步的话，系统的「读」全都正常，只有「写」会失败 —— 测试时如果只点开了几个列表页，很容易漏掉。

3. 整型与布尔

MySQL	PostgreSQL	说明
`TINYINT`	`SMALLINT`	PG 没有 `TINYINT`。`SMALLINT` 能被 JDBC 正常映射到 Java 的 `Integer`
`TINYINT(1)` 当布尔用	`BOOLEAN`	如果原来就是 0/1 语义，可以直接换成 `BOOLEAN`，但注意 ORM 的字段类型要跟着改
`DATETIME`	`TIMESTAMP`	PG 的 `TIMESTAMP` 默认不带时区，等价于 MySQL 的 `DATETIME`

我这次选了最小改动：TINYINT 全换成 SMALLINT，保持 Java 侧还是 Integer，不用改实体类。

4. ON UPDATE CURRENT_TIMESTAMP 没有对应写法

MySQL 里这个很方便：

updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
           ON UPDATE CURRENT_TIMESTAMP

PG 没有这个语法。有两种做法：一是在业务代码里手动 set，二是建一个触发器统一处理。我选了后者，好处是不用改变「把维护时间戳写进业务逻辑」这个坏习惯 —— 也就不用在每个 UPDATE 的地方都记得加一行。

CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 每张需要自动更新时间的表挂一个
DROP TRIGGER IF EXISTS trg_touch_your_table ON your_table;
CREATE TRIGGER trg_touch_your_table
    BEFORE UPDATE ON your_table
    FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

注意 PG 12 的 CREATE TRIGGER 不支持 IF NOT EXISTS。 要让脚本可重复执行，得先 DROP TRIGGER IF EXISTS 再创建。

5. 索引名是 schema 级唯一的

MySQL 里索引名只在表内唯一，所以很多人习惯每张表都叫 uk_member。PG 不行 —— 索引名在整个 schema 内必须唯一。

-- MySQL 里很常见，PG 会报错
CREATE UNIQUE INDEX uk_member ON table_a (member);
CREATE UNIQUE INDEX uk_member ON table_b (member);   -- 冲突

-- PG 要各自起名
CREATE UNIQUE INDEX uk_table_a_member ON table_a (member);
CREATE UNIQUE INDEX uk_table_b_member ON table_b (member);

6. 导出数据：mysqldump 的参数

数据量不大的话，直接导成 INSERT 语句最省事。但 mysqldump 默认输出的反引号和 MySQL 专有注释 PG 不认。

网上常见的建议是加 --compatible=postgresql，但我在 MySQL 8.2 上试的时候直接报 Invalid mode to --compatible: postgresql。可用的做法是：

mysqldump -uUSER -pPASS \
  --no-create-info --skip-extended-insert \
  --skip-quote-names --skip-add-locks --skip-disable-keys \
  --compact --default-character-set=utf8mb4 \
  your_database > data.sql

关键是 --skip-quote-names，它负责去掉反引号。 --compact 去掉注释和 LOCK TABLES 之类的语句。

7. 对象属主：一个不太直观的坑

我用超级用户建了库和表，然后想让业务账号自己管理这些表，于是授了 GRANT ALL ON ALL TABLES。这没问题，但接下来要清空表重新导入时，出错了：

ERROR: must be owner of sequence xxx_id_seq

原因是 TRUNCATE ... RESTART IDENTITY 和 setval() 需要的是属主权限，不是普通的 DML 授权。 GRANT 给不了这个。

得单独改属主：

ALTER TABLE  public.your_table OWNER TO your_role;
ALTER SEQUENCE public.your_seq  OWNER TO your_role;

如果表多，用个循环一次性处理：

DO $$
DECLARE r record;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname='public' LOOP
        EXECUTE format('ALTER TABLE public.%I OWNER TO your_role', r.tablename);
    END LOOP;
    FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname='public' LOOP
        EXECUTE format('ALTER SEQUENCE public.%I OWNER TO your_role', r.sequencename);
    END LOOP;
END $$;

8. 代码层面其实没改什么

这次运气不错：持久层用的是 MyBatis-Plus 的 wrapper，几乎全是方言无关的调用。

LIMIT 1 —— PG 一样支持，不用改
没用到分页插件，所以不需要配 DbType
@TableId(type = IdType.AUTO) —— 配 BIGSERIAL 正常工作

真正要改的只有 pom.xml 的驱动和 application.yml 里的连接串。

一点体会： 迁移的成本基本都在「建表语句」和「数据搬迁」上，业务代码几乎不动。所以如果用的是 ORM 而不是满屏手写 SQL，这类迁移会比想象的轻松很多 —— 反过来说，如果项目里有大量 MySQL 专有函数（DATE_FORMAT、 IFNULL、GROUP_CONCAT 之类），工作量就会上去。

检查清单

迁完之后我是按这几条逐项过的：

连接池日志里确认驱动是 org.postgresql.jdbc.PgConnection
每张表的行数与源库一致
关键业务记录逐行 diff（不只是比行数）
中文字段抽查，没有出现乱码
插入一条新记录，确认序列没有撞主键
执行一次 UPDATE，确认 updated_at 确实变了