跳转到主要内容

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
PostgreSQL 和 MySQL 共享 ANSI SQL 语法,但在足够多的方面存在分歧,因此迁移需要仔细关注数据类型、默认行为和 SQL 方言差异。大部分工作是模式转换,以及修复依赖 PostgreSQL 特有功能的查询。 本指南介绍从 PostgreSQL 迁移到 MySQL,这是迁移到 VillageSQL 之类 MySQL 兼容服务器时的常见方向——VillageSQL 是一个开源的 MySQL 即插即用替代品,并在其之上添加了 PostgreSQL 风格的扩展框架。正因为有这个框架,本指南不仅涵盖您的模式和数据,还为您的 PostgreSQL 扩展安排了落脚之处;请参阅下面的迁移您的扩展。如果您是反方向迁移数据,下面的类型映射和方言差异反过来同样适用,但迁移工具部分不适用。

我应该使用哪种方法

  • 一次性切换——使用 pg_dump 和 LOAD DATA INFILE 进行转储/恢复。请参阅下面的迁移工具和迁移方法。
  • 迁移期间源数据库需要保持在线并可写,或需要持续同步——改为使用带 JDBC sink 连接器的 Debezium 进行持续复制,而不是一次性转储。

迁移工具

pg_dump + 手动模式转换是主要路径,除了标准的 PostgreSQL 和 MySQL 客户端工具之外不需要任何东西:
pg_dump 的模式输出使用 PostgreSQL 语法(SERIAL、BOOLEAN、PostgreSQL 特有的 CREATE TABLE 子句),因此模式文件需要按照下面的类型映射表手动转换,才能在 MySQL 上运行。没有任何工具能在每种情况下都自动且正确地完成这种转换——请把它当作大多数迁移的默认路径。 带 JDBC sink 连接器的 Debezium(debezium.io,Apache-2.0 许可证,积极维护中)读取 PostgreSQL 的预写日志,并通过 Kafka Connect 将变更流式传输到 MySQL,提供持续复制而不是一次性复制。当源数据库在迁移期间必须保持在线并可写,或者您需要的是持续同步而不是一次切换时,它是正确的工具。它需要一个运行中的 Kafka 和 Kafka Connect 集群,这比一次性迁移所需的基础设施多得多——对于单次切换,请改用 pg_dump。

数据类型映射

序列与 AUTO_INCREMENT

PostgreSQL 使用序列(独立的对象)来生成自增值。MySQL 使用 AUTO_INCREMENT 作为列属性: PostgreSQL:
MySQL 等价写法:
在 MySQL 中获取最后插入的 ID:SELECT LAST_INSERT_ID();(等价于 PostgreSQL 的 currval() 或 RETURNING id)。

SQL 方言差异

字符串连接:
字符串引号:
只有启用了 ANSI_QUOTES SQL 模式,MySQL 才接受用双引号括起的标识符。 ILIKE(不区分大小写的 LIKE):
在 utf8mb4_0900_ai_ci(MySQL 8.x 的默认值)或类似的 _ci 排序规则下,MySQL 的字符串比较默认不区分大小写。如果您需要区分大小写的匹配,请使用 _bin 排序规则。 RETURNING 子句:
LIMIT / OFFSET:
模式与数据库: PostgreSQL 在一个数据库内使用模式(mydb.public.orders)。MySQL 把”模式”和”数据库”当作同义词——不存在数据库内的模式这一概念。PostgreSQL 所说的模式,在 MySQL 中就是数据库。 UNIQUE 索引中的 NULL 处理: PostgreSQL 允许唯一列中存在多个 NULL 值(NULL 彼此不相等)。MySQL(InnoDB)同样允许唯一索引中存在多个 NULL——行为相同。 CTE: MySQL 支持 CTE,包括递归 CTE。请参阅 MySQL 公用表表达式。 窗口函数: MySQL 支持窗口函数。请参阅 MySQL 中的窗口函数。 存储过程和函数: PostgreSQL 用 PL/pgSQL 编写过程逻辑,带有异常处理和丰富的控制流。MySQL 使用自己的过程式 SQL 方言,不支持 PL/pgSQL,并且在客户端工具中需要更改 DELIMITER,这样过程体内的分号才不会被解析为语句结束:
从 PL/pgSQL 到 MySQL 过程式语法没有自动转换——请逐个手动重写过程,并预计异常处理逻辑(EXCEPTION WHEN ... 块)需要最多的返工,因为 MySQL 的 DECLARE ... HANDLER 工作方式不同。请参阅 MySQL 中的存储过程。 标识符的大小写敏感性: PostgreSQL 把不带引号的标识符折叠为小写,并把带引号的标识符("MyTable")视为区分大小写。MySQL 的规则不同:数据库名和表名是否区分大小写,取决于 lower_case_table_names 系统变量和底层文件系统,而不是引号。列名、索引名和存储例程名在查找时从不区分大小写,与平台无关。不要假设一个依赖区分大小写的 PostgreSQL 表名的模式在迁移后行为完全一致——请检查目标服务器上的 lower_case_table_names。

没有 MySQL 等价物的功能

一些 PostgreSQL 功能没有直接的等价物:

迁移您的扩展

PostgreSQL 依靠 pgcrypto、pg_trgm 和 uuid-ossp 等扩展来提供其本身不附带的功能。原版 MySQL 没有等价的机制来添加这类功能——但 VillageSQL 有:VillageSQL 扩展框架(VEF)允许您使用 INSTALL EXTENSION 将自定义 SQL 函数和类型安装到服务器中。VillageSQL 的一些扩展正是上述 PostgreSQL 扩展的直接移植: 如果您的 PostgreSQL 数据库依赖此处未列出的扩展,它可能还没有 VillageSQL 等价物——在断定该功能彻底不可用之前,请先查看 VillageSQL 文档中的扩展目录。如果该依赖是您自己团队编写的自定义 PostgreSQL 扩展,VEF 同样是移植它的工具:vsql-extension-builder 技能会引导 AI 编码代理在 VillageSQL 上重建自定义扩展,从需求分析一直到经过测试、附带文档的 .veb 包。

迁移方法

  1. 从 PostgreSQL 导出模式(pg_dump --schema-only),并按照上面的类型映射手动转换每个表。
  2. 从 PostgreSQL 导出数据为 CSV(COPY table TO '/tmp/table.csv' CSV HEADER)。
  3. 使用 LOAD DATA INFILE 或 mysqlimport 将数据加载到 MySQL。
  4. 测试查询——找出所有 PostgreSQL 特有的语法并重写。
  5. 验证行数和校验和——对每个表执行 SELECT COUNT(*);抽查关键行。

常见问题

MySQL 支持像 PostgreSQL 的 ON CONFLICT 那样的 UPSERT 吗?

支持,但语法不同。请参阅 MySQL 中的 UPSERT。MySQL 的 INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE INTO 涵盖了与 PostgreSQL 的 ON CONFLICT DO UPDATE 和 ON CONFLICT DO NOTHING 相同的用例。

如何在 MySQL 中处理 PostgreSQL 的 BOOLEAN 列?

使用 TINYINT(1)。用 1 表示 true,0 表示 false。大多数 MySQL 客户端库和 ORM 会自动处理这一点,并将 TINYINT(1) 列呈现为布尔值。您也可以使用 BIT(1),但 TINYINT(1) 得到的工具支持更广泛。

故障排除

另请参阅