PostgreSQL迁移MySQL全攻略：类型映射、SQL适配与数据校验实践

作过几次 PostgreSQL 往 MySQL 的迁移之后我最大的感触是这两样东西虽然都叫关系型数据库但真到了迁移这一步基本等于把一座房子从左手挪到右手结构可以平移但水电管道、墙体承重、门窗朝向全都得重排。尤其是那些PG 里跑得好好的 SQL和PG 里顺手就用的类型到 MySQL 里动不动就报错、隐式转换、性能跳水每一个都能让人心态崩一下。这篇东西不打算写成说明书式的流水账。我会把 PG 迁 MySQL 这条链路按真实项目里的推进顺序拆开写从要不要迁到怎么迁结构再到迁完怎么验中间穿插我在实际项目里踩过的具体坑和对应的排查思路。如果你想做的是 MySQL 往 PG 迁也可以参考方向反过来但很多坑是对称的。1. 为什么要逆向迁移PG 到 MySQL 的业务动机与决策前提很多人听到PG 迁 MySQL第一反应是这不是降级吗PostgreSQL 从功能丰富度、扩展性、标准兼容性上都比 MySQL 强不少为什么要逆向操作1.1 团队技术栈与运维能力的现实约束最普遍的情况是团队技术栈高度绑定 MySQL。一家公司从早期业务起就全员 MySQLDBA 的经验、中间件的运维体系、监控告警、备份恢复流程全部围绕 MySQL 建设。某个新项目或者并购来的项目用了 PG业务本身不复杂但让运维团队去维护一套 PG 集群学习成本和故障响应成本会持续存在。这种情况下把数据迁到 MySQL 是在用一次性的迁移成本置换长期的运维成本。还有一类场景是云厂商的托管数据库策略。很多云平台的 RDS for MySQL 有成熟的只读实例、读写分离、就近接入方案而 PG 实例在某些地域或价位段支持没那么完整。业务要全球化部署、要拉就近节点MySQL 的配套更顺这时候迁库反而成了业务扩张的必经步骤。1.2 生态依赖ORM、中间件和数据分析工具链后端框架和 ORM 对数据库的适配程度也不一样。Java/Spring 生态、Python/Django 生态通常两者都支持但不少开源中间件——比如一些数据同步组件、报表引擎、低代码平台——默认针对 MySQL 开发对 PostgreSQL 的支持停留在能用但没充分测试的状态。接入这些中间件时PG 用户要么自己改代码要么干脆把库迁到 MySQL 换取开箱即用。1.3 迁移前必须回答的三个问题在真正动手之前先回答三个问题任何一个答不上来都建议缓一缓数据量大不大停机窗口够不够全量导出导入的速度受限于网络带宽和目标库的写入能力TB 级以上的数据量如果只有一个两小时窗口基本不可能完成。业务能不能接受短时只读或停服PG 的物理复制和 MySQL 的主从复制是异构的做不了跨库实时同步除非引入 Debezium 这类 CDC 中间件所以最稳妥的方案是停机维护一段时间。应用层改动量有没有评估过如果你的代码里有大量 PG 自有函数date_trunc、to_char、string_agg或者用了WITH RECURSIVE、LATERAL JOIN迁移到 MySQL 8.0 以上还好8.0 以下会非常痛苦。这三个问题决定的是迁移策略是完全停机一次性迁还是走 CDC 工具做增量同步然后切换。多数中小项目选前者够用且可控。2. 结构迁移前必须做好的功课类型、函数、约束的差异图谱很多人在迁移时直接拿工具把表结构转换一下就开始导数据结果导到一半报错返回去改表结构再导再错来回好几轮。问题出在少了一步先花半天时间把两种数据库的差异点列出来心里有数之后再动工具。2.1 类型系统的差异是最容易踩坑的暗礁PG 的类型系统在关系型数据库里属于豪华配置MySQL 则精简得多。下面是常见的类型映射表建议直接在项目里存一份转结构的时候照着对PostgreSQLMySQL备注SERIAL/BIGSERIALINT AUTO_INCREMENT/BIGINT AUTO_INCREMENT注意列必须定义为键或加上索引TIMESTAMPTZDATETIME/TIMESTAMPMySQL 的TIMESTAMP有 2038 年上限强烈建议用DATETIMEBOOLEANTINYINT(1)/BOOLEANMySQL 的 BOOLEAN 本质是 TINYINT查询结果会变成 0/1UUIDCHAR(36)/BINARY(16)存储层没有原生对应需要应用层转换JSON/JSONBJSON类型名一样但函数和操作符差异巨大TEXTLONGTEXT索引长度限制不一样注意前缀索引NUMERIC(p,s)/DECIMAL(p,s)DECIMAL(p,s)基本兼容ARRAY[]无需要拆表或者用 JSON 序列化替代ENUMENUMPG 的 ENUM 可以追加值MySQL 的 ENUM 修改代价高建议直接换VARCHAR CHECK2.2 索引、约束与自增主键的注意事项PG 的索引设计默认为 B-tree但支持表达式索引、部分索引WHERE 条件、GIN 索引。MySQL 8.0 之前只能做普通 B-tree 索引。迁移时要仔细检查UNIQUE约束对 NULL 的处理PG 里多个NULL不冲突可以插入多行全为NULL的唯一键MySQL 里也允许但不同版本行为不一致8.0 以上没问题5.7 部分场景会出现问题最好测试一下。CHECK约束PG 用得很勤MySQL 8.0.16 之前CHECK约束被解析但不强制如果目标库版本低于 8.0.16数据校验逻辑可能在迁移后静默失效。自增主键的连续性问题PG 的SERIAL依赖序列迁移到 MySQL 的AUTO_INCREMENT后主键值可能不连续、插入失败时空号更多对业务无影响但如果上游系统拿主键做业务含义比如按主键范围分片需要重新评估。2.3 SQL 函数与操作符的替换清单这里列一个我项目里常用的替换表每一项都是实际遇到过的PostgreSQLMySQL说明NOW()/CURRENT_TIMESTAMPNOW()差异不大date_trunc(month, ts)DATE_FORMAT(ts, %Y-%m-01)需注意返回类型to_char(ts, YYYY-MM-DD HH24:MI:SS)DATE_FORMAT(ts, %Y-%m-%d %H:%i:%s)格式符完全不同COALESCEIFNULL/COALESCE两者都支持STRING_AGG(col, ,)GROUP_CONCAT(col SEPARATOR ,)用途一样但语法不同colxxx 字符串拼接LIMIT x OFFSET y同MySQL 长得一样ILIKE/~LIKE不区分大小写排序规则MySQL 默认排序规则下 LIKE 本身不区分大小写DISTINCT ON无对应需要改写通常用子查询或窗口函数RETURNINGMySQL 8.0 无直接对应需要拿LAST_INSERT_ID()或者改写事务逻辑WITH RECURSIVEMySQL 8.0 支持语法一致老版本需要换成存储过程循环3. 工具选型与结构迁移实操从 pg_dump 到目标库 DDL 的生成类型和函数差异摸清楚之后就可以动手了。结构迁移的核心目标是把 PG 里的表、索引、约束、视图、函数、触发器等对象转成 MySQL 能接受的形式。这一步做得越干净后面导数据越省事。3.1 结构迁移的几条路线与真实体验我试过纯手写、半自动脚本、第三方工具三种路线纯手写 DDL适合表数量少于 30 张、依赖关系不复杂的项目。直接看 PG 的\d 表名输出然后对照上面的映射表手写 MySQL DDL。好处是完全可控坏处是大表多的时候工作量非常大。基于pg_dump --schema-only转换先用 pg_dump 导出 PG 的建表语句然后用正则或者脚本把类型关键字替换成 MySQL 的。这条路线的问题是 PG 语法和 MySQL 语法差别太大正则替换只能处理简单类型遇到CHECK、DEFAULT nextval、COMMENT ON这些就失效了。你可以做一版转换脚本但不要期望它一次跑通。商业化/开源迁移工具像 pg2mysql、Navicat 的迁移工具都试过。Navicat 的表结构转换做得相对成熟能处理常见类型映射但遇到JSONB、数组、分区表、PG 特有的函数默认值比如DEFAULT gen_random_uuid()的时候依旧会卡壳需要手动补 DDL。3.2 手工校准 DDL 的几个关键点无论用哪种路线手工校准是躲不开的。以下几个点几乎每次都要处理默认值函数PG 里DEFAULT now()可以MySQL 8.0 里DEFAULT CURRENT_TIMESTAMP语法不同PG 里DEFAULT gen_random_uuid()在 MySQL 里要改成应用传入或用UUID()函数但要注意函数调用放在 DEFAULT 里有限制。分区表结构PG 的原生声明式分区的 DDL 和 MySQL 的分区语法完全不一样比如PARTITION BY RANGE的表达式有差异。视图定义视图内部如果使用了 PG 专属函数导出之后必须逐个手工改写。MySQL 对视图定义里的CHECK OPTION、算法限定有自己的限制。3.3 一个实际项目的结构迁移顺序我通常按先表后约束、先索引后视图、最后函数存储过程的顺序处理导出 PG 的所有表定义批量映射为 MySQL 的CREATE TABLE先建表不带外键。建立主键和唯一索引顺手检查自增列是否设置正确。建立普通索引和复合索引。注意 MySQL 的索引键长度限制在 utf8mb4 下VARCHAR(255) 的索引会超过 767 字节需要确认数据库版本和行格式否则要么减长度要么加前缀索引。外键和检查约束放最后。外键的建立需要所有相关表都已存在且数据类型完全一致。视图、存储过程、触发器最后再处理牵涉到函数替换的部分单独安装调试。这套顺序能最大程度减少建表时引用不存在的表这类低级错误。4. 数据迁移实战全量导出、导入与数据校验的完整链路结构就位之后进入数据迁移环节。根据项目体量不同我会在纯 SQL 导出导入和用 ETL/迁移工具两者之间选择。团队没有专职 DBA 时我一般建议先试工具工具搞不定再写脚本。4.1 用 pg_dump 导出数据还是直接用工具如果目标 MySQL 表已经通过前面的步骤建好最简单的数据迁移方式是pg_dump只导出数据导出成 CSV 或者 COPY 格式再通过LOAD DATA INFILE导入 MySQL。# 导出 PG 表数据为 CSV带表头 pg_dump --dbnamepostgresql://user:passhost:5432/source_db \ --tablepublic.users \ --data-only \ --formatplain \ --column-inserts \ --fileusers.sql # 或者直接 COPY 出 CSV psql -h host -U user -d source_db -c \copy public.users TO /tmp/users.csv WITH (FORMAT CSV, HEADER true, DELIMITER ,)CSV 方式更适合大数据量。拿到 CSV 之后通过mysqlimport或者直接LOAD DATA LOCAL INFILE导入。LOAD DATA LOCAL INFILE /tmp/users.csv INTO TABLE users CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY IGNORE 1 LINES;关键心得导 CSV 之前先统一字符集。PG 导出时默认按数据库编码输出如果源库是UTF8目标 MySQL 表也必须是utf8mb4否则中文直接乱码。连接字符串里显式指定client_encodingUTF8导入端用CHARACTER SET utf8mb4兜底。4.2 数据转换过程中最常见的三类错误日期时间格式不兼容PG 的timestamp输出可能是2024-05-01 12:34:56.78908MySQL 的DATETIME不支持时区偏移需要先把数据归一化成2024-05-01 12:34:56再导入。处理方式是在导出 SQL 里用to_char(col, YYYY-MM-DD HH24:MI:SS.US)格式化。布尔值表示差异PG 导出为t/f或true/falseMySQL 的 TINYINT(1) 需要1/0。建议导出时用CASE WHEN转换别在导入端折腾。NULL 与空字符串混淆PG 的TEXT字段空字符串和NULL是两种状态MySQL 的字段如果定义为NOT NULL DEFAULT 导入时\N或NULL可能导致报错或静默变成空串。导入之前用sed或脚本统一处理或者导出时用COALESCE转成默认值。4.3 数据校验不抽检早晚要返工数据导完之后必须做校验。最朴素也最有效的两种方式基于 count 的验数对每张核心表分别数出 PG 和 MySQL 的行数对比。这个只能查漏查不了错。基于哈希的抽样校验对关键业务表抽 5%-10% 的数据按某几个关键列做 MD5 拼接两边算出同样的哈希值再对比。我常用的是mysqldump的单表哈希和 PG 端的pg_md5配合或者写个小脚本跑查询。实际操作里我的校验 SQL 一般是-- MySQL 侧 SELECT MD5(GROUP_CONCAT(CONCAT_WS(|, id, name, created_at) ORDER BY id)) AS row_hash FROM users;-- PostgreSQL 侧 SELECT MD5(STRING_AGG(CONCAT_WS(|, id::text, name, created_at::text), ORDER BY id)) AS row_hash FROM users;两边算出来的值一致这条链路基本就稳了。注意GROUP_CONCAT有group_concat_max_len上限如果单表数据量大需要先SET SESSION group_concat_max_len 1024 * 1024 * 1024;或者分片一批批校验。5. 应用层适配SQL 方言和 ORM 的改造清单数据过去了表也能查了不代表迁移就结束了。真正让项目跑起来才是重头戏而应用层的 SQL 适配往往比数据迁移更耗时。很多团队在排期时只给数据迁移留时间忽略了 SQL 兼容性改造最后硬生生拖了一周。5.1 手写 SQL 的规范改造如果你的项目用的是 MyBatis 这类靠手写 SQL 为主的框架先做一遍全局搜索重点关注以下模式注意点一||拼接符的使用PG 里a || b是字符串拼接到了 MySQL 里默认||被解释为 OR。如果你的代码里有类似的语句必须改成CONCAT(a, b)。更隐蔽的是 PG 里用||拼接数组的行为到 MySQL 里完全没有对应物只能改业务逻辑。注意点二分页查询的写法大多数人用LIMIT ? OFFSET ?两边都支持问题不大。但要注意 MyBatis 的分页插件生成方言时是按数据库类型自动切换的如果你把数据库连接切到 MySQL 了插件一般能正确处理。真正的问题是那些手写LIMIT拼字符串的代码OFFSET后面跟的参数类型如果不对MySQL 会报错。注意点三隐式类型转换的差异PG 对类型比较严格WHERE id 123如果 id 是整数且 123 是字符串PG 通常能隐式转换MySQL 则更宽松但宽松带来了索引失效风险。比如WHERE user_id 12345若user_id是 BIGINTMySQL 会尝试转换但如果列上有函数或者前导模糊查询索引就废了。建议统一代码里参数绑定的类型别用字符串拼数字。5.2 ORM 项目的适配经验使用 Hibernate、JPA、Django ORM、SQLAlchemy 这类 ORM 的项目有个好处大部分 CRUD 的 SQL 是 ORM 自动生成的切数据库后字段映射和基础 SQL 基本能自动适配。但要注意几个小坑自增主键的获取方式JPA 的GenerationType.IDENTITY在 PG 和 MySQL 下表现不同PG 用序列、MySQL 用自增。切换后如果实体类主键生成策略没有跟着改插入时可能会报null id错误。JSON 字段的处理方式PG 的JSONB在 JPA 里通常映射为String或自定义类型MySQL 的 JSON 列走 JDBC 时返回的可能是String或字节数组序列化逻辑要重新测。分页方言Django 的Paginator在底层会根据数据库适配 SQL基本不用动。但如果你直接用了RawSQL或者extra()就要检查这些原生 SQL。5.3 存储过程与触发器的最终归宿PG 的存储过程语言是 PL/pgSQLMySQL 的存储过程语法是复合的 SQL 语法两者差得非常多。如果项目里存储过程数量超过十个建议直接考虑在应用层重写这些逻辑因为你把 PG 的存储过程逐行翻译成 MySQL 语法的工作量、测试工作量、以及后续维护成本可能比重写还要高。触发器同理。如果实在要保留注意几个常见差异PG 的RETURNING在触发器里很常用MySQL 没有。PG 的表名、列名在函数体内可以动态引用MySQL 的预处理语句语法不同。PG 的异常处理块EXCEPTION WHEN othersMySQL 里是DECLARE EXIT HANDLER FOR SQLEXCEPTION。6. 迁移后的验证、性能调优与回滚方案数据迁完、应用改完业务重新上线不等于万事大吉。我在迁移项目里最重视的是灰度验证和回滚护栏这部分如果设计得早心理压力和后半夜被叫醒的概率会大幅下降。6.1 灰度切换与双写设计对于不能长时间停服的业务设计一个双写期非常有价值。做法是先把代码切到 MySQL同时保留一个逻辑把增量写操作的日志实时同步到 PG或者反过来。观察一段时间的读写路由指标、报错日志、慢查询日志。稳定后再关掉 PG 的写入口保留只读查询一段时间确保历史数据也一致。双写方案听起来简单实际落地要考虑 DB 操作顺序、事务边界、失败重试。最常见的坑是双写时 MySQL 写入失败但 PG 写入成功又没有及时补偿导致数据不一致等切换完成后才暴露。所以双写开启前就先准备好补偿脚本定时比对差异并自动修复。6.2 慢查询和锁等待的性能体检PG 迁移到 MySQL 后SQL 执行计划基本完全重构。即使数据量一样索引使用情况也可能天差地别。上线前用EXPLAIN ANALYZE跑一遍核心查询EXPLAIN ANALYZE SELECT u.id, u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.created_at 2024-01-01 ORDER BY u.id DESC LIMIT 20;重点观察几个指标type是不是ALL或index如果是说明查询没走索引需要检查字段的隐式转换和索引是否失效。rows扫描行数与实际返回行数差距是否过大。MySQL 8.0 的EXPLAIN ANALYZE有实际执行时间对比迁移前后的接口响应时间变化偏差超过 30% 的接口重点排查。锁等待是另一大隐患。PG 的READ COMMITTED和 MySQL 的默认隔离级别InnoDB 下是REPEATABLE READ在高并发写入时行为不同之前 PG 上没有出现过的死锁到了 MySQL 上可能因间隙锁Gap Lock而频繁出现。遇到Deadlock found when trying to get lock; try restarting transaction报错时优先检查事务是否过长超过 1 秒的事务最容易撞锁。是否有范围条件的UPDATE/DELETE语句间隙锁范围变大。不同事务对多张表的加锁顺序是否不一致。6.3 回滚预案的落地方式迁移项目里最怕的是切了 MySQL 之后发现不行又切不回 PG。我通常留三份东西作为回滚兜底PG 源库的完整只读备份迁移期间源库处于只读状态或改动极小保留一个完整备份任何时刻都能原地恢复。增量同步日志双写期间的同步日志或者 binlog/WAL 的导出记录确保回滚时能把 MySQL 上的增量变更再回灌到 PG。切换开关在应用层做一个数据库路由开关通过配置中心远程切换 MySQL/PG不需要重新发版。这样回滚只是一个配置变更而不是一次紧急上线。真到了要回滚的时候先切流量再同步数据最后核对一致性。顺序错了容易造成双端同时写入对账会很痛苦。7. 实战中那些没写在文档里的坑最后分享几个我在具体项目里踩过、且常规文档基本不会提的坑。每一个都是真实环境里被逼出来的经验建议收藏。7.1 大小写敏感问题刚开始查数据一切正常一接应用就翻车PG 里未加引号的表名列名会转成小写存储MySQL 的表名在 Linux 上区分大小写而列名不区分。如果把 PG 导出时的驼峰列名直接搬到 MySQL 里SQL 里查UserName可能没问题但某些 ORM 自动生成的带反引号的 SQL 就会变成需要实际操作UserName。最省心的做法是迁移前把所有表名列名统一改成小写加下划线风格应用层映射做一次约定。7.2 字符集排序规则的隐性影响MySQL 的utf8mb4_general_ci和utf8mb4_0900_ai_ci排序规则对中英文的排序结果不一样尤其影响ORDER BY的中文排序和LIKE查询。如果源 PG 库的排序规则是en_US.UTF-8迁移后同样一条查询中文的排序位置可能会变。排序无关紧要的表不管但涉及分页、排行榜、去重逻辑的表建议先确认目标排序规则并写一遍回归测试。7.3 不要过度相信迁移工具的完全自动我遇到过一个项目团队用了某个迁移工具的一键迁移功能结果表结构、数据都过去了但每个AUTO_INCREMENT的auto_increment值都归零了因为工具没迁移序列的当前值。上线后第一笔业务插入就直接主键冲突跟源库的历史 ID 撞了。解决办法是在迁移工具的步骤之外多做一个记录当前序列最大值并手工设置 AUTO_INCREMENT 初始值的步骤ALTER TABLE users AUTO_INCREMENT 100000;我的体会是数据库迁移这事没有银弹。工具能帮你省力但最终的责任人还是你自己。把每一步的校验做扎实把回滚方案提前准备好把 SQL 方言的差异表存在团队共享文档里迁移成功之后的维护期会轻松很多。