平时做技术实践时，很多问题不是概念不会，而是细节没串起来。拿“RuoYi从MySQL迁移到PostgreSQL数据库的踩坑实录”来说，它看着像小点，放到项目里常会牵出环境、配置、兼容性和维护成本。下面按实际采用顺序，把思路、关键写法和容易踩坑的地方讲清楚，便于大家直接对照操作。

目录

• 写在前面
• 第一个坑：Druid连不上
• 数据迁移工具的选择
• SQL语法的各种不兼容
• 反引号问题
• ifnull函数
• sysdate函数
• 类型严格性的坑
• find_in_set的替代方案
• date_format的转换
• database函数
• TRUNCATE TABLE语法
• 序列同步的大坑
• PageHelper的设置
• 几个需留意的地方
• 1. 其他可能遇到的问题
• 2. 批量修改时注意编码
• 3. 先Git提交再批量修改
• 4. Nacos设置优先级高
• 5. 逐步测试
• 完整的改动清单
• 一些感悟
• 批量替换的正则规则
• 写在最后

写在前面

理解这一步时，公司项目用的是PostgreSQL，之前几个项目都在用，对它的事务隔离级别、MVCC这些特性还算熟悉。这次有个后台管理系统，我们选择用RuoYi框架。我下载的是MySQL版本的RuoYi（不知道有没有PostgreSQL版本，如果有的话，请老铁们告诉我一声）。本来以为就是改个数据库连接的事儿，毕竟两种数据库的SQL标准都差不多，结果还是遇到了一些问题。这里记录一下踩过的坑，给后面要做类似迁移的朋友们提个醒。

第一个坑：Druid连不上

一开始启动项目，直接报错：

org.postgresql.util.PSQLException: ERROR: relation "dual" does not exist

落到代码里，当时我还懵了，DUAL是啥？后来查了才知道，Druid默认用的是Oracle的验证SQL SELECT 1 FROM DUAL，MySQL能兼容这个语法，但PostgreSQL不认。

这个好办，去Nacos设置中心改一下：

# 原来的配置
validationQuery: SELECT 1 FROM DUAL

# 改成这样就行
validationQuery: SELECT 1

改完重启，总算能连上数据库了。

数据迁移工具的选择

从实现思路看，数据库连上了，下面就是把MySQL的数据迁移到PostgreSQL。这块我研究了几个工具：

1. pgloader：专门做数据库迁移的工具，功能很强大，但是二进制文件，安装比较麻烦（特别是在Windows上）
2. AWS DMS：如果在云上能够用，但我们是本地环境
3. 各种商业工具：比如Navicat的数据传输功能，不过都是黑盒操作，出了问题不好排查

最后我决定用Python自己写一个迁移脚本。主要考虑几点：

1. 代码清晰透明：整个迁移逻辑一目了然，出问题好调试
2. 灵活可控：能够在迁移过程中对数据做清洗和转换
3. 通用性好：Python操作数据库很成熟，pymysql和psycopg2都是久经考验的库
4. 容易扩展：需加日志、进度显示、异常处理，随时都能加

下面是完整的迁移脚本代码：

# coding=utf-8
import pymysql
import psycopg2
from psycopg2 import extras

# MySQL配置
mysql_config = {
    'host': '192.168.1.100',
    'port': 3306,
    'user': 'root',
    'password': 'your_password',
    'database': 'ruoyi',
    'charset': 'utf8mb4'
}

# PostgreSQL配置
pg_config = {
    'host': '192.168.1.101',
    'port': 5432,
    'user': 'postgres',
    'password': 'your_password',
    'database': 'ruoyi'
}

def get_all_tables(mysql_conn):
    """获取MySQL中所有表名"""
    cursor = mysql_conn.cursor()
    cursor.execute("SHOW TABLES")
    tables = [table[0] for table in cursor.fetchall()]
    cursor.close()
    return tables

def get_table_structure(mysql_conn, table_name):
    """获取表的列信息"""
    cursor = mysql_conn.cursor()
    cursor.execute(f"DESCRIBE {table_name}")
    columns = cursor.fetchall()
    cursor.close()
    return columns

def migrate_table(mysql_conn, pg_conn, table_name):
    """迁移单个表的数据"""
    print(f"正在迁移表: {table_name}")
    
    # 获取MySQL数据
    mysql_cursor = mysql_conn.cursor()
    mysql_cursor.execute(f"SELECT * FROM {table_name}")
    rows = mysql_cursor.fetchall()
    
    if not rows:
        print(f"  表 {table_name} 为空，跳过")
        mysql_cursor.close()
        return
    
    # 获取列名
    columns = [desc[0] for desc in mysql_cursor.description]
    mysql_cursor.close()
    
    # 构建INSERT语句
    placeholders = ','.join(['%s'] * len(columns))
    columns_str = ','.join(columns)
    insert_sql = f"INSERT INTO {table_name} ({columns_str}) VALUES ({placeholders})"
    
    # 批量插入到PostgreSQL
    pg_cursor = pg_conn.cursor()
    try:
        # 使用execute_batch提高性能
        extras.execute_batch(pg_cursor, insert_sql, rows, page_size=1000)
        pg_conn.commit()
        print(f"  成功迁移 {len(rows)} 条记录")
    except Exception as e:
        pg_conn.rollback()
        print(f"  迁移失败: {str(e)}")
        # 如果批量插入失败，尝试逐条插入（方便定位问题）
        error_count = 0
        for row in rows:
            try:
                pg_cursor.execute(insert_sql, row)
                pg_conn.commit()
            except Exception as e:
                pg_conn.rollback()
                error_count += 1
                if error_count <= 5:  # 只打印前5个错误
                    print(f"    错误记录: {row[:3]}... 错误: {str(e)}")
        
        if error_count > 0:
            print(f"  共有 {error_count} 条记录插入失败")
    finally:
        pg_cursor.close()

def main():
    # 连接数据库
    print("连接MySQL...")
    mysql_conn = pymysql.connect(**mysql_config)
    
    print("连接PostgreSQL...")
    pg_conn = psycopg2.connect(**pg_config)
    
    try:
        # 获取所有表
        tables = get_all_tables(mysql_conn)
        print(f"共找到 {len(tables)} 个表n")
        
        # 逐个迁移
        for i, table in enumerate(tables, 1):
            print(f"[{i}/{len(tables)}] ", end='')
            migrate_table(mysql_conn, pg_conn, table)
        
        print("n所有表迁移完成！")
        
    finally:
        mysql_conn.close()
        pg_conn.close()
        print("数据库连接已关闭")

if __name__ == '__main__':
    main()

采用方法：

1. 先安装依赖库：

pip install pymysql psycopg2-binary

2. 修改脚本中的数据库设置

3. 重要：在PostgreSQL中先新建好表结构（能够用Navicat等工具导出MySQL的表结构，随后手动调整一下再导入PG）

4. 运行脚本：

python migrate_data.py

脚本的几个亮点：

• 批量插入：用execute_batch一次插入1000条，比逐条insert快很多
• 错误处理：批量失败会回退，然后尝试逐条插入，便于定位问题数据
• 进度显示：能看到每个表的迁移进度和结果
• 事务控制：出错自动回滚，保证数据一致性

理解这一步时，这个脚本虽然轻松，但对于RuoYi这种规模的项目完全够用了。而且代码都在自己手里，需加什么功能随时改。比如我后来还加了个功能，在迁移某些表之前先做一下数据清洗，把一些脏数据过滤掉。

落到代码里，若你的项目数据量特别大（千万级以上），可能还是要用pgloader这种专业工具，性能会更好。但对于一般的中小型项目，Python脚本足够了，关键是清晰、可控。

SQL语法的各种不兼容

数据库连上之后，开始测试功能，发现各种SQL报错。

反引号问题

PostgreSQL不认MySQL的反引号：

-- MySQL这样写没问题
SELECT `user_id`, `user_name` FROM sys_user

-- 到PG就报错了
ERROR: syntax error at or near "`"

结合项目来看，这个把反引号全删了就行。能够用编辑器的全局替换功能，或者写个脚本批量处理。

注意：批量替换时一定要用UTF-8编码，不然中文会乱码。还有就是操作前先提交Git，避免改错了不好恢复。

ifnull函数

PostgreSQL没有ifnull()，要用COALESCE()替代：

-- MySQL
SELECT ifnull(perms, '') as perms FROM sys_menu

-- PostgreSQL
SELECT COALESCE(perms, '') as perms FROM sys_menu

这个也能够批量替换，具体的替换规则在后面会列出来。

sysdate函数

又是一个MySQL特有的函数：

-- MySQL
UPDATE sys_user SET update_time = sysdate()

-- PostgreSQL要换成这个
UPDATE sys_user SET update_time = CURRENT_TIMESTAMP

这种函数不兼容的地方还挺多的，好在都能批量处理。

类型严格性的坑

这个坑比较隐蔽。PostgreSQL对类型检查比MySQL严格得多：

-- MySQL里这样写没问题，字符类型的status字段可以和数字比较
WHERE status = 0

-- PostgreSQL直接报错
ERROR: operator does not exist: character = integer

得改成字符串比较：

WHERE status = '0'

在这个场景下，这其实涉及到PostgreSQL的类型系统设计，它不像MySQL那样依赖大量的隐式转换，更接近标准SQL的做法。从长远来看，这种严格性能避免很多隐藏的bug，但迁移的时候确实需花点时间排查。

我在SysMenuMapper.xml、SysDeptMapper.xml这些文件里找到好几处这样的问题，都要手动改。

find_in_set的替代方案

从实现思路看，这个函数在MySQL里挺常用的，但PostgreSQL没有。我们的代码里有好几处用到：

-- MySQL的写法
WHERE find_in_set(#{deptId}, ancestors)

得改成这样：

-- PostgreSQL的替代方案
WHERE (',' || ancestors || ',') LIKE '%,' || #{deptId} || ',%'

原理就是把字符串前后加上逗号，随后用LIKE匹配。虽然有点笨，但好用。

graph LR
    A["ancestors: '100,101,103'"] --> B["加工: ',100,101,103,'"]
    B --> C["匹配: '%,101,%'"]
    C --> D["结果: true"]

落到代码里，后来想了想，其实PostgreSQL有更好的方案，能够用数组类型。把ancestors存成int[]，随后直接用ANY操作符：

WHERE #{deptId} = ANY(ancestors)

落到代码里，这样性能会更好，还能借助GIN索引。不过这次迁移就先不动表结构了，改动太大。如果后面有性能问题，能够考虑重构这块。

date_format的转换

这个改起来稍微麻烦点：

-- MySQL
WHERE date_format(create_time, '%Y%m%d') >= date_format(#{params.beginTime}, '%Y%m%d')

-- PostgreSQL得这么写
WHERE to_char(create_time, 'YYYYMMDD') >= to_char(to_timestamp(#{params.beginTime}, 'YYYY-MM-DD'), 'YYYYMMDD')

涉及到的文件有SysUserMapper.xml、SysConfigMapper.xml、SysRoleMapper.xml等好几个。

database函数

-- MySQL
WHERE table_schema = (SELECT database())

-- PostgreSQL
WHERE table_schema = (SELECT current_database())

这个在GenTableMapper.xml和GenTableColumnMapper.xml里用到了。

TRUNCATE TABLE语法

PostgreSQL的TRUNCATE需显式指定序列重置：

-- MySQL
TRUNCATE TABLE sys_job_log

-- PostgreSQL要加上这个选项
TRUNCATE TABLE sys_job_log RESTART IDENTITY

序列同步的大坑

结合项目来看，SQL语法都改完了，以为终于能够了。结果测试插入数据的时候，又遇到一个问题：

ERROR: duplicate key value violates unique constraint "sys_user_pkey"
详细：Key (user_id)=(2) already exists.

主键冲突？但我明明是插入新数据啊。

落到代码里，后来才明白，从MySQL迁移数据过来后，PostgreSQL的序列（Sequence）没有自动更新。比如sys_user表里已经有ID到100的数据了，但序列还停留在1，所以下次插入时会尝试采用ID=2，当然就冲突了。

落到代码里，这个问题MySQL不会有，因为MySQL的AUTO_INCREMENT是直接存在表结构里的，导入数据时会自动更新。但PostgreSQL的序列是独立的数据库对象，和表是分离的。这种设计其实更灵活，比如多个表能够共享一个序列，但迁移时就需手动处理这个同步问题。

这个问题要手动修复所有表的序列。我写了个SQL脚本来批量处理：

DO $$
DECLARE
    r RECORD;
    max_id BIGINT;
BEGIN
    -- 遍历所有带序列的表
    FOR r IN 
        SELECT 
            t.tablename,
            c.column_name,
            pg_get_serial_sequence(t.schemaname || '.' || t.tablename, c.column_name) as sequence_name
        FROM 
            pg_tables t
            JOIN information_schema.columns c ON t.tablename = c.table_name
        WHERE 
            t.schemaname = 'public'
            AND pg_get_serial_sequence(t.schemaname || '.' || t.tablename, c.column_name) IS NOT NULL
    LOOP
        -- 获取表的最大ID
        EXECUTE format('SELECT COALESCE(MAX(%I), 0) FROM %I', r.column_name, r.tablename) INTO max_id;
        
        IF max_id > 0 THEN
            -- 把序列设置为最大ID
            EXECUTE format('SELECT setval(%L, %s)', r.sequence_name, max_id);
            RAISE NOTICE '已修复表 %.%, 序列: %, 当前最大ID: %', 
                'public', r.tablename, r.sequence_name, max_id;
        ELSE
            -- 如果表是空的，序列从1开始
            EXECUTE format('SELECT setval(%L, 1, false)', r.sequence_name);
            RAISE NOTICE '表 %.% 为空, 序列 % 已重置为从1开始', 
                'public', r.tablename, r.sequence_name;
        END IF;
    END LOOP;
    
    RAISE NOTICE '=== 所有序列修复完成 ===';
END $$;

落到代码里，这个脚本会自动找到所有带序列的表，随后把序列值设置为表中的最大ID。强烈建议把这个脚本加到迁移流程里，数据导入完就立即执行。

脚本逻辑很轻松：遍历所有表 → 拿到最大ID → 如果有数据就把序列设为最大ID，没数据就重置为1。

PageHelper的设置

差点忘了，PageHelper分页插件也要设置一下：

pagehelper:
  helperDialect: postgresql  # 指定数据库方言
  supportMethodsArguments: true
  params: count=countSql

结合项目来看，这个不改的话，分页SQL会按MySQL的语法生成，PostgreSQL就不认了。

几个需留意的地方

1. 其他可能遇到的问题

落到代码里，上面列举的是我在迁移过程中实际碰到的问题，但不同项目可能还会遇到其他情况：

• 字段类型映射：MySQL的某些类型在PostgreSQL中需转换，比如text类型、datetimevstimestamp等
• 大小写敏感：PostgreSQL默认把标识符转换为小写，如果建表时用了双引号包裹的大小写混合字段名，查询时要特别注意
• 自增主键：PostgreSQL采用SERIAL类型或序列，迁移时要确保这块正确映射
• 索引和约束：索引名称、约束可能需调整
• 存储过程和触发器：如果用了这些，语法差异会更大，需重写
• JSON类型：PostgreSQL的JSON兼容更强大，但语法不太一样

建议在实际迁移前，先搭个测试环境跑一遍，把项目特有的问题都找出来。

2. 批量修改时注意编码

从实现思路看，用脚本批量修改文件的时候，一定需留意编码格式。我一开始用默认编码，结果把Mapper文件里的中文注释全搞乱了。后来改成强制UTF-8才解决：

[System.IO.File]::WriteAllText($file.FullName, $content, [System.Text.Encoding]::UTF8)

3. 先Git提交再批量修改

在这个场景下，批量修改之前，一定要把代码先提交到Git。我第一次测试脚本的时候，因为正则写错了，把一堆文件改坏了。幸好有Git，直接reset回去重来。

4. Nacos设置优先级高

实际处理时，这个也需留意，我们项目用了Nacos做设置中心。本地设置文件改了半天没生效，后来才发现Nacos的设置优先级更高，得去Nacos里改。

5. 逐步测试

不要一次性把所有SQL都改完再测试。我的做法是改一批就编译一次：

mvn clean compile -DskipTests

这样能及时发现问题，不至于最后一堆错误不知道从哪儿排查。

完整的改动清单

最后整理了一下，这次迁移主要改了这些地方：

设置类修改：

• Nacos的Druid设置：validationQuery
• PageHelper设置：helperDialect

依赖升级：

• MyBatis Spring Boot Starter: 3.0.5
• 处理mybatis-spring依赖冲突

SQL语法替换（46个Mapper文件）：

• 反引号删除
• ifnull → COALESCE
• sysdate() → CURRENT_TIMESTAMP
• find_in_set → LIKE模式匹配
• date_format → to_char
• database() → current_database()
• TRUNCATE TABLE加RESTART IDENTITY
• 字符类型比较加引号

序列修复：

• 执行批量序列重置脚本

迁移流程就是：设置修改 → 依赖升级 → SQL语法替换 → 序列重置 → 功能测试，发现问题就回到对应步骤修复。

一些感悟

这次迁移下来，最大的感受就是两种数据库看起来差不多，细节差异还挺多的落到代码里，。MySQL很多地方比较"宽松"，隐式类型转换、特有函数啥的用起来很便于。但PostgreSQL更严格，该是什么类型就是什么类型，不会自动帮你转。

结合项目来看，说实话，刚开始各种报错确实有点烦。但后来想想，PostgreSQL这种严格性其实是好事，能在开发阶段就发现很多潜在的问题。而且PostgreSQL在事务处理、同时发控制这些方面确实做得更扎实，特别是MVCC的实现，不会有MySQL那种幻读的问题（即使在可重复读级别下）。

另外就是批量处理很重要理解这一步时，。像反引号、ifnull这种重复性高的改动，手动改肯定要疯。用脚本批量处理能省很多时间。不过前提是要先在Git里提交，避免改坏了不好恢复。

还有一个就是序列问题一定要重视理解这一步时，。这个坑比较隐蔽，如果不是测试时恰好插入数据，可能上线后才会发现。到时候就麻烦了。

从实现思路看，后面有时间想测试一下两边的执行计划差异，尤其是那些涉及子查询和JOIN的SQL。PostgreSQL的查询优化器和MySQL不太一样，有些SQL可能需针对性地调整索引策略。这块还在持续观察中。

批量替换的正则规则

若你要批量修改文件，能够参考这些替换规则：

# 1. 替换sysdate()
查找: sysdate()
替换: CURRENT_TIMESTAMP

# 2. 替换ifnull()
查找: ifnull(
替换: COALESCE(

# 3. 移除反引号
查找: `([a-z_]+)`
替换: $1

# 4. 替换database()
查找: select database()
替换: select current_database()

理解这一步时，用你熟悉的编辑器（比如VSCode、IntelliJ IDEA的全局替换功能）或者写脚本批量处理都行，记得先提交Git再操作。

写在最后

在这个场景下，整个迁移过程花了一些时间，主要是在排查各种SQL语法不兼容的问题。如果你也要做类似的迁移，希望这篇文章能帮你少走点弯路。

有几点建议：

1. 提前做好兼容性检查，列个清单
2. 批量修改前先备份（用Git）
3. 逐步测试，别一次改太多
4. 序列问题一定要处理

落到代码里，这块我也还在持续优化中，比如有些地方的SQL性能可能还有提升空间。如果你在迁移过程中遇到了其他问题，或者有更好的解决方案，欢迎在评论区交流讨论！

实际处理时，另外，如果你对PostgreSQL的一些高级特性感兴趣（比如JSON类型、全文搜索、分区表等），或者想了解更多数据库迁移的经验，也能够留言告诉我，我后面能够再写几篇详细的文章。

大家在用RuoYi的时候还遇到过哪些坑？一起来吐槽一下吧

到此这篇关于RuoYi从MySQL迁移到PostgreSQL数据库的踩坑实录的文章就介绍到这了,更多相关RuoYi从MySQL迁移PostgreSQL内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多兼容脚本之家！

您可能感兴趣的文章:

• MySQL迁移到PostgreSQL操作指南
• 将Django采用的数据库从MySQL迁移到PostgreSQL的教程
• Mysql迁移Postgresql的实现示例
• Mysql 8.0.33 如何迁移至 Postgresql 16.2