PHP 實現 Oracle 到 MySQL 異構遷移完整實戰

遷移動機

• 授權成本：MySQL 開源免費，大幅降低 TCO。
• 功能成熟：5.1+ 支援 Partitioning、Replication、Cluster、JSON、GIS。
• 運維簡單：輕量級、社群生態豐富。

為何自研腳本

• 數百張表，手工不可行。
• 免費工具（MySQL Migration Toolkit 等）型別映射不準、資料截斷、不可靠。
• 自研可即時調整映射邏輯、異常處理、驗證對帳。

技術棧

• PHP 5 + oci8 擴展（Oracle 連線）。
• MySQL 5.1+（mysqli/PDO）。

一、結構遷移

1. 取得所有表

SELECT table_name FROM user_tables;

2. 取得單表欄位定義

SELECT column_name, data_type, data_length, nullable, data_default
  FROM user_tab_columns
 WHERE table_name = UPPER(:tname)
 ORDER BY column_id;

3. 型別映射表

⚠️ Oracle 無自增欄位，**PK 改為 MySQL AUTO_INCREMENT**（需識別 Sequence 關聯欄位，或統一 PK 設自增）。

4. 取得約束與索引

-- PK/UK/FK
SELECT c.constraint_name, c.constraint_type, c.r_constraint_name, c.delete_rule,
       cc.column_name
  FROM user_constraints c, user_cons_columns cc
 WHERE c.table_name = UPPER(:tname)
   AND c.constraint_type != 'C'
   AND c.constraint_name = cc.constraint_name
   AND c.owner = cc.owner
   AND c.table_name = cc.table_name
 ORDER BY c.constraint_type, c.constraint_name, cc.position;

-- Index
SELECT t.index_name, t.column_name, i.index_type
  FROM user_ind_columns t, user_indexes i
 WHERE t.index_name = i.index_name
   AND t.table_name = i.table_name
   AND t.table_name = UPPER(:tname);

5. 組裝 CREATE TABLE DDL

依序：欄位定義 → PK → UK → FK → Index。注意 MySQL FK 需 InnoDB、參考表先建立。

二、View 遷移

SELECT view_name, text FROM user_views;

**不兼容處理**：MySQL View 不允許 FROM (subquery) 直接建立。
解法：將子查詢另建為 Base View，主 View 引用 Base View。

三、資料遷移

核心邏輯

1. SELECT Oracle 資料（游標分批，避免記憶體爆炸）。
2. 型別轉換：
   • DATE → TO_CHAR(dt, 'YYYY-MM-DD HH24:MI:SS') 符合 MySQL DATETIME 格式。
   • CLOB → oci_fetch_array($stid, OCI_ASSOC|OCI_RETURN_NULLS|OCI_RETURN_LOBS) 取出完整內容寫入 MySQL TEXT。
3. 拼裝 INSERT 或 LOAD DATA 批次寫入。

MySQL 關鍵配置

[mysqld]
max_allowed_packet = 16M  -- Windows 預設 1M 導致大 CLOB/TEXT 匯入失敗
innodb_buffer_pool_size = 1G  -- 視記憶體調大
bulk_insert_buffer_size = 64M

Linux 預設 16M 通常無問題。

四、無法自動遷移的物件

• Trigger、Stored Procedure、Function、Package：PL/SQL 與 MySQL Stored Program 語法差異極大，**必須人工重寫**。
• 建議：清單化 → 功能對照表 → 逐一改寫測試。

五、遷移後驗證

1. 表結構 DESC 對比、行數 COUNT(*) 對比。
2. 關鍵業務 SQL 在 MySQL 執行計畫與結果集驗證。
3. 應用端切換測試（Read Replica → 切主）。

總結

Oracle → MySQL 遷移：**結構與資料可高度自動化**，View 需中介轉換，**程式碼物件必須人工重寫**。關鍵在於**精準型別映射、CLOB/日期處理、MySQL 參數預調、分批提交與雙向驗證**。