本文目录导读：
MySQL到PostgreSQL迁移实战指南：用脚本实现数据无缝过渡
目录导读
- 迁移前的准备工作：数据评估与环境检查
- 核心脚本设计思想：逐表迁移与类型映射
- 导出MySQL数据为通用格式
- 转换SQL语法与数据类型
- 导入PostgreSQL并验证完整性
- 常见问题与脚本优化技巧
- 问答环节：高频问题解答
迁移前的准备工作
在进行数据库迁移之前，首先需要明确两个关键点：数据量级和结构复杂度，MySQL与PostgreSQL虽然都遵循SQL标准，但在数据类型、索引机制、函数实现等方面存在显著差异，脚本迁移的核心思路是“先解构再重构”——将MySQL数据导出为中间格式（如CSV或通用SQL）,再通过适配脚本转换为PostgreSQL兼容形式。
环境检查清单
- 确认PostgreSQL版本（建议使用14+以获得更好的兼容性）
- 检查MySQL中是否使用了PostgreSQL不支持的存储引擎（如MyISAM）
- 统计表数量、数据总量、字段类型分布
注意：如果包含存储过程、触发器或视图，需额外处理DDL转换,单纯的脚本迁移更适合纯数据迁移场景。
核心脚本设计思想
脚本迁移通常采用以下三层架构：
- 提取层：从MySQL导出表结构和数据，优先使用
mysqldump搭配--no-create-info和--skip-triggers等参数 - 转换层：使用Python或Perl脚本处理字段映射（如
TINYINT(1)→BOOLEAN、AUTO_INCREMENT→SERIAL） - 加载层：使用PostgreSQL的
COPY命令批量导入，比逐条INSERT效率提升10倍以上
伪代码示例：
def mysql_to_pg_type(mysql_type):
mapping = {
'tinyint(1)': 'boolean',
'int': 'integer',
'varchar': 'text',
'datetime': 'timestamp',
# 更多类型映射...
}
return mapping.get(mysql_type.lower(), mysql_type)
导出MySQL数据为通用格式
推荐做法：使用mysqldump+CSV双通道
# 1. 仅导出表结构（需手动微调） mysqldump -h localhost -u root -p --no-data mydb > schema_mysql.sql # 2. 导出数据为CSV（按表分割，避免单文件过大） mysql -h localhost -u root -p -e "SELECT * FROM users" mydb > users.csv
为什么不用原生工具？
虽然pgloader可以直接迁移，但遇到复杂场景（如MySQL的ON UPDATE CURRENT_TIMESTAMP、嵌套事务）容易失败,脚本方式提供了更高的容错性和可定制性。
转换SQL语法与数据类型
关键转换规则表
| MySQL类型 | PostgreSQL类型 | 注意事项 |
|---|---|---|
TINYINT(1) |
BOOLEAN |
需转换0/1为false/true |
AUTO_INCREMENT |
SERIAL |
需重置序列值 |
TEXT字段有CHARSET |
TEXT |
移除字符集声明 |
DATETIME |
TIMESTAMP |
PostgreSQL默认不带时区 |
ENUM |
VARCHAR+CHECK |
或用自定义类型 |
脚本转换工具推荐
- pgloader：适合标准类型，但需手动处理边缘情况
- pg-converter（开源）：支持批量脚本生成
- 自建Python脚本：最灵活，可定制日志和重试逻辑
导入PostgreSQL并验证完整性
高效导入脚本模板
# 使用COPY命令（比INSERT快10-50倍）
psql -h localhost -U postgres -d newdb -c "\COPY users FROM '/path/users.csv' WITH CSV HEADER;"
# 修复序列
psql -h localhost -U postgres -d newdb -c "SELECT setval('users_id_seq', (SELECT max(id) FROM users));"
验证步骤
- 行数对比：
SELECT COUNT(*) FROM users与MySQL原库对比 - 随机抽样：检查10条记录的字段值是否一致
- 索引重建：避免PostgreSQL自动创建重复索引
常见问题与脚本优化技巧
内存优化
- 分批导入：每10000行使用
COMMIT，避免内存溢出 - 使用
UNLOGGED TABLE临时加快导入，完成后改为LOGGED
编码问题
MySQL默认utf8mb4，PostgreSQL使用UTF8,需在脚本中设置：
PGOPTIONS='--client-encoding=UTF8' psql -d newdb
事务处理
迁移大量数据时,禁用自动提交：
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
问答环节：高频问题解答
Q1：脚本迁移会不会丢失数据？
答：只要做好三个验证——字段数量、行数、随机抽样，数据丢失概率小于0.1%,建议保留原始CSV文件作为回退方案。
Q2：迁移过程中如何处理大表（超过1000万行）？
答：使用分片策略，例如按时间戳或ID范围划分区间，每个区间独立导出导入脚本，并记录断点位置（如保存最后成功的ID值）。
Q3：是否需要专业工具？比如AWS DMS或Debezium？
答：一次性迁移且数据量小于100GB时，脚本方案性价比最高，如果需要持续同步（如不停机迁移）,才考虑CDC工具。
Q4：迁移后数据库性能下降怎么办？
答：检查三点：索引是否全部创建（PostgreSQL索引名与MySQL不同）、统计信息是否更新（运行ANALYZE）、查询计划是否优化（特别是LIKE和GROUP BY语句）。
Q5：脚本迁移的最大风险是什么？
答：SQL方言差异是最隐蔽的风险，例如MySQL的LIMIT 10,20在PostgreSQL中需改为LIMIT 20 OFFSET 10，以及GROUP BY的严格模式（PostgreSQL要求非聚合字段必须在GROUP BY子句中）。
用脚本迁移MySQL到PostgreSQL，核心是“理解差异、分步处理、验证兜底”，对于99%的企业场景，一个编写良好的迁移脚本配合必要的类型映射表，即可实现10万行/分钟以上的迁移速度，建议先在小规模表上测试完整流程,再全量执行。
本文基于MySQL 8.0和PostgreSQL 15版本编写，所有脚本示例均可在Linux/macOS环境运行，如需Windows适配，建议使用WSL或Cygwin环境。
