隨著業務增長,MySQL 在高併發、大數據量下出現連接緩慢、甚至宕機,企業常規劃遷移至 Oracle。但兩者在 SQL 語法、資料類型、函數語義上存在顯著差異,若不系統處理,遷移後將引發大量應用錯誤。本文彙整 8 大核心差異點與解決方案。
-- 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)。
-- MySQL: "O'Reilly" 或 'O\'Reilly'
-- Oracle: 僅支援單引號,單引號需轉義為兩個單引號
INSERT INTO t VALUES ('O''Reilly'); -- 正確
應用層統一實施 REPLACE(str, '''', '''''') 轉義函數。
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;
SELECT * FROM employees
WHERE department_id = 60
ORDER BY hire_date
OFFSET 80 ROWS FETCH NEXT 20 ROWS ONLY;
優點:語法標準、可讀性強、優化器支援更好。
VARCHAR2 最大 4000 位元組 (12c+ MAX_STRING_SIZE=EXTENDED 可達 32K);CLOB 搭配 DBMS_LOB 操作;| 操作 | MySQL | Oracle |
|---|---|---|
| 當前時間 | 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) |
NOT NULL 欄位允許插入 '' (空字串);NOT NULL 欄位禁止 NULL,但 '' 視同 NULL;' ' 或業務預設值,否則導入報錯。-- 兩者語義相同,但 INSTR 可使用函數索引
WHERE col LIKE '%keyword%'
WHERE INSTR(col, 'keyword') > 0
-- 建立函數索引加速
CREATE INDEX idx_col_instr ON t(UPPER(col)); -- 配合 UPPER(INSTR(...))
Java 中 PreparedStatement、ResultSet、Connection 需在 finally 區塊或 try-with-resources 關閉,避免 ORA-01000: maximum open cursors exceeded。
| 項目 | MySQL | Oracle | 處理方式 |
|---|---|---|---|
| 自增主鍵 | AUTO_INCREMENT | SEQUENCE + TRIGGER / IDENTITY | 重構 INSERT 邏輯 |
| 分頁 | LIMIT | ROWNUM / OFFSET FETCH | 統一分頁工具類 |
| 長文本 | TEXT/MEDIUMTEXT | CLOB + DBMS_LOB | 改寫 DAO 層 |
| 日期函數 | NOW/DATE_FORMAT | SYSDATE/TO_CHAR | 建立兼容函數庫 |
| 空字串 | 允許 | 視同 NULL | ETL 階段轉換 |
| 大小寫敏感 | 依 collation | 預設不敏感 (NLS_COMP) | 統一大寫比較 |
MySQL 到 Oracle 遷移不僅是語法轉換,更是架構重構良機。建議建立「語法轉換層」或「ORM 抽象層」吸收差異,配合自動化測試與灰度發布,確保業務零感知遷移。