首页 > 数据库 >RuoYi从MySQL迁移到PostgreSQL踩坑实录

RuoYi从MySQL迁移到PostgreSQL踩坑实录

来源：互联网 2026-07-26 08:32:14

写在前面 公司项目一直用PostgreSQL，几个项目下来，对其事务隔离级别、MVCC这些特性还算熟悉。这次有个后台管理系统，团队决定用RuoYi框架。下载的是MySQL版本的RuoYi（如果有PostgreSQL版本，欢迎同行告知）。本以为只是改个数据库连接的事儿——毕竟两种数据库的SQL标准相差

写在前面

公司项目一直用PostgreSQL，几个项目下来，对其事务隔离级别、MVCC这些特性还算熟悉。这次有个后台管理系统，团队决定用RuoYi框架。下载的是MySQL版本的RuoYi（如果有PostgreSQL版本，欢迎同行告知）。本以为只是改个数据库连接的事儿——毕竟两种数据库的SQL标准相差不大——结果还是踩了些坑。这篇实录把遇到的问题梳理一下，给后续做类似迁移的朋友提个醒。

RuoYi从MySQL迁移到PostgreSQL踩坑实录

长期稳定更新的攒劲资源： >>>点此立即查看<<<

第一个坑：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. 商业工具（如Na vicat的数据传输）：黑盒操作，出了问题不好排查。

最后决定用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

  1. 修改脚本中的数据库配置。

  2. 重要：在PostgreSQL中先创建好表结构（可以用Na vicat等工具导出MySQL的表结构，然后手动调整再导入PG）。

  3. 运行脚本：

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类型、datetime vs timestamp等。
  • 大小写敏感：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类型、全文搜索、分区表等），或者想了解更多数据库迁移的经验，也可以留言告诉，后续可以再写几篇详细的文章。

侠游戏发布此文仅为了传递信息，不代表侠游戏网站认同其观点或证实其描述

相关攻略

更多

同类更新

更多

热游推荐

更多

精彩专题

更多
手游 专题 攻略 App下载 web3
返回首页 回到顶部
湘ICP备14008430号-1 湘公网安备 43070302000280号
All Rights Reserved
本站为非盈利网站，不接受任何广告。本站所有软件，都由网友
上传，如有侵犯你的版权，请发邮件给xiayx666@163.com
抵制不良色情、反动、暴力游戏。注意自我保护，谨防受骗上当。
适度游戏益脑，沉迷游戏伤身。合理安排时间，享受健康生活。