Oracle 自增欄位實作指南：12c Identity 與 11g Sequence+Trigger 方案對比

Oracle 自增欄位實作指南：12c Identity 與 11g Sequence+Trigger 方案對比

從 MySQL 轉戰 Oracle 的開發者最容易踩的坑，莫過於「自增主鍵」的缺失。MySQL 的 AUTO_INCREMENT 用慣了，到了 Oracle 卻發現原生不支援——直到 12c 版本推出 IDENTITY 特性才徹底改寫這段歷史。本文詳細對比兩大版本的實作方案，助您因地制宜選擇最佳策略。

一、Oracle 12c+：原生 Identity Column 方案（推薦）

12c 引入 ANSI SQL 標準的 GENERATED AS IDENTITY 語法，語法簡潔、效能優異、無需額外物件維護。

CREATE TABLE t (
    id          NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1),
    name        VARCHAR2(20),
    password    VARCHAR2(20),
    CONSTRAINT pk_t PRIMARY KEY (id)
);

關鍵字解析：

• GENERATED BY DEFAULT：允許手動指定值，若為 NULL 則自動取序列值
• GENERATED ALWAYS：強制由資料庫產生，手動插入會報錯
• START WITH 1 INCREMENT BY 1：起始值與遞增步長，支援負數與大數值

二、Oracle 11g 與舊版：Sequence + Trigger 經典方案

若受限於 11g 環境，需手動建立序列與觸發器模擬自增行為。

步驟 1：建立基礎表結構

CREATE TABLE t (
    id          NUMBER,
    name        VARCHAR2(20),
    password    VARCHAR2(20),
    CONSTRAINT pk_t PRIMARY KEY (id)
);

步驟 2：建立序列物件

CREATE SEQUENCE seq_t
    INCREMENT BY 1
    START WITH 1
    NOMAXVALUE
    NOCYCLE
    NOCACHE;

參數說明： NOCACHE 確保序列值連續不跳號（犧牲少量效能），NOMAXVALUE 移除上限限制。

步驟 3：建立 BEFORE INSERT 觸發器

CREATE OR REPLACE TRIGGER tri_t
BEFORE INSERT ON t
FOR EACH ROW
WHEN (NEW.id IS NULL)
BEGIN
    SELECT seq_t.NEXTVAL INTO :NEW.id FROM dual;
END;
/

注意事項：

1. 觸發器僅在 id IS NULL 時觸發，保留手動指定主鍵的彈性
2. 使用 :NEW.id 參照新資料列的欄位值
3. 必須從 dual 查詢 NEXTVAL，直接賦值會報錯

三、兩種方案效能與維護性對比

四、進階技巧：12c Identity 進階選項

-- 指定緩存大小提升高併發效能
GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 CACHE 100)

-- 強制自動產生，禁止手動插值
GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)

-- 循環使用（慎用！主鍵衝突風險）
GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1 MAXVALUE 9999 CYCLE)

實務建議

• 新專案若跑在 12c+，**首選 Identity Column**，減少物件依賴與維護成本
• 舊系統升級時，可考慮將關鍵表遷移至 Identity，邊緣表維持舊架構
• 高併發場景建議啟用 CACHE（如 20~100），大幅降低序列鎖競爭
• 跨表共用序列需求時，仍需採用 Sequence + Trigger 或 Application 層管理