Oracle 到 MySQL 遷移：核心差異與解決方案
企業將既有 Oracle 系統遷移至 MySQL 可大幅降低授權成本，但需處理語法、資料類型、函數、PL/SQL 等多重差異。本文整理遷移關鍵點與相容性設計原則。
一、大小寫敏感度 (Linux 環境關鍵)
| 項目 | Oracle | MySQL (Linux) | 解決方案 |
|---|
| 表名/欄位名 | 不敏感 (內部轉大寫) | 敏感 (檔案系統決定) | 統一採用小寫 + 底線命名法，lower_case_table_names=1 強制不敏感 |
二、保留字衝突
- Oracle 允許保留字作識別符 (如
INTERVAL、SHOW)
- MySQL 需用反引號包裹：
\`interval\` 或 table.\`interval\`
- 建議：遷移前掃描所有識別符，自動加反引號或改名
三、資料類型對照表
| Oracle | MySQL 對應 | 備註 |
|---|
| VARCHAR2(n) | VARCHAR(n) / VARCHAR(n CHAR) | MySQL 5.0+ VARCHAR 65535 位元組 |
| NUMBER(p,s) | DECIMAL(p,s) / NUMERIC(p,s) | 精確數值，儲存空間較大 |
| NUMBER | DOUBLE / DECIMAL(38,10) | 視精度需求選擇 |
| DATE | DATETIME / TIMESTAMP | Oracle DATE 含時分秒 |
| TIMESTAMP | TIMESTAMP(6) | 微秒精度 |
| CLOB | LONGTEXT / MEDIUMTEXT | 64KB / 16MB / 4GB |
| BLOB | LONGBLOB | |
| RAW | VARBINARY / BLOB | |
| ROWID | 無直接對應 | 用 PRIMARY KEY 替代 |
四、自動遞增機制
| Oracle | MySQL |
|---|
| SEQUENCE 物件 | AUTO_INCREMENT 欄位屬性 |
| 獨立管理、可跨表、CYCLE/CACHE | 綁定單表、單調遞增、重啟不重置 (8.0+ 持久化) |
遷移策略：
- 單表主鍵 → 直接改
AUTO_INCREMENT
- 跨表共用 Sequence → 建立專用序列表
CREATE TABLE seq_table (id BIGINT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64)); 搭配 LAST_INSERT_ID()
五、索引長度限制
- MySQL 4.1.2+：MyISAM/InnoDB 單索引 ≤ 1000 位元組 (InnoDB 可調
innodb_large_prefix)
- UTF8MB4：每字元 4 位元組 → 最大 255 字元
- Oracle 限制較寬鬆 (約 32 字元 * 區塊大小)
- 解決：縮短索引欄位、使用前綴索引
INDEX idx_name (col(191))、升級 MySQL 5.7+ 支援 3072 位元組
六、分頁查詢語法
-- Oracle 12c+
SELECT * FROM t ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-- MySQL / PostgreSQL
SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20;
-- 或
SELECT * FROM t ORDER BY id LIMIT 20, 10;
七、函數與語法差異精選
| 功能 | Oracle | MySQL |
|---|
| 字串連接 | \|\| 或 CONCAT | CONCAT() 或 \|\| (需 PIPES_AS_CONCAT SQL_MODE) |
| 空值處理 | NVL(a,b), COALESCE | IFNULL(a,b), COALESCE |
| 日期加減 | SYSDATE + 1 | DATE_ADD(NOW(), INTERVAL 1 DAY) |
| 截取子字串 | SUBSTR(str, pos, len) | SUBSTRING(str, pos, len) |
| 轉換字元集 | CONVERT | CONVERT(str USING utf8mb4) |
| 合併插入 | MERGE INTO | INSERT ... ON DUPLICATE KEY UPDATE |
| 階層查詢 | CONNECT BY PRIOR | WITH RECURSIVE CTE (8.0+) |
八、相容性設計原則 (新專案適用)
- 遵循 ANSI SQL 標準： 避免廠商專屬語法
- 統一命名規範： 全小寫 + 底線，避免保留字
- 抽象資料存取層： 使用 ORM (Hibernate、MyBatis、Entity Framework) 或自建 DAL
- 分頁統一介面： 應用層封裝
PageRequest，底層適配不同方言
- 測試驗證： CI/CD 管線同時跑 Oracle/MySQL 整合測試
遷移非一日之功，建議採用「雙寫驗證 → 灰度切換 → 全量切換」策略，並保留回滾方案。工具推薦：ora2pg、MySQL Workbench Migration Wizard、AWS SCT。
