MySQL 遷移至 Oracle 完整注意事項與雙向兼容指南

資料庫異構遷移是常見架構演進場景。本文系統整理 MySQL → Oracle 遷移的 7 大核心差異點，並補充 Oracle → MySQL 反向遷移的 5 大兼容性建議，助您平滑落地。

一、MySQL → Oracle 遷移必處理的 7 大差異

1. 自增主鍵：AUTO_INCREMENT → Sequence + Trigger

MySQL：id INT AUTO_INCREMENT PRIMARY KEY
Oracle：需建立 Sequence 與 Trigger（或 12c+ 使用 GENERATED AS IDENTITY）。

CREATE SEQUENCE seq_tbl_id INCREMENT BY 1 START WITH 1 MAXVALUE 99999 NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER trg_tbl_id
BEFORE INSERT ON tbl FOR EACH ROW
BEGIN SELECT seq_tbl_id.NEXTVAL INTO :NEW.id FROM dual; END;

INSERT 時：INSERT INTO tbl (id, ...) VALUES (seq_tbl_id.NEXTVAL, ...)

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

MySQL 允許雙引號包裹字串；Oracle **僅支援單引號**。
遷移時：所有單引號 ' 替換為雙單引號 ''。

3. 長字串處理：TEXT/BLOB → CLOB/BLOB

Oracle VARCHAR2 最大 4000 位元組。超長字串必須用 CLOB，配合 DBMS_LOB 套件讀寫。
ETL 階段需做長度檢查與非空驗證。

4. 分頁查詢：LIMIT → ROWNUM 子查詢

MySQL：SELECT * FROM t LIMIT 20, 10
Oracle 兩種經典寫法：

-- 寫法一：雙層子查詢
SELECT * FROM (
  SELECT a.*, ROWNUM rn FROM (
    SELECT * FROM t WHERE 條件 ORDER BY 排序
  ) a WHERE ROWNUM <= 30
) WHERE rn > 20;

-- 寫法二：單層子查詢（12c+ 可用 OFFSET/FETCH）
SELECT * FROM t WHERE 條件 ORDER BY 排序
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

5. 日期型別與運算：DATE/TIME → DATE + SYSDATE

MySQL 分 DATE/TIME/DATETIME；Oracle 單一 DATE 含年月日時分秒。
日期運算差異：

6. 模糊查詢優化：LIKE → INSTR

Oracle LIKE '%str%' **無法使用索引**。改用 INSTR(col, 'str') > 0 可走索引（需建立 Function-Based Index），效能大幅提升。

7. 空字串與 NULL：Oracle 視 '' 為 NULL

MySQL NOT NULL 欄位可存空字串 ''；Oracle NOT NULL **禁止** 空字串（視同 NULL）。
遷移腳本需將 '' 轉為 ' '（單空格）或保留 NULL，視業務邏輯決定。

二、Oracle → MySQL 反向遷移兼容性指南

1. 大小寫敏感度

• Oracle 不區分大小寫（除雙引號識別符）。
• MySQL 在 Linux 上**區分**表名/庫名大小寫（對應檔案系統）。
  → 統一採用**小寫**命名，應用層 SQL 亦統一小寫。

2. 保留字衝突

Oracle 允許保留字作識別符；MySQL 禁止。
→ 避免使用保留字；若必須用，MySQL 用反引號 \`interval\` 或 tbl.interval 包裹。

3. 自增實現差異

Oracle Sequence → MySQL AUTO_INCREMENT。
→ 遷移腳本自動轉換；極少數需保留 Sequence 語義時，另建序號表模擬。

4. 資料型別對照

5. 索引長度限制

MySQL (InnoDB/MyISAM) 索引鍵長度上限 **1000 位元組**（UTF-8 約 333 字元）。Oracle 限制寬鬆得多。
→ 遷移前檢查複合索引定義，必要時縮減欄位長度或改用前綴索引。

三、打造跨資料庫兼容應用的原則

1. 堅持標準 SQL：避免專有語法（MySQL 反引號、Oracle DUAL、PostgreSQL LIMIT/OFFSET）。
2. 拒絕保留字：命名規範加前綴 _（如 _id, _order）永不衝突。
3. 統一大小寫：全專案採用小寫表名/欄位名，ORM 框架配置對應策略。
4. 抽象分頁邏輯：封裝 PageHelper / Dialect 層，上層業務碼零感知。
5. 工具輔助：使用 Convert MySQL to Oracle 等工具時，注意「名稱轉大寫」選項控制。