MySQL 转 pgsql

1. 引言

MySQL 和 PostgreSQL 是两种常用的关系型数据库系统，它们在语法和功能上有一些差异。当需要从 MySQL 数据库迁移到 PostgreSQL 数据库时，可能需要进行一些调整和修改。本文将详细介绍如何将 MySQL 数据库转换为 PostgreSQL 数据库。

2. 数据类型的转换

MySQL 和 PostgreSQL 支持的数据类型并不完全一致，因此在迁移过程中需要进行数据类型的转换。

2.1 数值类型

MySQL 和 PostgreSQL 都支持整数类型和浮点数类型。但在细节上有一些差异：

MySQL 中的 INT 类型对应 PostgreSQL 的 INTEGER 类型；
MySQL 中的 TINYINT 类型对应 PostgreSQL 的 SMALLINT 类型；
MySQL 中的 FLOAT 类型对应 PostgreSQL 的 REAL 类型；
MySQL 中的 DOUBLE 类型对应 PostgreSQL 的 DOUBLE PRECISION 类型。

以下是一个示例 SQL 语句，演示如何将 MySQL 数据库中的表和字段的数据类型转换为 PostgreSQL 数据库中的对应类型：

-- 创建 MySQL 数据库表
CREATE TABLE `example` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50),
  `age` TINYINT,
  `price` FLOAT
);

-- 将 MySQL 数据表转换为 PostgreSQL 数据表
CREATE TABLE "example" (
  "id" INTEGER PRIMARY KEY,
  "name" VARCHAR(50),
  "age" SMALLINT,
  "price" REAL
);

2.2 字符串类型

MySQL 和 PostgreSQL 都支持字符类型，但在某些情况下需要注意差异。

MySQL 中的 VARCHAR 类型对应 PostgreSQL 的 VARCHAR 类型；
MySQL 中的 CHAR 类型对应 PostgreSQL 的 CHAR 类型；
MySQL 中的 TEXT 类型对应 PostgreSQL 的 TEXT 类型。

以下是一个示例 SQL 语句，演示如何将 MySQL 数据库中的表和字段的字符类型转换为 PostgreSQL 数据库中的对应类型：

-- 创建 MySQL 数据库表
CREATE TABLE `example` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50),
  `description` TEXT
);

-- 将 MySQL 数据表转换为 PostgreSQL 数据表
CREATE TABLE "example" (
  "id" INTEGER PRIMARY KEY,
  "name" VARCHAR(50),
  "description" TEXT
);

2.3 日期和时间类型

MySQL 和 PostgreSQL 也有一些差异的日期和时间类型：

MySQL 中的 DATETIME 类型对应 PostgreSQL 的 TIMESTAMP 类型；
MySQL 中的 DATE 类型对应 PostgreSQL 的 DATE 类型；
MySQL 中的 TIME 类型对应 PostgreSQL 的 TIME 类型。

以下是一个示例 SQL 语句，演示如何将 MySQL 数据库中的表和字段的日期和时间类型转换为 PostgreSQL 数据库中的对应类型：

-- 创建 MySQL 数据库表
CREATE TABLE `example` (
  `id` INT PRIMARY KEY,
  `created_at` DATETIME,
  `updated_at` TIMESTAMP
);

-- 将 MySQL 数据表转换为 PostgreSQL 数据表
CREATE TABLE "example" (
  "id" INTEGER PRIMARY KEY,
  "created_at" TIMESTAMP,
  "updated_at" TIMESTAMP
);

3. 数据迁移工具

迁移 MySQL 数据库到 PostgreSQL 数据库的过程中，可以使用一些数据迁移工具来简化操作。

3.1 pgloader

pgloader 是一个功能强大且易于使用的开源工具，它专门用于从各种关系型数据库迁移到 PostgreSQL 数据库。下面是使用 pgloader 工具从 MySQL 数据库迁移到 PostgreSQL 数据库的示例命令：

pgloader mysql://username:password@hostname/database postgresql://username:password@hostname/database

3.2 使用 INSERT INTO 语句手动迁移数据

如果数据量较小且没有大量复杂的业务逻辑，也可以通过手动编写 INSERT INTO 语句来将数据从 MySQL 数据库迁移到 PostgreSQL 数据库。以下是一个示例 SQL 语句，演示如何手动编写 INSERT INTO 语句来迁移数据：

-- 从 MySQL 数据库中选择数据
SELECT * FROM `example`;

-- 手动编写 INSERT INTO 语句将数据迁移到 PostgreSQL 数据库
INSERT INTO "example" ("id", "name", "age", "price")
VALUES
(1, 'John', 25, 10.5),
(2, 'Jane', 30, 12.0),
...

4. 函数和语法的差异

MySQL 和 PostgreSQL 有一些函数和语法上的差异，当进行数据库迁移时需要注意这些差异。

以下是一些常见的函数和语法的示例，演示 MySQL 和 PostgreSQL 之间的差异：

4.1 字符串函数

MySQL 中使用 SUBSTRING_INDEX 函数可以获取指定分隔符分割的字符串的部分内容。在 PostgreSQL 中可以使用 SPLIT_PART 函数来实现相同的功能。示例代码如下：

-- MySQL
SELECT SUBSTRING_INDEX('www.example.com', '.', -2);  -- 输出 "example.com"

-- PostgreSQL
SELECT SPLIT_PART('www.example.com', '.', 2);  -- 输出 "example.com"

4.2 复合条件查询

MySQL 和 PostgreSQL 在复合条件查询上有一些差异。以下是一个示例，演示 MySQL 和 PostgreSQL 中的复合条件查询的差异：

-- MySQL
SELECT * FROM `example` WHERE (`name`, `age`) IN (('John', 25), ('Jane', 30));

-- PostgreSQL
SELECT * FROM "example" WHERE ROW("name", "age") IN (('John', 25), ('Jane', 30));

5. 数据库迁移的测试和验证

在完成数据库迁移后，需要对迁移后的 PostgreSQL 数据库进行测试和验证，以确保数据的准确性和一致性。

可以编写一些测试脚本和查询来检查迁移后的数据是否完整和正确。以下是一个示例，演示如何编写查询来验证迁移后的数据：

-- 检查迁移后的表是否存在
SELECT EXISTS(SELECT 1 FROM "example");

-- 检查迁移后的数据是否一致
SELECT COUNT(*) FROM "example";

-- 检查迁移后的数据是否完整
SELECT * FROM "example" LIMIT 10;