MySQL 遷移至 Oracle 實戰：工具選型、型別映射、遷移策略與坑點規避

前言：異構遷移的工程挑戰

MySQL → Oracle 遷移涉及 SQL 方言差異、資料型別映射、PL/SQL 改寫、效能模型轉換、工具鏈選型等多維度挑戰。本文基於 Oracle SQL Developer Migration Workbench、第三方工具與手工改寫實踐，提供端到端遷移方法論。

一、遷移工具選型對比

二、核心型別映射表

三、關鍵語法與功能差異改寫指南

1. 自增主鍵 → Sequence + Trigger / Identity Column (12c+)

-- MySQL
CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

-- Oracle 12c+ Identity Column (推薦)
CREATE TABLE t (
  id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
  name VARCHAR2(50 CHAR)
);

-- Oracle 11g Sequence + Trigger
CREATE SEQUENCE t_seq START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50 CHAR));
CREATE OR REPLACE TRIGGER t_bir
BEFORE INSERT ON t FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
  SELECT t_seq.NEXTVAL INTO :NEW.id FROM dual;
END;
/

2. LIMIT / OFFSET → ROWNUM / FETCH FIRST (12c+)

-- MySQL
SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20;

-- Oracle 12c+ (標準 SQL)
SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Oracle 11g (子查詢 + ROWNUM)
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM t ORDER BY id
  ) a WHERE ROWNUM <= 30
) WHERE rn > 20;

3. REPLACE INTO / INSERT ... ON DUPLICATE KEY UPDATE → MERGE

-- MySQL
INSERT INTO t (id, val) VALUES (1, 'a') ON DUPLICATE KEY UPDATE val = 'b';

-- Oracle MERGE
MERGE INTO t USING (SELECT 1 AS id, 'b' AS val FROM dual) src
ON (t.id = src.id)
WHEN MATCHED THEN UPDATE SET t.val = src.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (src.id, src.val);

4. IFNULL / COALESCE → NVL / COALESCE

-- MySQL: IFNULL(a, b) / COALESCE(a, b, c)
-- Oracle: NVL(a, b) (僅兩參數) / COALESCE(a, b, c) (標準 SQL，推薦)

5. DATE_FORMAT / STR_TO_DATE → TO_CHAR / TO_DATE

-- MySQL: DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')
-- Oracle: TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
-- 注意: Oracle 格式模型區分大小寫，MI=分, MM=月

6. NOW() / CURDATE() → SYSDATE / SYSTIMESTAMP / CURRENT_DATE

-- MySQL NOW() 返回當前會話時間 (受 time_zone 影響)
-- Oracle SYSDATE 返回資料庫伺服器時間 (不受會話影響)
-- Oracle SYSTIMESTAMP 含毫秒/微秒 + 時區
-- Oracle CURRENT_DATE 返回會話時區日期

7. GROUP_CONCAT → LISTAGG (11gR2+)

-- MySQL
SELECT GROUP_CONCAT(name ORDER BY id SEPARATOR ',') FROM t GROUP BY type;

-- Oracle
SELECT LISTAGG(name, ',') WITHIN GROUP (ORDER BY id) FROM t GROUP BY type;

8. 存儲過程/函數/觸發器：PL/SQL 完全重寫

核心差異：

• 變數宣告：`DECLARE v INT DEFAULT 0;` → `v NUMBER := 0;`
• 流程控制：`IF ... THEN ... ELSE ... END IF;` 語法相似但細節不同
• 游標：MySQL 隱含游標 vs Oracle 顯式游標 `OPEN/FETCH/CLOSE`
• 異常處理：Oracle `EXCEPTION WHEN OTHERS THEN ...` 體系完善
• 動態 SQL：MySQL `PREPARE/EXECUTE` vs Oracle `EXECUTE IMMEDIATE / DBMS_SQL`
• 包：Oracle `PACKAGE SPEC/BODY` 封裝性強，MySQL 無對應

策略：邏輯複雜的存儲過程**不建議自動轉換**，需資深 PL/SQL 開發重寫，並建立單元測試驗證邏輯等價。

四、遷移實施策略：分階段降低風險

階段 0：評估與規劃 (2~4 週)

• 資產清單：表數量、儲存過程行數、觸發器、視圖、外鍵、分區表、大表 (>10GB)
• 相容性掃描：SQL Developer / SCT 生成評估報告，識別「無法自動轉換」物件
• 效能基線：MySQL 核心 SQL 執行計劃、執行時間、QPS、資源佔用
• 切換策略：Big Bang (一次性) vs 分階段 (雙寫/同步/驗證/切換)

階段 1：Schema 遷移與驗證 (2~6 週)

1. 使用 SQL Developer / SCT 執行 Schema 遷移
2. 人工修正：型別映射、預設值、註釋、Check Constraint、Index 定義
3. 建立 Oracle 目標庫，驗證結構完整性
4. 核心表抽樣資料對比 (行數、Min/Max/Count Distinct、Checksum)

階段 2：資料遷移與同步 (視資料量)

階段 3：應用改造與雙寫驗證 (4~12 週)

• 應用 SQL 改寫：ORM 映射、原生 SQL、動態 SQL 全面排查
• 雙寫模式：應用同時寫 MySQL (主) + Oracle (從)，讀仍走 MySQL
• 資料對帳：定時/觸發比對核心表，差異自動告警、人工修復
• 效能基準測試：Oracle 核心 SQL 執行計劃、綁定變數窺探、統計資訊收集

階段 4：切換與回滾預案 (1 週)

1. 業務低峰期凍結寫入 (或最後一次雙寫同步)
2. 全量最終一致性驗證
3. DNS/VIP/連線池切換到 Oracle
4. 觀察期 24~72 小時，保留 MySQL 唯讀回滾通道
5. 確認無異常，下線 MySQL，歸檔備份

五、常見坑點與規避清單

六、驗證與回歸測試體系

1. 結構驗證

-- 表/欄位/索引/約束/視圖/同義詞 逐項對比
SELECT table_name, column_name, data_type, data_length, nullable, data_default
FROM user_tab_columns WHERE table_name IN (...)
MINUS
SELECT ... FROM mysql_schema_export;

2. 資料驗證

-- 核心表: 行數、主鍵 Min/Max、Checksum (ORA_HASH/DBMS_CRYPTO)
SELECT COUNT(*), MIN(id), MAX(id), SUM(ORA_HASH(row_data)) FROM core_table;

-- 抽樣逐行對比 (關鍵業務表)
SELECT * FROM mysql_core_table
MINUS
SELECT * FROM oracle_core_table;

3. SQL 回歸測試

• 收集 MySQL 生產環境 Top 200 SQL (按執行頻率/延遲)
• 在 Oracle 執行，對比執行計劃、邏輯讀、執行時間
• 建立自動化測試套件 (JMeter / 自研框架)，每日跑批回歸

總結

1. 工具輔助、人工主導：SQL Developer/SCT 解決 80% Schema，核心 PL/SQL 需重寫
2. 型別映射是基石：CHAR 語義、TIMESTAMP 時區、空字串語義、大小寫敏感度
3. 分階段雙寫驗證：Big Bang 風險極高，雙寫 + 對帳 + 效能基準是標準路徑
4. 統計資訊與綁定變數：Oracle 效能極依賴統計資訊新鮮度與綁定變數窺探
5. 授權成本試算：EE 選項費用可能超過遷移專案本身，必須早期試算
6. 團隊技能轉型：DBA/開發需掌握 PL/SQL、AWR/ASH 分析、Oracle 架構原理

MySQL → Oracle 遷移不僅是技術遷移，更是**架構、成本、技能、風險**的綜合工程決策。若無強制合規/生態綁定需求，**MySQL 8.0 + InnoDB Cluster / MGR + 讀寫分離 + 分庫分表**往往能以 1/10 成本滿足 99% 業務場景。遷移前請三思：是否真正需要 Oracle？