MySQL 遷移 Oracle 完全指南：八大差異與應對策略

背景

隨著業務增長，MySQL 在高併發、大數據量下逐現瓶頸（連線緩慢、甚至宕機），許多團隊選擇遷移至 Oracle。但兩者在 SQL 語法、資料型別、函數行為上存在顯著差異，若不妥善處理，將導致應用層大量報錯。本文歸納八大核心差異點，並提供對應解決方案，助力平滑遷移。

1. 自動遞增欄位：Sequence 取代 AUTO_INCREMENT

MySQL 使用 AUTO_INCREMENT，插入時無需指定值；Oracle 無此屬性，需建立 Sequence（序列） 搭配觸發器或在 INSERT 中直接引用 SEQ_NAME.NEXTVAL。

CREATE SEQUENCE seq_table_name
  INCREMENT BY 1
  START WITH 1
  MAXVALUE 999999
  NOCACHE
  NOCYCLE;

插入範例：INSERT INTO t(id, col) VALUES (seq_table_name.NEXTVAL, 'val');

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

MySQL 允許雙引號包裹字串，Oracle 僅支援單引號。遷移時需將所有單引號替換為兩個單引號（''），例如 'O''Reilly'。

3. 分頁查詢：ROWNUM 子查詢替代 LIMIT

MySQL LIMIT offset, rows 簡潔直觀；Oracle 需利用 ROWNUM 搭配巢狀子查詢。兩種常用寫法（假設每頁 20 筆，查第 5 頁）：

寫法一：雙層子查詢 + ID 回表

SELECT id, col1, col2
FROM   table_name
WHERE  id IN (
  SELECT id FROM (
    SELECT ROWNUM AS rn, id
    FROM   table_name
    WHERE  condition1
    ORDER BY condition2
  )
  WHERE  rn > 80 AND rn <= 100
)
ORDER BY condition3;

寫法二：單層子查詢 + 全欄位投影

SELECT * FROM (
  SELECT ROWNUM AS rn, t.*
  FROM (
    SELECT col1, col2, ...
    FROM   table_name
    WHERE  condition1
    ORDER BY condition2
  ) t
)
WHERE rn > 80 AND rn <= 100
ORDER BY condition3;

關鍵點：ROWNUM 只能用 < 或 <=，不能用 >；必須先排序再產生 ROWNUM，否則分頁順序錯亂。

4. 長字串處理：超過 4000 位元組改用 CLOB

Oracle VARCHAR2 上限 4000 bytes。若需儲存更長文本，欄位型別需定為 CLOB，並使用 DBMS_LOB 套件進行讀寫。應用層插入/更新前務必檢查非空與長度，超限時回傳錯誤而非截斷。

5. 日期時間型別與運算

Oracle DATE 型別已含時分秒，無需像 MySQL 區分 DATE/TIME。

6. 空值與 NOT NULL 陷阱

MySQL 允許 NOT NULL 欄位存入空字串 ''；Oracle 視空字串同 NULL，導入時會拋錯。解決：遷移腳本中將 NULL 或 '' 統一轉為單一空格 ' ' 或預設值。

7. 模糊查詢效能：LIKE → INSTR

Oracle 中 col LIKE '%abc%' 無法使用索引，全表掃描。改用 INSTR(col, 'abc') > 0 可獲得較佳執行計畫（仍屬全掃，但可配合 Function-Based Index 優化）。

8. 游標與結果集釋放

Oracle PL/SQL 程序結束時，務必顯式關閉 CURSOR、釋放 REF CURSOR，避免游標洩漏導致 ORA-01000: maximum open cursors exceeded。

遷移檢查清單

• 建立對應 Sequence 並修正所有 INSERT 語句
• 全域搜尋替換雙引號、單引號轉義
• 重寫所有分頁 SQL，建議封裝為通用分頁函數/視圖
• 評估長文本欄位，決定是否改為 CLOB 並調整讀寫邏輯
• 統一日期函數呼叫，修正日期運算邏輯
• 清洗空字串資料，確保符合 Oracle NOT NULL 限制
• 針對高頻 LIKE 查詢建立 Function-Based Index 或改用 Oracle Text
• 代碼審查所有游標使用處，補上 CLOSE/FREE