从 MySQL 迁移到 PostgreSQL 有哪些数据类型转换注意事项

MySQL 迁移 PostgreSQL 时，数据类型转换的核心风险在于无符号整数溢出、布尔值映射歧义以及时区处理差异。建议优先扫描 schema 中所有UNSIGNED字段和TINYINT(1)字段，并在应用层验证时间戳序列化和 JSON 路径查询语法。

先说结论：迁移成功的关键在于提前识别 MySQL 特有类型（如 UNSIGNED、TINYINT）并转换为 PostgreSQL 兼容类型，同时修正 SQL 语法差异。

适合场景：异构数据库迁移、去 MySQL 化、需要复杂查询或 JSON 能力的场景。
重点看：无符号整数范围、布尔值定义、时间戳时区、自增主键实现。
别忽略：引号规范（反引号 vs 双引号）、函数名称差异（IFNULL vs COALESCE）、JSON 操作符变化。

命令速用版

以下是常见数据类型的映射转换 SQL 示例，可直接用于建表脚本参考：

-- 数值类型：MySQL INT UNSIGNED 需转为 BIGINT 防止溢出
CREATE TABLE users (
    id BIGINT PRIMARY KEY, -- 对应 MySQL INT UNSIGNED
    age INTEGER            -- 对应 MySQL INT
);

-- 布尔类型：MySQL TINYINT(1) 转为 BOOLEAN
CREATE TABLE settings (
    is_active BOOLEAN DEFAULT FALSE -- 对应 MySQL TINYINT(1)
);

-- 自增主键：MySQL AUTO_INCREMENT 转为 SERIAL 或 IDENTITY
CREATE TABLE orders (
    id SERIAL PRIMARY KEY -- 或 GENERATED ALWAYS AS IDENTITY
);

-- 时间类型：注意时区
CREATE TABLE logs (
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

为什么会这样

MySQL 和 PostgreSQL 在设计哲学和 SQL 标准遵循程度上存在根本差异，导致类型系统不完全兼容。MySQL 为了易用性支持无符号整数和非标准引号，而 PostgreSQL 更严格遵循 SQL 标准且类型系统更丰富。例如 PostgreSQL 没有原生无符号整数类型，直接映射会导致正数范围减半；时间戳处理上 PostgreSQL 默认区分带时区和不带时区，而 MySQL 的DATETIME通常不带时区信息。这些底层差异决定了迁移不能仅靠工具自动转换，必须人工介入评估。

分步处理

按照以下流程执行类型转换，可降低数据丢失风险：

扫描源库结构：使用查询语句找出所有UNSIGNED列和TINYINT(1)列，评估数据最大值是否超过 PostgreSQL 有符号整数上限。
转换定义脚本：将AUTO_INCREMENT改为SERIAL或IDENTITY，将VARCHAR长度限制根据实际需求调整（PostgreSQL 不强制检查长度性能更好）。
修正 SQL 语法：全局替换反引号`为双引号"或去除，将IFNULL改为COALESCE，FIND_IN_SET改为ANY(string_to_array(...))。
调整应用代码：检查 ORM 映射配置，特别是 Java 中LocalDateTime与TIMESTAMPTZ的兼容性，JSON 字段读取语法从->'$.key'改为->>'key'。

怎么验证是否生效

迁移完成后，执行以下检查确认数据类型转换正确：

数据一致性：对比源库和目标库的行数计数，抽样检查大整数字段是否溢出。
类型检查：在 PostgreSQL 执行\d+ table_name查看列类型是否符合预期。
应用日志：观察应用启动日志是否有类型转换异常（如PSQLException: Cannot convert the column of type TIMESTAMPTZ）。
功能测试：重点测试涉及排序、时间筛选和 JSON 查询的业务接口。

常见坑

以下场景在迁移中极易出错，需谨慎处理：

无符号整数溢出：MySQL 的INT UNSIGNED最大值约 42 亿，PostgreSQL 的INTEGER最大值约 21 亿。若直接映射，大于 21 亿的数据会报错。解决方案是统一映射为BIGINT。
引号规范冲突：MySQL 允许使用反引号`包裹字段名，PostgreSQL 使用双引号"包裹标识符，字符串值必须用单引号'。代码中残留的反引号会导致语法错误。
JSON 查询语法：MySQL 使用->'$.key'获取 JSON 值，PostgreSQL 使用->>'key'获取文本值或->'key'获取 JSON 对象。混合使用会导致查询结果为空或类型错误。
空字符串与 NULL：MySQL 中空字符串和 NULL 在某些场景下行为相似，PostgreSQL 严格区分。需检查代码中是否依赖空字符串作为默认值。

常见问题

MySQL 的 TINYINT(1) 在 PostgreSQL 中对应什么类型？

对应BOOLEAN类型。MySQL 常用TINYINT(1)存储布尔值，迁移时应直接转换为 PostgreSQL 的BOOLEAN，应用层需适配 true/false 而非 0/1。

自增主键 AUTO_INCREMENT 怎么迁移？

改为SERIAL或GENERATED ALWAYS AS IDENTITY。SERIAL是旧语法，IDENTITY是 SQL 标准语法，建议新表使用IDENTITY。

迁移后时间查询报错怎么办？

检查是否混淆了TIMESTAMP和TIMESTAMPTZ。若应用使用LocalDateTime，数据库字段建议用TIMESTAMP；若需时区支持，应用层需使用ZonedDateTime或Date。

JSON 字段迁移后查询变慢吗？

不会，但需建立索引。PostgreSQL 的JSONB类型支持 GIN 索引，查询性能通常优于 MySQL 的 JSON 文本存储，但必须在迁移后手动创建索引。

参考来源

MySQL 迁移 PostgreSQL 避坑指南：数据类型转换那些事儿 (含常见错误解决方案)
超全 MySQL 转换 PostgreSQL 数据库迁移实战指南
项目从 MySQL 切换 PostgreSQL，踩了太多的坑!!!追悔莫及
MySQL 转 PostgreSQL 全流程方案：从工具到实践的完整指南
Mysql 转 PostgreSQL 注意事项及说明【转】