为什么要迁移越来越多的团队选择从 MySQL 迁移到 PostgreSQL主要驱动力包括- 需要 JSONB、地理空间、数组等高级数据类型- 业务规模增长需要更强的并发处理能力MVCC- 复杂查询性能瓶颈- 更严格的 SQL 标准合规要求本文总结迁移过程中的关键步骤和常见坑帮助你少走弯路。一、语法差异速查表场景MySQLPostgreSQL字符串拼接CONCAT(a, b)a || b或CONCAT(a, b)自增主键AUTO_INCREMENTSERIAL或GENERATED ALWAYS AS IDENTITY限制行数LIMIT 10LIMIT 10相同日期函数NOW()NOW()相同字符串比较默认大小写不敏感默认大小写敏感⚠️布尔值1/0TRUE/FALSE反引号字段名fieldfield双引号GROUP BY允许非聚合字段必须严格聚合⚠️二、最常踩的三个坑坑 1字符串大小写敏感MySQL 默认不区分大小写PostgreSQL 默认区分。-- MySQL 中返回结果 SELECT * FROM users WHERE name alice; -- 能匹配 Alice -- PostgreSQL 中不返回大小写严格匹配 SELECT * FROM users WHERE name alice; -- 匹配不到 Alice -- PostgreSQL 正确写法 SELECT * FROM users WHERE LOWER(name) LOWER(alice); -- 或者使用不区分大小写的操作符 SELECT * FROM users WHERE name ILIKE alice;坑 2GROUP BY 严格模式-- MySQL 中可以运行宽松模式 SELECT name, age, COUNT(*) FROM users GROUP BY name; -- PostgreSQL 报错age 必须出现在 GROUP BY 或聚合函数中 -- 正确写法 SELECT name, MAX(age), COUNT(*) FROM users GROUP BY name;坑 3自增主键语法不同-- MySQL CREATE TABLE posts ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) ); -- PostgreSQL推荐现代写法 CREATE TABLE posts ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR(255) ); -- PostgreSQL兼容旧写法 CREATE TABLE posts ( id SERIAL PRIMARY KEY, title VARCHAR(255) );三、迁移工具推荐pgloader推荐开源工具支持从 MySQL 直接迁移到 PostgreSQL自动转换数据类型。# 安装 sudo apt install pgloader # 执行迁移 pgloader mysql://user:passlocalhost/mydb \ postgresql://user:passlocalhost/mydbpgloader 会自动- 转换AUTO_INCREMENT→SERIAL- 转换TINYINT(1)→BOOLEAN- 转换字符集和排序规则AWS DMS适合生产环境的在线迁移支持最小停机时间的持续同步适合数据量大、业务不能中断的场景。四、迁移后的性能调优迁移完成后需要针对 PostgreSQL 做专项优化-- 1. 更新统计信息迁移后必做 ANALYZE; -- 2. 检查缺失索引 SELECT schemaname, tablename, attname, n_distinct, correlation FROM pg_stats WHERE tablename your_table; -- 3. 查看慢查询 SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;五、迁移检查清单[ ] 字符串比较逻辑已改为大小写敏感[ ] GROUP BY 语句已检查聚合合规性[ ] AUTO_INCREMENT 已替换为 SERIAL 或 IDENTITY[ ] 反引号字段名已改为双引号或去掉引号[ ] BOOLEAN 字段已从 0/1 改为 TRUE/FALSE[ ] 执行 ANALYZE 更新统计信息[ ] 索引已重建并验证[ ] 应用层连接串已更新总结MySQL 到 PostgreSQL 的迁移不是一蹴而就的工作语法差异、大小写敏感和聚合规则是最常见的问题点。建议先在测试环境用 pgloader 做一次完整迁移跑通所有 SQL再逐步切换生产环境。迁移的短期成本换来的是长期更强大的功能和并发能力对于成长中的业务是值得投入的。
从 MySQL 迁移到 PostgreSQL：实战指南与常见坑
为什么要迁移越来越多的团队选择从 MySQL 迁移到 PostgreSQL主要驱动力包括- 需要 JSONB、地理空间、数组等高级数据类型- 业务规模增长需要更强的并发处理能力MVCC- 复杂查询性能瓶颈- 更严格的 SQL 标准合规要求本文总结迁移过程中的关键步骤和常见坑帮助你少走弯路。一、语法差异速查表场景MySQLPostgreSQL字符串拼接CONCAT(a, b)a || b或CONCAT(a, b)自增主键AUTO_INCREMENTSERIAL或GENERATED ALWAYS AS IDENTITY限制行数LIMIT 10LIMIT 10相同日期函数NOW()NOW()相同字符串比较默认大小写不敏感默认大小写敏感⚠️布尔值1/0TRUE/FALSE反引号字段名fieldfield双引号GROUP BY允许非聚合字段必须严格聚合⚠️二、最常踩的三个坑坑 1字符串大小写敏感MySQL 默认不区分大小写PostgreSQL 默认区分。-- MySQL 中返回结果 SELECT * FROM users WHERE name alice; -- 能匹配 Alice -- PostgreSQL 中不返回大小写严格匹配 SELECT * FROM users WHERE name alice; -- 匹配不到 Alice -- PostgreSQL 正确写法 SELECT * FROM users WHERE LOWER(name) LOWER(alice); -- 或者使用不区分大小写的操作符 SELECT * FROM users WHERE name ILIKE alice;坑 2GROUP BY 严格模式-- MySQL 中可以运行宽松模式 SELECT name, age, COUNT(*) FROM users GROUP BY name; -- PostgreSQL 报错age 必须出现在 GROUP BY 或聚合函数中 -- 正确写法 SELECT name, MAX(age), COUNT(*) FROM users GROUP BY name;坑 3自增主键语法不同-- MySQL CREATE TABLE posts ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) ); -- PostgreSQL推荐现代写法 CREATE TABLE posts ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title VARCHAR(255) ); -- PostgreSQL兼容旧写法 CREATE TABLE posts ( id SERIAL PRIMARY KEY, title VARCHAR(255) );三、迁移工具推荐pgloader推荐开源工具支持从 MySQL 直接迁移到 PostgreSQL自动转换数据类型。# 安装 sudo apt install pgloader # 执行迁移 pgloader mysql://user:passlocalhost/mydb \ postgresql://user:passlocalhost/mydbpgloader 会自动- 转换AUTO_INCREMENT→SERIAL- 转换TINYINT(1)→BOOLEAN- 转换字符集和排序规则AWS DMS适合生产环境的在线迁移支持最小停机时间的持续同步适合数据量大、业务不能中断的场景。四、迁移后的性能调优迁移完成后需要针对 PostgreSQL 做专项优化-- 1. 更新统计信息迁移后必做 ANALYZE; -- 2. 检查缺失索引 SELECT schemaname, tablename, attname, n_distinct, correlation FROM pg_stats WHERE tablename your_table; -- 3. 查看慢查询 SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;五、迁移检查清单[ ] 字符串比较逻辑已改为大小写敏感[ ] GROUP BY 语句已检查聚合合规性[ ] AUTO_INCREMENT 已替换为 SERIAL 或 IDENTITY[ ] 反引号字段名已改为双引号或去掉引号[ ] BOOLEAN 字段已从 0/1 改为 TRUE/FALSE[ ] 执行 ANALYZE 更新统计信息[ ] 索引已重建并验证[ ] 应用层连接串已更新总结MySQL 到 PostgreSQL 的迁移不是一蹴而就的工作语法差异、大小写敏感和聚合规则是最常见的问题点。建议先在测试环境用 pgloader 做一次完整迁移跑通所有 SQL再逐步切换生产环境。迁移的短期成本换来的是长期更强大的功能和并发能力对于成长中的业务是值得投入的。
