若依框架从MySQL迁移到PostgreSQL实战：一个踩坑与填坑的完整记录

若依框架从MySQL迁移到PostgreSQL实战一个踩坑与填坑的完整记录去年接手了一个企业级项目的数据库迁移任务需要将原本运行在MySQL上的若依框架完整迁移到PostgreSQL环境。本以为只是简单的数据源切换没想到从驱动配置到SQL语法处处暗藏玄机。这篇文章将用真实项目复盘的形式分享我们趟过的那些坑和最终解决方案。1. 前期准备环境差异的认知觉醒第一次打开PostgreSQL的官方文档时就被开篇的警告击中虽然我们和MySQL都遵循SQL标准但实现方式可能不同。这句话在后来的迁移过程中被反复验证。关键差异认知清单自增机制MySQL的AUTO_INCREMENT vs PostgreSQL的SEQUENCE函数库差异IFNULL()变COALESCE()sysdate()变now()类型系统PostgreSQL对类型匹配更严格特别是布尔值处理大小写敏感PostgreSQL默认区分大小写而MySQL不区分提示建议在迁移前先通读PostgreSQL与MySQL的官方对比文档建立完整的差异认知框架我们团队犯的第一个错误就是直接用Navicat进行表结构导出导入。虽然大部分基础表顺利迁移但遇到自增主键时立刻报错。后来发现需要手动创建序列-- 典型序列创建语句 CREATE SEQUENCE sys_user_id_seq START WITH 100 INCREMENT BY 1; ALTER TABLE sys_user ALTER COLUMN user_id SET DEFAULT nextval(sys_user_id_seq);2. 数据源配置的暗礁险滩在ruoyi-admin的pom.xml中添加PostgreSQL驱动是第一步dependency groupIdorg.postgresql/groupId artifactIdpostgresql/artifactId version42.5.4/version /dependency然后是application.yml的配置调整。这里有个深坑连接字符串必须添加stringtypeunspecified参数否则会遇到类型映射问题spring: datasource: druid: master: url: jdbc:postgresql://127.0.0.1:5432/ruoyi?stringtypeunspecified driver-class-name: org.postgresql.Driver验证查询也需要从MySQL风格调整为PostgreSQL风格# 原MySQL验证语句 # validation-query: SELECT 1 FROM DUAL # PostgreSQL适配方案 validation-query: SELECT 13. SQL语法改造的持久战MyBatis映射文件的改造是工作量最大的部分。我们建立了完整的检查清单MySQL语法PostgreSQL替代方案影响文件数IFNULL()COALESCE()23sysdate()now()17database()CURRENT_SCHEMA()5status0status038典型改造示例!-- 原MySQL写法 -- if teststatus ! null and status ! AND status #{status}/if !-- PostgreSQL适配方案 -- if teststatus ! null and status ! AND status CAST(#{status} AS VARCHAR)/if最棘手的是分页插件配置。若依默认使用PageHelper需要特别调整dialect参数pagehelper: helper-dialect: postgresql reasonable: true support-methods-arguments: true4. 类型系统的精准打击PostgreSQL严格的类型系统给我们上了深刻的一课。在MySQL中能隐式转换的场景在PostgreSQL中会直接报错。特别是布尔值处理// 前端传值的处理方案调整 PostMapping(/updateStatus) public AjaxResult updateStatus(RequestParam String status) { // 原MySQL方案直接使用 // sysUser.setStatus(status); // PostgreSQL适配方案 sysUser.setStatus(1.equals(status) ? 1 : 0); return success(userService.updateUser(sysUser)); }在XML映射文件中也需要增加类型转换if testisFrame ! null and isFrame ! CAST(#{isFrame} AS INTEGER), /if5. 序列管理的自动化方案手动为每个表创建序列效率太低我们最终开发了自动化脚本DO $$ DECLARE tbl record; seq_name text; max_id bigint; BEGIN FOR tbl IN SELECT table_name, column_name FROM information_schema.columns WHERE table_schema public AND column_default LIKE nextval% LOOP seq_name : tbl.table_name || _ || tbl.column_name || _seq; EXECUTE format(SELECT COALESCE(MAX(%I), 0) 1 FROM %I, tbl.column_name, tbl.table_name) INTO max_id; EXECUTE format(ALTER SEQUENCE %I RESTART WITH %s, seq_name, max_id); END LOOP; END $$;这个脚本会扫描所有包含序列字段的表并自动将序列值设置为当前最大值1。6. 定时任务模块的特殊处理若依的定时任务模块使用了Quartz其表结构在PostgreSQL中需要特别注意大小写问题。我们最终采用的方案是完全删除原有的qrtz_*表使用标准PostgreSQL建表脚本重建在配置中明确指定不区分大小写spring: quartz: properties: org.quartz.jobStore.driverDelegateClass: org.quartz.impl.jdbcjobstore.PostgreSQLDelegate org.quartz.jobStore.useProperties: true重建表结构时特别注意BYTEA类型的字段处理这是PostgreSQL的二进制类型对应MySQL的BLOB。7. 性能优化的意外收获迁移完成后我们意外发现某些查询性能明显下降。通过EXPLAIN分析发现PostgreSQL的查询优化器与MySQL有显著差异。最终通过以下措施优化为常用查询条件创建特定索引CREATE INDEX idx_sys_user_dept ON sys_user(dept_id) WHERE del_flag 0;调整PostgreSQL的work_mem参数ALTER SYSTEM SET work_mem 16MB;对复杂查询使用CTECommon Table Expressions优化WITH user_dept AS ( SELECT u.user_id, d.dept_name FROM sys_user u JOIN sys_dept d ON u.dept_id d.dept_id WHERE u.del_flag 0 ) SELECT * FROM user_dept WHERE dept_name LIKE %技术%;整个迁移过程历时两周最终系统在PostgreSQL上的运行效率比原来MySQL环境提升了约15%特别是在复杂报表查询场景。最大的教训是数据库迁移不是简单的数据搬运而是需要全面了解目标数据库的特性并做好充分的测试验证。