保姆级教程：手把手教你将Ruoyi-Vue项目从MySQL迁移到PostgreSQL（附完整SQL改写清单）

从MySQL到PostgreSQL：Ruoyi-Vue项目数据库迁移全流程实战指南

当技术团队面临数据库选型调整时，将现有系统从MySQL迁移到PostgreSQL已成为越来越多企业的选择。PostgreSQL凭借其强大的JSON支持、地理空间数据处理能力以及更丰富的SQL标准兼容性，在复杂业务场景中展现出独特优势。本文将以Ruoyi-Vue这一流行Java快速开发框架为例，详细拆解数据库迁移的全生命周期管理，从前期评估到后期验证，提供一套完整的工程化解决方案。

1. 迁移决策评估与技术准备

数据库迁移绝非简单的驱动替换，而是需要综合考虑技术生态、业务需求和团队能力的系统工程。PostgreSQL与MySQL虽然同属关系型数据库，但在特性实现上存在诸多差异：

核心差异对比

特性	MySQL	PostgreSQL
事务隔离级别	默认REPEATABLE-READ	默认READ COMMITTED
字符串比较	不区分大小写	区分大小写
空字符串处理	视为有效值	与NULL等同
分页语法	LIMIT offset, size	LIMIT size OFFSET offset
系统函数	如sysdate()	如now()

在Ruoyi-Vue项目中，需要特别关注以下技术适配点：

1. 依赖配置调整：移除MySQL驱动，引入PostgreSQL依赖
2. 连接池配置：修改Druid连接参数和驱动类
3. 定时任务适配：调整Quartz的JobStore配置
4. SQL方言改写：处理函数差异和类型转换
5. 分页插件配置：切换方言为PostgreSQL模式

提示：建议在测试环境完整验证迁移流程后再进行生产环境操作，可考虑使用Flyway或Liquibase管理数据库变更脚本。

2. 环境配置与依赖调整

2.1 依赖管理配置

首先需要在项目的pom.xml中移除MySQL驱动，添加PostgreSQL依赖：

<dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.3.1</version> </dependency>

建议使用较新版本的驱动以获得更好的性能和功能支持。如果项目中使用到MyBatis或其他ORM框架，可能需要同步更新相关插件版本。

2.2 数据源配置调整

修改application-druid.yml配置文件中的关键参数：

spring: datasource: druid: driver-class-name: org.postgresql.Driver url: jdbc:postgresql://localhost:5432/ruoyi?currentSchema=public username: postgres password: yourpassword initial-size: 5 max-active: 20 validation-query: SELECT 1

特别注意PostgreSQL的连接URL参数：

• currentSchema：指定默认schema，相当于MySQL的database
• 不需要设置useSSL和serverTimezone等MySQL特有参数

2.3 定时任务适配

Ruoyi使用Quartz作为定时任务框架，需要调整ScheduleConfig配置：

@Bean public SchedulerFactoryBean schedulerFactoryBean(DataSource dataSource) { Properties prop = new Properties(); prop.put("org.quartz.jobStore.driverDelegateClass", "org.quartz.impl.jdbcjobstore.PostgreSQLDelegate"); // 其他配置... }

3. SQL方言转换策略

3.1 常见函数替换

PostgreSQL与MySQL在函数语法上存在显著差异，需要进行全局替换：

MySQL函数	PostgreSQL等效	应用场景
sysdate()	now()	获取当前时间
ifnull()	coalesce()	空值处理
date_format()	to_char()	日期格式化
group_concat()	string_agg()	字符串聚合

在Ruoyi-Vue项目中，这些函数主要出现在：

• 动态SQL片段
• MyBatis映射文件
• 注解SQL语句

3.2 类型处理差异

PostgreSQL是强类型数据库，需要特别注意类型转换：

1. 枚举值处理：

   -- MySQL status = 0 -- PostgreSQL status = '0'

2. 字符串处理：

   -- MySQL find_in_set find_in_set(#{deptId}, ancestors) -- PostgreSQL替代方案 cast(#{deptId} as varchar) = any(string_to_array(ancestors,','))

3. 分页语法：

   -- MySQL LIMIT 10, 20 -- PostgreSQL LIMIT 20 OFFSET 10

3.3 批量改写工具推荐

对于大型项目，手动修改SQL效率低下，推荐使用以下工具辅助：

1. IDE全局替换：

   • 使用正则表达式批量替换常见函数
   • 示例：将sysdate()替换为now()

2. SQL转换器：

   • pgloader ：支持从MySQL到PostgreSQL的Schema和数据迁移
   • AWS Schema Conversion Tool ：AWS提供的数据库转换工具

3. 自定义脚本：

   # 示例：批量替换SQL文件中的函数 import re def convert_sql(file_path): with open(file_path, 'r+') as f: content = f.read() content = re.sub(r'sysdate\(\)', 'now()', content) f.seek(0) f.write(content) f.truncate()

4. 数据迁移与验证

4.1 数据迁移方案

推荐采用分阶段迁移策略：

1. Schema迁移：

   • 使用pg_dump导出PostgreSQL Schema
   • 使用工具转换DDL语句

2. 数据迁移：

   • 全量迁移：初始数据导入
   • 增量同步：确保迁移过程中新产生的数据不丢失

3. 验证阶段：

   • 数据一致性检查
   • 性能基准测试

4.2 迁移后验证清单

完成迁移后，建议执行以下验证步骤：

1. 基础功能验证：

   • 用户登录和权限校验
   • 核心业务流程测试
   • 定时任务执行检查

2. 数据一致性验证：

   -- 示例：记录数比对 SELECT count(*) FROM sys_user; -- 数据抽样检查 SELECT * FROM sys_post LIMIT 5;

3. 性能对比测试：

   • 使用JMeter等工具进行压力测试
   • 对比关键接口响应时间
   • 监控数据库资源使用情况

4.3 常见问题解决方案

在实际迁移过程中，可能会遇到以下典型问题：

问题1：序列（Sequence）处理

• 现象：主键自增失败
• 解决方案：

  -- 确保序列与表关联 ALTER SEQUENCE seq_name OWNED BY table_name.column_name;

问题2：字符集编码

• 现象：中文乱码
• 解决方案：
  • 确认数据库创建时指定UTF-8编码
  • 检查客户端连接编码设置

问题3：事务隔离级别差异

• 现象：并发操作结果与MySQL不一致
• 解决方案：
  • 调整应用代码中的事务控制逻辑
  • 考虑使用SELECT FOR UPDATE明确锁定行

5. 高级优化与最佳实践

5.1 PostgreSQL特有功能利用

迁移完成后，可考虑利用PostgreSQL的高级特性提升系统能力：

1. JSON支持：

   -- 存储和查询JSON数据 UPDATE sys_config SET params = '{"timeout": 30}' WHERE config_id = 1; -- 使用JSON运算符 SELECT config_id FROM sys_config WHERE params->>'timeout' = '30';

2. 全文检索：

   -- 创建全文搜索索引 CREATE INDEX idx_content_search ON sys_notice USING gin(to_tsvector('english', notice_content));

3. 窗口函数：

   -- 复杂数据分析 SELECT user_name, salary, avg(salary) OVER (PARTITION BY dept_id) as dept_avg FROM sys_user;

5.2 性能调优建议

针对Ruoyi-Vue的典型使用场景，推荐以下优化措施：

1. 连接池配置：

   spring: datasource: druid: max-active: 50 min-idle: 10 max-wait: 60000 time-between-eviction-runs-millis: 60000

2. 索引优化：

   • 为常用查询条件添加索引
   • 考虑使用部分索引减少索引大小

3. 查询优化：

   • 使用EXPLAIN ANALYZE分析慢查询
   • 避免N+1查询问题

5.3 监控与维护

建立完善的监控体系对于生产环境至关重要：

1. 关键指标监控：

   • 连接数使用情况
   • 长事务检测
   • 锁等待分析

2. 维护任务：

   -- 定期执行VACUUM VACUUM (VERBOSE, ANALYZE) sys_oper_log; -- 更新统计信息 ANALYZE VERBOSE sys_user;

3. 备份策略：

   • 使用pg_dump进行逻辑备份
   • 配置WAL归档实现时间点恢复

在实际项目迁移中，我们发现在处理复杂报表查询时，PostgreSQL的CTE（Common Table Expressions）和窗口函数能显著简化SQL编写。例如，原本需要在Java代码中处理的层级数据汇总，现在可以直接通过一条SQL完成，不仅提高了性能，也降低了应用层复杂度。