三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

MySQL到PostgreSQL迁移实战：语法差异、数据类型与架构调整全解析

发布时间：2026/8/5 6:00:37 三亩地 编程学习日记
MySQL到PostgreSQL迁移实战：语法差异、数据类型与架构调整全解析

1. 从MySQL到PostgreSQL：一次数据库迁移的深度复盘

最近几年，在技术选型上，越来越多的团队开始将目光投向PostgreSQL。无论是其强大的SQL标准支持、丰富的内置数据类型（如JSONB、数组），还是对复杂查询的卓越优化能力，都让它在很多场景下比MySQL更具吸引力。我所在的团队，就在去年完成了一个核心业务系统从MySQL 5.7到PostgreSQL 14的完整迁移。整个过程并非一帆风顺，从语法差异、功能特性到运维习惯，我们踩了不少坑，也积累了大量实战经验。这篇文章，我就以一个亲历者的身份，把我们在迁移过程中遇到的那些“拦路虎”以及我们的解决方案，毫无保留地分享出来。无论你是正在规划迁移，还是单纯想了解两者差异，希望这篇超过5000字的深度复盘，能给你带来实实在在的帮助。

迁移数据库，远不止是改改连接字符串那么简单。它更像是一次对应用架构、SQL编写习惯和运维体系的全面审视。我们的项目是一个典型的Web应用，数据层之前重度依赖MySQL的一些“方言”和特定行为。迁移的目标是PostgreSQL，我们期望获得更好的事务一致性、更强大的分析查询能力以及更可控的并发性能。整个迁移周期历时近三个月，涉及上百张表、数千条SQL语句和数十个存储过程的重构。下面，我就分几个核心板块，详细拆解我们遇到的问题和应对策略。

2. 语法与功能差异：那些“看起来一样却不一样”的坑

这是迁移初期最直接、也最琐碎的一类问题。很多在MySQL下运行良好的SQL，到了PostgreSQL会直接报语法错误或产生不同的结果。我们必须像过筛子一样，逐条检查应用中的SQL。

2.1 引号与大小写：标识符处理的根本不同

这是第一个下马威。在MySQL中，默认情况下，表名和字段名是不区分大小写的（在Windows和macOS上，甚至表名存储也被转换为小写）。而在PostgreSQL中，除非使用双引号创建，否则所有未加引号的标识符都会被转换为小写，但查询时是严格区分大小写的。

问题场景： 我们的代码中，历史遗留了一些大写的表名，例如User。在MySQL中，SELECT * FROM User;和SELECT * FROM user;都能查到数据。但在PostgreSQL中，如果创建表时语句是CREATE TABLE User (...);（未加双引号），PostgreSQL实际创建的表名是user。此时执行SELECT * FROM User;会报错：relation “User” does not exist。

解决方案与实操：

  1. 统一规范：在迁移前，我们利用工具扫描了所有SQL，强制将表名和列名统一为小写加下划线的命名规范（snake_case），例如user_info。这是最一劳永逸的办法。
  2. 迁移时处理：使用pg_dump导出MySQL结构时，可以添加--quote-all-identifiers参数，但这会让所有标识符都被双引号包裹，后续维护麻烦。我们选择的是在导出后，用脚本批量将DDL语句中的对象名转换为小写。
  3. 应用层适配：修改应用代码中的SQL语句，确保表名、列名与PostgreSQL中的实际名称（小写）完全一致。对于无法修改的第三方库或遗留代码，可以在PostgreSQL中创建同义词（VIEW）来映射，但这不是推荐做法。

注意：在PostgreSQL中，双引号用于界定标识符，单引号用于界定字符串常量，这和MySQL一样。但MySQL还支持反引号（`）来包裹标识符，这在PostgreSQL中是非法的，必须替换为双引号。

2.2 自增主键：从AUTO_INCREMENT到SERIAL/IDENTITY

MySQL的AUTO_INCREMENT深入人心，但PostgreSQL提供了更现代、更标准的两种方式。

问题场景： 表结构定义需要重写。

-- MySQL CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(50) ); -- PostgreSQL (传统方式，仍常见) CREATE TABLE orders ( id SERIAL PRIMARY KEY, -- SERIAL 本质上是INT，并自动关联一个序列 order_no VARCHAR(50) ); -- PostgreSQL (标准SQL方式，推荐) CREATE TABLE orders ( id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, order_no VARCHAR(50) );

解决方案与选择：

  • SERIAL类型： 这是一种PostgreSQL特有的便捷写法。SERIAL不是一个真正的数据类型，它实际上是INTEGER类型，并自动关联一个序列（SEQUENCE），同时设置该列的默认值为从序列中取值。它简单易用，但不符合SQL标准。
  • IDENTITY列： 这是PostgreSQL 10+版本引入的，完全遵循SQL:2003标准。它在功能上更强大和清晰，能明确区分“总是由系统生成”（GENERATED ALWAYS）和“允许手动覆盖”（GENERATED BY DEFAULT）。我们团队最终选择了IDENTITY列，因为它代表了未来的方向，语义更明确，并且在某些数据复制工具中兼容性更好。

迁移实操： 我们需要将MySQL的DDL导出后，通过脚本或手动将AUTO_INCREMENT替换为GENERATED BY DEFAULT AS IDENTITY。同时，需要注意旧数据的导入。如果原MySQL表已有数据，在导入PostgreSQL后，需要手动设置该表关联序列的当前值，使其大于表中已有的最大ID，避免后续插入冲突。

-- 假设导入后，orders表最大的id是 1000 SELECT setval(pg_get_serial_sequence('orders', 'id'), coalesce(max(id), 0) + 1, false) FROM orders;

2.3 分页查询：LIMIT的“孪生兄弟”OFFSET

分页语法两者类似，但有一个关键行为差异。

问题场景：LIMIT 0的含义。

-- MySQL: LIMIT 0 会返回空结果集，常用于快速获取表结构或测试。 SELECT * FROM users LIMIT 0; -- PostgreSQL: LIMIT 0 的行为与MySQL一致。但需要注意，如果同时使用 `OFFSET`，在MySQL中 `LIMIT 0` 会忽略 `OFFSET`，而在PostgreSQL中，`OFFSET` 仍然会执行（尽管结果为空），这可能带来微小的性能差异（虽然结果都是空）。 -- 更重要的区别在于，PostgreSQL的 `LIMIT ALL` 等价于不限制，而MySQL不支持 `ALL` 关键字。

解决方案： 这部分语法改动不大，主要是习惯问题。需要检查代码中是否使用了LIMIT 0来做性能测试或结构探查，确保理解其行为。对于分页，更需要注意的是在PostgreSQL中，深度分页（OFFSET值很大）的性能问题比MySQL更显著，后期需要考虑使用基于游标或WHERE id > ?的键集分页来优化。

2.4 日期时间函数：细微之处见真章

日期处理是业务逻辑的重灾区，两者函数名和参数经常不同。

问题场景：

  1. 获取当前时间： MySQL用NOW()， PostgreSQL也用NOW()，但更推荐使用CURRENT_TIMESTAMP（标准SQL）。另外，PostgreSQL的NOW()返回的是带时区的时间戳（TIMESTAMPTZ），而MySQL的NOW()返回的是无时区的时间。
  2. 日期加减：
    -- MySQL SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); SELECT NOW() + INTERVAL 1 DAY; -- 也支持 -- PostgreSQL SELECT NOW() + INTERVAL '1 day'; SELECT CURRENT_DATE + 1; -- 加天数可以直接用整数
  3. 日期格式化： MySQL使用DATE_FORMAT(date, ‘%Y-%m-%d’)， PostgreSQL使用TO_CHAR(date, ‘YYYY-MM-DD’)。
  4. 提取日期部分： MySQL用YEAR(date),DAYOFWEEK(date)。 PostgreSQL用EXTRACT(YEAR FROM date),EXTRACT(DOW FROM date)。

解决方案： 我们创建了一个详细的函数映射表，并编写了一个SQL转换脚本，对应用代码仓库中的所有SQL文件进行扫描和批量替换。对于无法自动替换的复杂表达式，我们标记出来进行手动核对。这里有一个重要心得：不要试图在PostgreSQL中创建名为DATE_FORMAT的自定义函数来模拟MySQL行为，这会让代码库更加混乱，且不利于团队掌握PostgreSQL原生知识。正确的做法是，一次性将代码迁移到PostgreSQL的标准语法。

3. 数据类型与默认行为的碰撞

数据类型的不匹配是导致数据迁移后应用行为异常的隐形杀手。

3.1 布尔类型的处理

MySQL没有真正的布尔类型，它用TINYINT(1)来模拟，0为假，非0为真（通常用1表示真）。PostgreSQL有内置的BOOLEAN类型，值为TRUE/FALSE。

问题场景： 应用代码中可能直接使用1或0与布尔字段进行比较。

-- MySQL中常见写法 UPDATE settings SET is_active = 1 WHERE user_id = 100; SELECT * FROM users WHERE is_deleted = 0; -- 在PostgreSQL中，如果`is_active`是BOOLEAN类型，上述语句需要改为： UPDATE settings SET is_active = TRUE WHERE user_id = 100; SELECT * FROM users WHERE is_deleted = FALSE; -- 或者使用字符串表示 UPDATE settings SET is_active = ‘t’ WHERE user_id = 100;

解决方案：

  1. 在迁移表结构时，将MySQL中的TINYINT(1)转换为PostgreSQL的BOOLEAN。
  2. 修改所有相关的应用代码和存储过程，将数字0/1替换为FALSE/TRUE或‘f’/‘t’。
  3. 使用数据迁移工具（如pgloader）时，可以配置类型转换规则，自动将0/1转换为布尔值。

3.2 字符串与编码：UTF-8的统治与额外空格

MySQL的VARCHAR和TEXT类型在比较时，默认是不区分末尾空格的（除非使用BINARY修饰符）。而PostgreSQL的字符串比较是严格区分末尾空格的，这与SQL标准一致。

问题场景： 如果一个字段存储了‘hello ‘（末尾有空格），在查询WHERE field = ‘hello’时，MySQL会匹配成功，PostgreSQL则不会。

解决方案：

  1. 数据清洗： 在迁移前，对MySQL中可能含有尾部空格的重要字段进行清洗，使用TRIM()函数。
  2. 应用逻辑审查： 检查应用中是否有依赖这种不区分空格行为的逻辑，并进行修正。
  3. 使用LIKE或正则表达式： 如果确实需要模糊匹配，应使用LIKE ‘hello%’而非等号。

关于编码，强烈建议统一使用UTF-8。PostgreSQL对UTF-8的支持非常完善。确保从MySQL导出的数据和PostgreSQL创建的数据库编码都是UTF-8，可以避免绝大部分乱码问题。

3.3 默认值与非空约束的细微差别

在MySQL中，如果你向一个声明为NOT NULL的列插入NULL，但该列有默认值（例如DEFAULT 0），MySQL会“悄悄地”将NULL替换为默认值0，然后插入成功。这其实是对SQL标准的偏离。

在PostgreSQL中，行为是严格遵循标准的：向NOT NULL列插入NULL是明确违反约束的行为，会直接抛出错误，即使该列有默认值。默认值只在插入语句完全省略该列，或者显式使用DEFAULT关键字时才会生效。

问题场景： 应用代码中可能存在这样的插入语句：

INSERT INTO users (name, age) VALUES (‘张三’, NULL);

假设age列定义为INT NOT NULL DEFAULT 18。

  • 在MySQL中，这条语句会成功，age被存入18。
  • 在PostgreSQL中，这条语句会失败，报错：null value in column “age” violates not-null constraint。

解决方案：

  1. 严格模式检查： 在MySQL开发阶段，就应设置sql_mode=STRICT_ALL_TABLES等严格模式，让MySQL也表现出类似PostgreSQL的严格行为，提前暴露问题。
  2. 代码审计与修复： 迁移前，必须全面审计所有数据插入和更新的代码，确保没有向NOT NULL列传递NULL值。如果业务逻辑允许使用默认值，应该从插入列表中移除该列，或者显式传入DEFAULT。
    -- 正确写法 (省略列) INSERT INTO users (name) VALUES (‘张三’); -- 正确写法 (显式使用DEFAULT) INSERT INTO users (name, age) VALUES (‘张三’, DEFAULT);
  3. 利用迁移工具： 一些高级的数据迁移工具可以在传输过程中进行转换，但最根本的还是要修正应用逻辑。

4. 高级功能与架构差异的应对策略

当基础语法和类型都适配后，更深层次的差异开始浮现，这涉及到事务、锁、复制等核心架构。

4.1 事务与DDL：MySQL的“神奇”提交

在MySQL的InnoDB存储引擎中，默认的隔离级别是REPEATABLE READ，并且有一个广为人知（也广受诟病）的特性：每条SQL语句本身就是一个事务（如果未显式开启事务），并且大多数DDL语句（如ALTER TABLE,DROP TABLE）会隐式提交当前活动的事务。

PostgreSQL的行为则完全不同：它遵循更严格和传统的数据库模型。在PostgreSQL中，任何修改数据的语句（DML）或修改结构的语句（DDL）都必须在一个显式的事务块内执行（除非使用自动提交模式）。并且，DDL语句可以被回滚。

问题场景：

  1. 应用代码中，可能混杂着DML和DDL，并依赖于MySQL的自动提交。迁移到PostgreSQL后，这些DDL语句可能因为不在事务中而执行失败。
  2. 用于数据库版本管理的脚本（如Flyway, Liquibase），其脚本通常包含多个DDL。在MySQL中，一个脚本文件中的错误可能导致部分语句已提交，难以回滚。在PostgreSQL中，整个迁移脚本通常在一个事务中运行，要么全部成功，要么全部回滚，保证了数据一致性。

解决方案：

  1. 审查应用代码： 找出所有直接执行DDL的代码（通常是管理后台或初始化脚本），确保它们在执行时处于一个明确的事务上下文中，或者使用支持事务的数据库迁移工具来管理。
  2. 配置连接： 在应用连接PostgreSQL时，确保连接池或ORM框架配置了合适的自动提交行为。例如，在Java的JDBC中，可以设置autocommit=false来手动控制事务。
  3. 利用PostgreSQL的优势： 将数据库结构变更全部交给像Flyway这样的工具管理，享受其原子性（一个迁移脚本一个事务）带来的安全感。这是提升运维质量的好机会。

4.2 锁机制与并发控制

MySQL的InnoDB和PostgreSQL的MVCC（多版本并发控制）实现有显著差异，这直接影响了高并发下的行为。

问题场景： “SELECT … FOR UPDATE” 的锁范围。 在MySQL InnoDB中，SELECT … FOR UPDATE会在扫描到的索引记录上设置行锁。如果查询无法使用索引而进行全表扫描，它可能会锁住整个表的大部分甚至全部记录，容易导致死锁和性能问题。

在PostgreSQL中，SELECT … FOR UPDATE同样锁定行，但它的MVCC机制使得“写不阻塞读”。一个事务更新某行时，其他事务仍然可以读取该行的旧版本。这大大提高了读并发能力。但是，PostgreSQL对表级锁的使用更为保守和明确。

解决方案：

  1. 优化查询： 确保FOR UPDATE查询必须使用高效索引，避免全表扫描。这在MySQL和PostgreSQL中都是最佳实践，但在PostgreSQL中，由于其更复杂的锁管理器，全表扫描的FOR UPDATE可能带来更重的开销。
  2. 理解锁级别： 学习PostgreSQL提供的多种行级锁（FOR UPDATE,FOR NO KEY UPDATE,FOR SHARE,FOR KEY SHARE）和表级锁（ACCESS SHARE,ROW SHARE,ROW EXCLUSIVE,SHARE UPDATE EXCLUSIVE,SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE,ACCESS EXCLUSIVE）。根据业务场景选择限制最小的锁。
  3. 监控与排查： 使用pg_stat_activity和pg_locks系统视图来监控锁等待情况。当发生锁等待时，PostgreSQL提供的诊断信息通常比MySQL更详细。

4.3 复制与高可用方案切换

MySQL传统的主从复制（基于binlog）和PostgreSQL的流复制（基于WAL）是两种不同的哲学。

问题场景： 运维团队需要重新学习一套复制监控、故障切换和延迟处理的方法。

  • 延迟监控： MySQL通过SHOW SLAVE STATUS查看Seconds_Behind_Master。PostgreSQL则通过比较主备WAL位置的差异来计算，通常查询pg_stat_replication视图。
  • 故障切换： MySQL的MHA、Orchestrator等工具生态和PostgreSQL的Patroni、repmgr等工具生态不同。
  • 复制格式： MySQL有基于语句、基于行、混合三种复制格式。PostgreSQL的流复制本质上是物理复制，传输的是数据块的变更，一致性极强，但不支持像MySQL那样在从库执行不同模式的查询。

解决方案：

  1. 重新培训运维团队： 这是必须的投入。让团队深入理解WAL、复制槽、同步提交、级联复制等核心概念。
  2. 选用成熟的集群管理工具： 对于生产环境，强烈建议使用Patroni这样的工具来管理PostgreSQL高可用集群。它集成了配置管理、故障自动切换、监控等功能，极大地降低了运维复杂度。
  3. 设计新的备份策略： PostgreSQL的物理备份（pg_basebackup）与WAL归档结合，提供了强大且高效的时间点恢复能力。需要重新设计备份脚本和恢复演练流程。

5. 迁移后的性能调优与监控体系重建

数据库迁移完成并稳定运行后，工作远未结束。新的数据库需要新的调优思路和监控指标。

5.1 查询性能分析与优化器提示

MySQL的优化器提示（如USE INDEX,FORCE INDEX）和PostgreSQL的完全不同。盲目迁移这些提示会导致错误或性能下降。

问题场景： MySQL中用来强制使用索引的提示在PostgreSQL中无效。

-- MySQL SELECT * FROM users USE INDEX (idx_email) WHERE email LIKE ‘%@example.com%’; -- PostgreSQL (完全不同的语法) SELECT * FROM users WITH (INDEX (idx_email)) WHERE email LIKE ‘%@example.com%’; -- 但请注意，PostgreSQL的优化器通常非常聪明，应优先考虑不使用提示，通过调整配置、更新统计信息或重写查询来优化。

解决方案：

  1. 移除所有优化器提示： 在迁移的第一阶段，建议先注释掉或删除所有MySQL特有的优化器提示。让PostgreSQL的优化器自由发挥。
  2. 使用EXPLAIN ANALYZE： 这是PostgreSQL性能调优的利器。EXPLAIN显示执行计划，EXPLAIN ANALYZE会实际执行并显示各步骤耗时。仔细分析执行计划，关注是否有不合理的全表扫描、错误的连接顺序或估算行数严重偏差。
  3. 更新统计信息： PostgreSQL的查询优化器严重依赖统计信息。在大量数据导入或删除后，务必对相关表执行ANALYZE table_name;。可以配置autovacuum来自动完成这项工作。
  4. 谨慎使用提示： 只有在确凿证据表明优化器选择错误，且无法通过其他方式（如修改查询、创建部分索引、调整random_page_cost等成本参数）纠正时，才考虑使用PostgreSQL的WITH语法或SET命令来影响优化器。

5.2 连接池与资源管理

MySQL应用中常用的连接池（如HikariCP, Druid）可以直接用于连接PostgreSQL，但配置参数需要调整。

问题场景： PostgreSQL对连接的内存开销通常比MySQL更大。每个连接都是一个独立的操作系统进程（在较新版本中也可以是线程，但进程模型是主流）。维持过多空闲连接会消耗大量内存。

解决方案：

  1. 使用专用连接池中间件： 除了应用内连接池，强烈建议在应用和数据库之间部署PgBouncer或pgpool-II。它们作为独立的连接池代理，可以将成千上万的客户端连接复用为少量的数据库后端连接，极大地节省数据库服务器资源。PgBouncer轻量且高效，是我们团队的选择。
  2. 调整应用内连接池配置： 适当减小应用内连接池的最大大小，因为前端有PgBouncer。增加连接验证和超时设置。
  3. 监控数据库连接： 密切监控pg_stat_activity视图，观察活跃连接、空闲连接和长事务。设置idle_in_transaction_session_timeout参数来自动终止长时间空闲的事务连接，避免资源泄露。

5.3 监控指标体系的切换

原有的Zabbix、Prometheus等监控平台中针对MySQL的监控项（如QPS、慢查询、InnoDB缓冲池命中率）需要替换为PostgreSQL的等效项。

核心监控指标重建：

  1. 数据库负载： 监控pg_stat_database中的xact_commit,xact_rollback,tup_fetched,tup_updated等。
  2. 连接数： 监控pg_stat_activity的连接状态。
  3. 缓存命中率： PostgreSQL的共享缓冲池（shared_buffers）相当于InnoDB的缓冲池。计算命中率：SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio FROM pg_statio_user_tables;。
  4. WAL与复制： 监控pg_stat_replication中的lag（复制延迟），以及WAL日志的产生速度和归档状态。
  5. 表与索引膨胀： PostgreSQL的MVCC特性可能导致表和索引膨胀（死元组占用空间）。定期监控pg_stat_user_tables中的n_dead_tup，并配置合理的autovacuum参数。
  6. 慢查询： 在postgresql.conf中设置log_min_duration_statement = 1000（记录超过1秒的语句），并收集日志进行分析。也可以使用pg_stat_statements扩展模块，它提供了所有SQL语句的聚合性能统计，是性能分析的黄金标准。

我们的做法： 我们使用Prometheus + Grafana生态，部署了postgres_exporter来采集上述所有指标，并构建了全新的PostgreSQL监控仪表盘，替代了原有的MySQL仪表盘。这让我们对新的数据库运行状态一目了然。

迁移到PostgreSQL是一次挑战，但更是一次让团队重新审视数据层设计、提升代码严谨性和运维能力的机会。整个过程下来，最大的体会是：提前规划、充分测试、小步快跑。我们制定了详细的迁移检查清单，在预发环境进行了多轮全量压测和回归测试，最终采用双写并行、灰度切流的方式完成了线上切换，将风险降到了最低。PostgreSQL的严谨和强大，也反过来推动了我们在应用层写出更规范、更高效的SQL代码。如果你也正在考虑或正在进行类似的迁移，希望这些从实战中得来的经验，能帮你少走一些我们曾经走过的弯路。

编程学习 学习日记 实战经验
← 返回列表