核心差異對照表
| 類別 | MySQL | Oracle | 遷移策略 |
|---|
| 自增主鍵 | AUTO_INCREMENT | SEQUENCE + 觸發器 / IDENTITY (12c+) | 建立 SEQUENCE，INSERT 時 seq.NEXTVAL；或改用 12c GENERATED AS IDENTITY |
| 字串引號 | 單引號、雙雙引號 | **僅單引號** | 全域替換雙引號為單引號；單引號內嵌單引號寫兩個 '' |
| 分頁查詢 | LIMIT offset, rows | ROWNUM 子查詢 / OFFSET ... FETCH (12c+) | 改寫為標準 OFFSET m ROWS FETCH NEXT n ROWS ONLY 或 ROWNUM 套嵌 |
| 長字串 | TEXT/LONGTEXT (4GB) | CLOB (4GB)，VARCHAR2 最大 4000 字元 | >4000 字元必須用 CLOB，配合 DBMS_LOB 讀寫 |
| 日期類型 | DATE、DATETIME、TIMESTAMP | DATE (含時分秒)、TIMESTAMP (含時區) | 統一對應 DATE 或 TIMESTAMP，注意精度差異 |
| 當前時間 | NOW()、SYSDATE() | SYSDATE (無括號) | 統一用 SYSDATE (Oracle) / CURRENT_TIMESTAMP (標準) |
| 日期運算 | DATE_SUB(NOW(), INTERVAL 7 DAY) | SYSDATE - 7 (數字代表天) | Oracle 直接加減數字；間隔類型用 INTERVAL '7' DAY |
| 日期格式化 | DATE_FORMAT() | TO_CHAR(date, 'fmt') | 格式字串對照：%Y→YYYY、%m→MM、%d→DD、%H→HH24、%i→MI、%s→SS |
| 空字串 vs NULL | 不同值 | ''` 等同 `NULL` | 遷移前將空字串轉為 NULL 或單空格，否則 NOT NULL 約束報錯 |
| 模糊查詢 | LIKE '%abc%' | LIKE '%abc%' (不走索引) / INSTR(col, 'abc') > 0 | 功能性索引 (Function-Based Index) 可加速 LIKE/INSTR |
| 結果集游標 | 驅動層自動管理 | **必須顯式關閉 REF CURSOR / Statement** | 程式碼層確保 finally 區塊關閉 ResultSet/PreparedStatement |
分頁查詢改寫範本 (Oracle 11g/12c 通用)
-- 方案一：ROWNUM 兩層嵌套 (通用)
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (
SELECT id, name, created_at FROM orders WHERE status=1 ORDER BY created_at DESC
) a WHERE ROWNUM <= :endRow
) WHERE rn >= :startRow;
-- 方案二：分析函數 ROW_NUMBER() (10g+，可維護性更好)
SELECT * FROM (
SELECT id, name, created_at, ROW_NUMBER() OVER (ORDER BY created_at DESC) rn
FROM orders WHERE status=1
) WHERE rn BETWEEN :startRow AND :endRow;
-- 方案三：Oracle 12c+ 標準語法 (最簡潔)
SELECT id, name, created_at FROM orders WHERE status=1
ORDER BY created_at DESC
OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY;
日期處理遷移速查
-- MySQL: DATE_SUB(NOW(), INTERVAL 7 DAY)
-- Oracle:
SYSDATE - 7
SYSDATE - INTERVAL '7' DAY
ADD_MONTHS(SYSDATE, -1) -- 減一個月 (自動處理月末)
TRUNC(SYSDATE) -- 截取日期部分 (00:00:00)
TO_DATE('2024-01-15', 'YYYY-MM-DD')
TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
資料類型映射建議
| MySQL | Oracle 建議 | 備註 |
|---|
| TINYINT | NUMBER(3) | |
| SMALLINT | NUMBER(5) | |
| MEDIUMINT | NUMBER(7) | |
| INT / INTEGER | NUMBER(10) | |
| BIGINT | NUMBER(19) | |
| FLOAT | BINARY_FLOAT | 單精度 |
| DOUBLE | BINARY_DOUBLE | 雙精度 |
| DECIMAL(p,s) | NUMBER(p,s) | 精確數值首選 NUMBER |
| CHAR(n) | CHAR(n BYTE/CHAR) | 建議 CHAR(n CHAR) |
| VARCHAR(n) | VARCHAR2(n CHAR) | Oracle 僅用 VARCHAR2 |
| TINYTEXT | VARCHAR2(255 CHAR) | |
| TEXT | CLOB | >4000 字元 |
| MEDIUMTEXT | CLOB | |
| LONGTEXT | CLOB | |
| DATE | DATE | |
| DATETIME | DATE / TIMESTAMP(0) | |
| TIMESTAMP | TIMESTAMP(6) | 微秒精度 |
| ENUM/SET | VARCHAR2 + CHECK 約束 | Oracle 12c+ 支援 CHECK |
| JSON | JSON (21c+) / CLOB + CHECK (IS JSON) | |
遷移工具鏈推薦
- Oracle SQL Developer Migration Workbench：免費 GUI，支援異構遷移、離線捕獲、代碼轉換。
- Ora2Pg：Perl 腳本，MySQL/PostgreSQL → Oracle，自動化程度高。
- AWS Schema Conversion Tool (SCT)：支援多源多目標，雲遷移首選。
- 手動 SQL 改寫：複雜預存程序、觸發器、動態 SQL 必須人工介入。
驗收測試清單
- 行數核對：核心表
COUNT(*) 一致。
- 核心業務 SQL 執行計劃對比 (EXPLAIN PLAN)，關鍵索引生效。
- 分頁查詢首頁/末頁/中間頁數據一致。
- 日期計算邏輯 (報表、帳單週期) 結果一致。
- 長文本欄位完整性 (CLOB 讀寫)。
- 交易一致性：模擬併發轉帳、扣庫存，驗證隔離級別 (Oracle 預設 READ COMMITTED，MySQL 預設 REPEATABLE READ)。