MySQL 遷移 Oracle 完整避坑指南：自增、引號、分頁、長字串、日期、空值、模糊查詢七大差異

遷移背景

業務增長導致 MySQL 連線數暴增、單表億級查詢緩慢，決定遷移至 Oracle 企業版。應用層以 PHP/Java 為主，需同步改造。

七大核心差異與改造方案

1. 自增主鍵 → Sequence + Trigger

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- Oracle
CREATE SEQUENCE seq_tbl_id INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999 NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER trg_tbl_bir
BEFORE INSERT ON tbl
FOR EACH ROW
BEGIN
    SELECT seq_tbl_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/
-- INSERT 時不指定 id，觸發器自動填充

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

-- MySQL 允許雙引號
SELECT "hello", 'world';

-- Oracle 僅單引號，單引號轉義為兩個單引號
SELECT 'hello', 'world' FROM dual;
-- 應用層寫入前：str.replace("'", "''")

3. LIMIT 分頁 → ROWNUM 子查詢 / OFFSET FETCH (12c+)

-- MySQL
SELECT * FROM t ORDER BY id LIMIT 20 OFFSET 40;

-- Oracle 11g 經典寫法 (ID 為唯一鍵)
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (
        SELECT * FROM t ORDER BY id
    ) a WHERE ROWNUM <= 60
) WHERE rn > 40;

-- Oracle 12c+ 標準語法
SELECT * FROM t ORDER BY id OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

4. 長字串 (>4000 字元) → CLOB + DBMS_LOB

-- 建表
CREATE TABLE docs (id NUMBER PRIMARY KEY, content CLOB);

-- 插入 (PL/SQL)
DECLARE
    l_clob CLOB;
BEGIN
    INSERT INTO docs (id, content) VALUES (1, EMPTY_CLOB()) RETURNING content INTO l_clob;
    DBMS_LOB.WRITEAPPEND(l_clob, LENGTH(:long_text), :long_text);
    COMMIT;
END;
/
-- 應用層：JDBC setCharacterStream / OraclePreparedStatement.setClob

5. 日期型別與運算差異

6. 空值處理：NOT NULL 约束更嚴格

• MySQL：NOT NULL 欄位可插入空字串 '' 或 0。
• Oracle：NOT NULL 僅允許非 NULL，空字串 '' 視同 NULL（Oracle 無空字串概念）。
• 遷移前：UPDATE t SET col = ' ' WHERE col = '' OR col IS NULL;（用空格佔位）。

7. 模糊查詢優化：INSTR 替代 LIKE

-- MySQL
SELECT * FROM t WHERE name LIKE '%keyword%';

-- Oracle LIKE 不走索引，改用 INSTR (函數索引可加速)
SELECT * FROM t WHERE INSTR(name, 'keyword') > 0;
-- 建立函數索引
CREATE INDEX idx_t_name_instr ON t (INSTR(name, 'keyword'));

其他注意事項

• 游標/結果集釋放：Oracle 显式 CLOSE cursor、FREE statement，防洩漏。
• 大小寫敏感：Oracle 物件名預設大寫，雙引號保留大小寫，應用層統一大寫或雙引號。
• 驅動程式：JDBC ojdbc8.jar、PHP oci8、Python cx_Oracle。
• 測試策略：建立遷移驗證腳本，核對行數、Checksum、關鍵業務查詢結果一致性。

遷移工具推薦

• Oracle SQL Developer Migration Workbench：官方免費 GUI，支援 Schema/資料/程式碼轉換。
• AWS Schema Conversion Tool (SCT)：支援異構遷移評估報告。
• Flyway / Liquibase：版本控制式遷移腳本管理。

系統性處理七大差異，配合自動化驗證，讓 MySQL→Oracle 遷移可控、可驗證、可回滾。