RuoYi從MySQL遷移到PostgreSQL數(shù)據(jù)庫的踩坑實(shí)錄

更新時間：2026年02月13日 10:43:20 作者：踏浪無痕

隨著業(yè)務(wù)發(fā)展對數(shù)據(jù)庫性能要求的提升,MySQL遷移至PostgreSQL成為企業(yè)級應(yīng)用的常見需求,下面這篇文章主要介紹了RuoYi從MySQL遷移到PostgreSQL數(shù)據(jù)庫的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下

寫在前面

公司項目用的是PostgreSQL，之前幾個項目都在用，對它的事務(wù)隔離級別、MVCC這些特性還算熟悉。這次有個后臺管理系統(tǒng)，我們選擇用RuoYi框架。我下載的是MySQL版本的RuoYi（不知道有沒有PostgreSQL版本，如果有的話，請老鐵們告訴我一聲）。本來以為就是改個數(shù)據(jù)庫連接的事兒，畢竟兩種數(shù)據(jù)庫的SQL標(biāo)準(zhǔn)都差不多，結(jié)果還是遇到了一些問題。這里記錄一下踩過的坑，給后面要做類似遷移的朋友們提個醒。

第一個坑：Druid連不上

一開始啟動項目，直接報錯：

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

當(dāng)時我還懵了，DUAL是啥？后來查了才知道，Druid默認(rèn)用的是Oracle的驗證SQL SELECT 1 FROM DUAL，MySQL能兼容這個語法，但PostgreSQL不認(rèn)。

這個好辦，去Nacos配置中心改一下：

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

# 改成這樣就行
validationQuery: SELECT 1

改完重啟，總算能連上數(shù)據(jù)庫了。

數(shù)據(jù)遷移工具的選擇

數(shù)據(jù)庫連上了，接下來就是把MySQL的數(shù)據(jù)遷移到PostgreSQL。這塊我研究了幾個工具：

pgloader：專門做數(shù)據(jù)庫遷移的工具，功能很強(qiáng)大，但是二進(jìn)制文件，安裝比較麻煩（特別是在Windows上）
AWS DMS：如果在云上可以用，但我們是本地環(huán)境
各種商業(yè)工具：比如Navicat的數(shù)據(jù)傳輸功能，不過都是黑盒操作，出了問題不好排查

最后我決定用Python自己寫一個遷移腳本。主要考慮幾點(diǎn)：

代碼清晰透明：整個遷移邏輯一目了然，出問題好調(diào)試
靈活可控：可以在遷移過程中對數(shù)據(jù)做清洗和轉(zhuǎn)換
通用性好：Python操作數(shù)據(jù)庫很成熟，pymysql和psycopg2都是久經(jīng)考驗的庫
容易擴(kuò)展：需要加日志、進(jìn)度顯示、異常處理，隨時都能加

下面是完整的遷移腳本代碼：

# 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):
    """遷移單個表的數(shù)據(jù)"""
    print(f"正在遷移表: {table_name}")
    
    # 獲取MySQL數(shù)據(jù)
    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()
    
    # 構(gòu)建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():
    # 連接數(shù)據(jù)庫
    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("數(shù)據(jù)庫連接已關(guān)閉")

if __name__ == '__main__':
    main()

使用方法：

先安裝依賴庫：

pip install pymysql psycopg2-binary

修改腳本中的數(shù)據(jù)庫配置
重要：在PostgreSQL中先創(chuàng)建好表結(jié)構(gòu)（可以用Navicat等工具導(dǎo)出MySQL的表結(jié)構(gòu)，然后手動調(diào)整一下再導(dǎo)入PG）
運(yùn)行腳本：

python migrate_data.py

腳本的幾個亮點(diǎn)：

批量插入：用execute_batch一次插入1000條，比逐條insert快很多
錯誤處理：批量失敗會回退，然后嘗試逐條插入，方便定位問題數(shù)據(jù)
進(jìn)度顯示：能看到每個表的遷移進(jìn)度和結(jié)果
事務(wù)控制：出錯自動回滾，保證數(shù)據(jù)一致性

這個腳本雖然簡單，但對于RuoYi這種規(guī)模的項目完全夠用了。而且代碼都在自己手里，需要加什么功能隨時改。比如我后來還加了個功能，在遷移某些表之前先做一下數(shù)據(jù)清洗，把一些臟數(shù)據(jù)過濾掉。

如果你的項目數(shù)據(jù)量特別大（千萬級以上），可能還是要用pgloader這種專業(yè)工具，性能會更好。但對于一般的中小型項目，Python腳本足夠了，關(guān)鍵是清晰、可控。

SQL語法的各種不兼容

數(shù)據(jù)庫連上之后，開始測試功能，發(fā)現(xiàn)各種SQL報錯。

反引號問題

PostgreSQL不認(rèn)MySQL的反引號：

-- MySQL這樣寫沒問題
SELECT `user_id`, `user_name` FROM sys_user

-- 到PG就報錯了
ERROR: syntax error at or near "`"

這個把反引號全刪了就行。可以用編輯器的全局替換功能，或者寫個腳本批量處理。

注意：批量替換時一定要用UTF-8編碼，不然中文會亂碼。還有就是操作前先提交Git，避免改錯了不好恢復(fù)。

ifnull函數(shù)

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

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

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

這個也可以批量替換，具體的替換規(guī)則在后面會列出來。

sysdate函數(shù)

又是一個MySQL特有的函數(shù)：

-- MySQL
UPDATE sys_user SET update_time = sysdate()

-- PostgreSQL要換成這個
UPDATE sys_user SET update_time = CURRENT_TIMESTAMP

這種函數(shù)不兼容的地方還挺多的，好在都能批量處理。

類型嚴(yán)格性的坑

這個坑比較隱蔽。PostgreSQL對類型檢查比MySQL嚴(yán)格得多：

-- MySQL里這樣寫沒問題，字符類型的status字段可以和數(shù)字比較
WHERE status = 0

-- PostgreSQL直接報錯
ERROR: operator does not exist: character = integer

得改成字符串比較：

WHERE status = '0'

這其實(shí)涉及到PostgreSQL的類型系統(tǒng)設(shè)計，它不像MySQL那樣依賴大量的隱式轉(zhuǎn)換，更接近標(biāo)準(zhǔn)SQL的做法。從長遠(yuǎn)來看，這種嚴(yán)格性能避免很多隱藏的bug，但遷移的時候確實(shí)需要花點(diǎn)時間排查。

我在SysMenuMapper.xml、SysDeptMapper.xml這些文件里找到好幾處這樣的問題，都要手動改。

find_in_set的替代方案

這個函數(shù)在MySQL里挺常用的，但PostgreSQL沒有。我們的代碼里有好幾處用到：

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

得改成這樣：

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

原理就是把字符串前后加上逗號，然后用LIKE匹配。雖然有點(diǎn)笨，但好用。

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

后來想了想，其實(shí)PostgreSQL有更好的方案，可以用數(shù)組類型。把a(bǔ)ncestors存成int[]，然后直接用ANY操作符：

WHERE #{deptId} = ANY(ancestors)

這樣性能會更好，還能利用GIN索引。不過這次遷移就先不動表結(jié)構(gòu)了，改動太大。如果后面有性能問題，可以考慮重構(gòu)這塊。

date_format的轉(zhuǎn)換

這個改起來稍微麻煩點(diǎn)：

-- 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函數(shù)

-- 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語法都改完了，以為終于可以了。結(jié)果測試插入數(shù)據(jù)的時候，又遇到一個問題：

ERROR: duplicate key value violates unique constraint "sys_user_pkey"
詳細(xì)：Key (user_id)=(2) already exists.

主鍵沖突？但我明明是插入新數(shù)據(jù)啊。

后來才明白，從MySQL遷移數(shù)據(jù)過來后，PostgreSQL的序列（Sequence）沒有自動更新。比如sys_user表里已經(jīng)有ID到100的數(shù)據(jù)了，但序列還停留在1，所以下次插入時會嘗試使用ID=2，當(dāng)然就沖突了。

這個問題MySQL不會有，因為MySQL的AUTO_INCREMENT是直接存在表結(jié)構(gòu)里的，導(dǎo)入數(shù)據(jù)時會自動更新。但PostgreSQL的序列是獨(dú)立的數(shù)據(jù)庫對象，和表是分離的。這種設(shè)計其實(shí)更靈活，比如多個表可以共享一個序列，但遷移時就需要手動處理這個同步問題。

這個問題要手動修復(fù)所有表的序列。我寫了個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
            -- 把序列設(shè)置為最大ID
            EXECUTE format('SELECT setval(%L, %s)', r.sequence_name, max_id);
            RAISE NOTICE '已修復(fù)表 %.%, 序列: %, 當(dāng)前最大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 '=== 所有序列修復(fù)完成 ===';
END $$;

這個腳本會自動找到所有帶序列的表，然后把序列值設(shè)置為表中的最大ID。強(qiáng)烈建議把這個腳本加到遷移流程里，數(shù)據(jù)導(dǎo)入完就立即執(zhí)行。

腳本邏輯很簡單：遍歷所有表 → 獲取最大ID → 如果有數(shù)據(jù)就把序列設(shè)為最大ID，沒數(shù)據(jù)就重置為1。

PageHelper的配置

差點(diǎn)忘了，PageHelper分頁插件也要配置一下：

pagehelper:
  helperDialect: postgresql  # 指定數(shù)據(jù)庫方言
  supportMethodsArguments: true
  params: count=countSql

這個不改的話，分頁SQL會按MySQL的語法生成，PostgreSQL就不認(rèn)了。

幾個要注意的地方

1. 其他可能遇到的問題

上面列舉的是我在遷移過程中實(shí)際碰到的問題，但不同項目可能還會遇到其他情況：

字段類型映射：MySQL的某些類型在PostgreSQL中需要轉(zhuǎn)換，比如text類型、datetimevstimestamp等
大小寫敏感：PostgreSQL默認(rèn)把標(biāo)識符轉(zhuǎn)換為小寫，如果建表時用了雙引號包裹的大小寫混合字段名，查詢時要特別注意
自增主鍵：PostgreSQL使用SERIAL類型或序列，遷移時要確保這塊正確映射
索引和約束：索引名稱、約束可能需要調(diào)整
存儲過程和觸發(fā)器：如果用了這些，語法差異會更大，需要重寫
JSON類型：PostgreSQL的JSON支持更強(qiáng)大，但語法不太一樣

建議在實(shí)際遷移前，先搭個測試環(huán)境跑一遍，把項目特有的問題都找出來。

2. 批量修改時注意編碼

用腳本批量修改文件的時候，一定要注意編碼格式。我一開始用默認(rèn)編碼，結(jié)果把Mapper文件里的中文注釋全搞亂了。后來改成強(qiáng)制UTF-8才解決：

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

3. 先Git提交再批量修改

批量修改之前，一定要把代碼先提交到Git。我第一次測試腳本的時候，因為正則寫錯了，把一堆文件改壞了。幸好有Git，直接reset回去重來。

4. Nacos配置優(yōu)先級高

這個也要注意，我們項目用了Nacos做配置中心。本地配置文件改了半天沒生效，后來才發(fā)現(xiàn)Nacos的配置優(yōu)先級更高，得去Nacos里改。

5. 逐步測試

不要一次性把所有SQL都改完再測試。我的做法是改一批就編譯一次：

mvn clean compile -DskipTests

這樣能及時發(fā)現(xiàn)問題，不至于最后一堆錯誤不知道從哪兒排查。

完整的改動清單

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

配置類修改：

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
字符類型比較加引號

序列修復(fù)：

執(zhí)行批量序列重置腳本

遷移流程就是：配置修改 → 依賴升級 → SQL語法替換 → 序列重置 → 功能測試，發(fā)現(xiàn)問題就回到對應(yīng)步驟修復(fù)。

一些感悟

這次遷移下來，最大的感受就是兩種數(shù)據(jù)庫看起來差不多，細(xì)節(jié)差異還挺多的。MySQL很多地方比較"寬松"，隱式類型轉(zhuǎn)換、特有函數(shù)啥的用起來很方便。但PostgreSQL更嚴(yán)格，該是什么類型就是什么類型，不會自動幫你轉(zhuǎn)。

說實(shí)話，剛開始各種報錯確實(shí)有點(diǎn)煩。但后來想想，PostgreSQL這種嚴(yán)格性其實(shí)是好事，能在開發(fā)階段就發(fā)現(xiàn)很多潛在的問題。而且PostgreSQL在事務(wù)處理、并發(fā)控制這些方面確實(shí)做得更扎實(shí)，特別是MVCC的實(shí)現(xiàn)，不會有MySQL那種幻讀的問題（即使在可重復(fù)讀級別下）。

另外就是批量處理很重要。像反引號、ifnull這種重復(fù)性高的改動，手動改肯定要瘋。用腳本批量處理能省很多時間。不過前提是要先在Git里提交，避免改壞了不好恢復(fù)。

還有一個就是序列問題一定要重視。這個坑比較隱蔽，如果不是測試時恰好插入數(shù)據(jù)，可能上線后才會發(fā)現(xiàn)。到時候就麻煩了。

后面有時間想測試一下兩邊的執(zhí)行計劃差異，尤其是那些涉及子查詢和JOIN的SQL。PostgreSQL的查詢優(yōu)化器和MySQL不太一樣，有些SQL可能需要針對性地調(diào)整索引策略。這塊還在持續(xù)觀察中。

批量替換的正則規(guī)則

如果你要批量修改文件，可以參考這些替換規(guī)則：

# 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語法不兼容的問題。如果你也要做類似的遷移，希望這篇文章能幫你少走點(diǎn)彎路。

有幾點(diǎn)建議：

提前做好兼容性檢查，列個清單
批量修改前先備份（用Git）
逐步測試，別一次改太多
序列問題一定要處理

這塊我也還在持續(xù)優(yōu)化中，比如有些地方的SQL性能可能還有提升空間。如果你在遷移過程中遇到了其他問題，或者有更好的解決方案，歡迎在評論區(qū)交流討論！

另外，如果你對PostgreSQL的一些高級特性感興趣（比如JSON類型、全文搜索、分區(qū)表等），或者想了解更多數(shù)據(jù)庫遷移的經(jīng)驗，也可以留言告訴我，我后面可以再寫幾篇詳細(xì)的文章。

大家在用RuoYi的時候還遇到過哪些坑？一起來吐槽一下吧 ??

到此這篇關(guān)于RuoYi從MySQL遷移到PostgreSQL數(shù)據(jù)庫的踩坑實(shí)錄的文章就介紹到這了,更多相關(guān)RuoYi從MySQL遷移PostgreSQL內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: