萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

MySQL 遷移 Oracle 核心差異與避坑指南:自增、分頁、長字串、日期、編碼全解析

遷移背景

隨著業務增長,MySQL 在高併發、大數據量下出現連接緩慢、甚至宕機,企業常規劃遷移至 Oracle。但兩者在 SQL 語法、資料類型、函數語義上存在顯著差異,若不系統處理,遷移後將引發大量應用錯誤。本文彙整 8 大核心差異點與解決方案。

1. 自增欄位:AUTO_INCREMENT → SEQUENCE + TRIGGER

-- Oracle 建立序列
CREATE SEQUENCE seq_emp_id 
  INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999 NOCACHE NOCYCLE;

-- 建立觸發器自動賦值
CREATE OR REPLACE TRIGGER trg_emp_bir
BEFORE INSERT ON employees
FOR EACH ROW
WHEN (NEW.emp_id IS NULL)
BEGIN
  SELECT seq_emp_id.NEXTVAL INTO :NEW.emp_id FROM dual;
END;

12c+ 新語法:emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY (內建 Identity Column)。

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

-- MySQL: "O'Reilly" 或 'O\'Reilly'
-- Oracle: 僅支援單引號,單引號需轉義為兩個單引號
INSERT INTO t VALUES ('O''Reilly');  -- 正確

應用層統一實施 REPLACE(str, '''', '''''') 轉義函數。

3. 分頁查詢:LIMIT → ROWNUM/分析函數

方案一:內層 ROWNUM + 外層過濾 (經典)

SELECT * FROM (
  SELECT ROWNUM rn, a.* FROM (
    SELECT * FROM employees WHERE department_id = 60 ORDER BY hire_date
  ) a WHERE ROWNUM <= 100
) WHERE rn > 80;

方案二:OFFSET/FETCH (12c+ 標準 SQL)

SELECT * FROM employees 
WHERE department_id = 60 
ORDER BY hire_date 
OFFSET 80 ROWS FETCH NEXT 20 ROWS ONLY;

優點:語法標準、可讀性強、優化器支援更好。

4. 長字串處理:VARCHAR2(4000) → CLOB

  • Oracle VARCHAR2 最大 4000 位元組 (12c+ MAX_STRING_SIZE=EXTENDED 可達 32K);
  • 超長字串必須使用 CLOB 搭配 DBMS_LOB 操作;
  • 應用層寫入前驗證長度與非空,超限拋出業務異常而非資料庫錯誤。

5. 日期時間:DATE/TIMESTAMP 差異

操作MySQLOracle
當前時間NOW()SYSDATE (精確到秒) / SYSTIMESTAMP (精確到毫秒+時區)
字串轉日期STR_TO_DATE('2024-01-15','%Y-%m-%d')TO_DATE('2024-01-15','YYYY-MM-DD')
日期轉字串DATE_FORMAT(dt,'%Y-%m-%d')TO_CHAR(dt,'YYYY-MM-DD')
日期運算DATE_SUB(NOW(), INTERVAL 7 DAY)SYSDATE - 7 (數字代表天)
加減月份DATE_ADD(dt, INTERVAL 3 MONTH)ADD_MONTHS(dt, 3)

6. 空值語義:空字串 ≠ NULL

  • MySQL:NOT NULL 欄位允許插入 '' (空字串);
  • Oracle:NOT NULL 欄位禁止 NULL,但 '' 視同 NULL;
  • 遷移時需將 MySQL 空字串轉為單空格 ' ' 或業務預設值,否則導入報錯。

7. 模糊查詢優化:LIKE → INSTR

-- 兩者語義相同,但 INSTR 可使用函數索引
WHERE col LIKE '%keyword%'
WHERE INSTR(col, 'keyword') > 0

-- 建立函數索引加速
CREATE INDEX idx_col_instr ON t(UPPER(col)); -- 配合 UPPER(INSTR(...))

8. 資源釋放:ResultSet/Statement 必須顯式關閉

Java 中 PreparedStatement、ResultSet、Connection 需在 finally 區塊或 try-with-resources 關閉,避免 ORA-01000: maximum open cursors exceeded。

遷移檢查清單

項目MySQLOracle處理方式
自增主鍵AUTO_INCREMENTSEQUENCE + TRIGGER / IDENTITY重構 INSERT 邏輯
分頁LIMITROWNUM / OFFSET FETCH統一分頁工具類
長文本TEXT/MEDIUMTEXTCLOB + DBMS_LOB改寫 DAO 層
日期函數NOW/DATE_FORMATSYSDATE/TO_CHAR建立兼容函數庫
空字串允許視同 NULLETL 階段轉換
大小寫敏感依 collation預設不敏感 (NLS_COMP)統一大寫比較

總結

MySQL 到 Oracle 遷移不僅是語法轉換,更是架構重構良機。建議建立「語法轉換層」或「ORM 抽象層」吸收差異,配合自動化測試與灰度發布,確保業務零感知遷移。

copyright © 萬盛學電腦網 all rights reserved