遷移背景

MySQL 適合中小型應用，Oracle 承擔超大規模核心業務。遷移不僅是語法轉換，更是**架構模式、數據類型、並發模型、運維體系**的全維度重構。本文梳理 8 大必踩坑點與標準化轉換方案。

1. 自增主鍵：AUTO_INCREMENT → SEQUENCE + 觸發器

MySQL

CREATE TABLE t (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO t (name) VALUES ('test');  -- id 自動賦值

Oracle

-- 1. 創建序列
CREATE SEQUENCE seq_t_id 
  INCREMENT BY 1 START WITH 1 
  MAXVALUE 9999999999 CYCLE NOCACHE;

-- 2. 創建表（無自增屬性）
CREATE TABLE t (id NUMBER(10) PRIMARY KEY, name VARCHAR2(50));

-- 3. 創建觸發器自動填充
CREATE OR REPLACE TRIGGER trg_t_bir
BEFORE INSERT ON t
FOR EACH ROW
BEGIN
  SELECT seq_t_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/

-- 4. 插入時無需指定 id
INSERT INTO t (name) VALUES ('test');

遷移工具提示：自動化工具（如 AWS SCT、Ora2Pg）可自動生成 Sequence+Trigger 組合。

2. 字符串引號：雙引號 → 單引號 + 轉義

場景	MySQL	Oracle	轉換規則
字符串字面量	'abc' 或 "abc"	僅 'abc'	統一改單引號
字符串包含單引號	'don\'t' 或 "don't"	'don''t'	單引號轉雙單引號
標識符（表/列名）	`table` 或 "table"	"TABLE"（大寫）	Oracle 無引號默認大寫，雙引號保持大小寫

3. 分頁查詢：LIMIT → ROWNUM/OFFSET...FETCH

MySQL 簡單直觀

SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 40;  -- 第 3 頁，每頁 20 條

Oracle 12c+ 標準語法（推薦）

SELECT * FROM t ORDER BY id 
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

Oracle 11g/10g 經典寫法（嵌套子查詢）

-- 寫法一：雙層 ROWNUM
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM t ORDER BY id
  ) a WHERE ROWNUM <= 60
) WHERE rn > 40;

-- 寫法二：ROW_NUMBER() 分析函數（性能更好）
SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY id) rn FROM t
) WHERE rn BETWEEN 41 AND 60;

關鍵點：Oracle ROWNUM 是**結果集生成時**賦值，**不能用 ROWNUM > 40 直接過濾**（永無結果），必須先賦值再外層過濾。

4. 長文本：TEXT/BLOB → CLOB/BLOB + DBMS_LOB

MySQL	Oracle	注意事項
TEXT (64KB)	CLOB (4GB)
MEDIUMTEXT (16MB)	CLOB
LONGTEXT (4GB)	CLOB
BLOB	BLOB

Oracle 插入超長字符串（>4000 字節）必須用 DBMS_LOB

DECLARE
  l_clob CLOB;
BEGIN
  INSERT INTO t (id, content) VALUES (1, EMPTY_CLOB()) RETURNING content INTO l_clob;
  DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(:long_text), :long_text);
  COMMIT;
END;
/

應用層（Java/JDBC）可用 PreparedStatement.setCharacterStream() 或 setClob() 直接流式寫入。

5. 日期時間：類型統一、函數差異大

功能	MySQL	Oracle
當前時間	NOW(), SYSDATE()	SYSDATE (精確到秒), SYSTIMESTAMP (精確到毫秒)
字符串轉日期	STR_TO_DATE('2024-01-01','%Y-%m-%d')	TO_DATE('2024-01-01','YYYY-MM-DD')
日期轉字符串	DATE_FORMAT(dt,'%Y-%m-%d %H:%i:%s')	TO_CHAR(dt,'YYYY-MM-DD HH24:MI:SS')
日期運算（+7天）	DATE_ADD(NOW(), INTERVAL 7 DAY)	SYSDATE + 7 (數字代表天)
日期運算（+3小時）	DATE_ADD(NOW(), INTERVAL 3 HOUR)	SYSDATE + 3/24
截取日期	DATE(dt)	TRUNC(dt)

6. 空值語義：MySQL 空字符串 ≠ NULL，Oracle 空字符串 = NULL

-- MySQL
INSERT INTO t (col) VALUES ('');  -- 合法，長度 0
INSERT INTO t (col) VALUES (NULL); -- 合法（除非 NOT NULL）

-- Oracle
INSERT INTO t (col) VALUES ('');  -- 等同於 INSERT NULL，若 NOT NULL 會報錯
INSERT INTO t (col) VALUES (NULL); -- 同樣

遷移策略

• 導數據前：**將所有空字符串 '' 替換為單空格 ' ' 或特定標記 '[EMPTY]'**
• 應用層：統一理解「空值 = NULL」，避免業務邏輯依賴「空字符串 ≠ NULL」

7. 模糊查詢：LIKE vs INSTR

寫法	MySQL	Oracle	索引利用
col LIKE '%abc%'	✅ 支持	✅ 支持	❌ 全表掃描（前導通配符）
col LIKE 'abc%'	✅ 走索引	✅ 走索引	✅ 索引範圍掃描
INSTR(col, 'abc') > 0	✅ 支持	✅ 推薦	❌ 通常不走索引（除非函數索引）

建議：Oracle 中優先用 LIKE 'prefix%' 走索引；全文搜索場景使用 **Oracle Text** 或 **Elasticsearch** 外部引擎。

8. 游標與結果集：顯式關閉，防洩漏

Oracle PL/SQL 中：

CURSOR c_data IS SELECT * FROM t;
v_rec c_data%ROWTYPE;
OPEN c_data;
LOOP
  FETCH c_data INTO v_rec;
  EXIT WHEN c_data%NOTFOUND;
  -- 處理
END LOOP;
CLOSE c_data;  -- 必須顯式關閉！

Java/JDBC：ResultSet、Statement、Connection 必須在 finally 塊中關閉，建議使用 try-with-resources (JDK 7+)。

遷移工具鏈推薦

• AWS Schema Conversion Tool (SCT)：免費、圖形化、支持複雜對象轉換、生成評估報告
• Ora2Pg：開源 Perl 腳本，Oracle→PostgreSQL/MySQL，適合自動化流水線
• SQLines：在線/命令行工具，支持多種方言互轉
• DataX / Canal + 自定義 ETL：數據同步階段，處理類型映射、分批提交、斷點續傳

遷移驗收清單

1. ✅ 所有表結構、索引、約束、觸發器、序列、視圖、包/過程/函數
2. ✅ 全量數據行數、MD5 校驗、關鍵業務字段抽樣對比
3. ✅ 應用端 SQL 改寫完成率 100%，無 MySQL 方言殘留
4. ✅ 性能基準測試：核心 SQL 在 Oracle 上執行時間 ≤ MySQL × 1.5
5. ✅ 並發壓測：連接池、鎖競爭、Undo/Redo 表空間水位正常
6. ✅ 容災演練：Data Guard / GoldenGate 同步延遲 < 5s，切換演練成功

結語

MySQL → Oracle 遷移，**語法轉換只佔 30% 工作量**，70% 在於**架構適配、性能調優、運維體系重建、業務驗證**。建立標準化轉換規範、引入自動化工具、分階段灰度驗證，才能將風險控制在可接受範圍內，平滑完成核心系統上雲/遷移。