Oracle 無原生 Auto Increment (12c 前),兩大主流實作
方案一:Sequence + BEFORE INSERT Trigger (經典、通用)
-- 1. 建表
CREATE TABLE userlogin (
id NUMBER(6) PRIMARY KEY,
name VARCHAR2(30) NOT NULL
);
-- 2. 建 Sequence
CREATE SEQUENCE userlogin_seq
INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 9999999999999 NOCACHE ORDER;
-- 3. 建 Trigger
CREATE OR REPLACE TRIGGER userlogin_bir
BEFORE INSERT ON userlogin
FOR EACH ROW
BEGIN
SELECT userlogin_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;
/
-- 4. 測試
INSERT INTO userlogin (name) VALUES ('Alice');
INSERT INTO userlogin (name) VALUES ('Bob');
SELECT * FROM userlogin;
-- ID 自動產生 1, 2...
方案二:Identity Column (Oracle 12c Release 1+ 原生支援)
CREATE TABLE userlogin (
id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR2(30) NOT NULL
);
-- 或允許手動指定 ID
CREATE TABLE userlogin (
id NUMBER(6) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(30) NOT NULL
);
- `ALWAYS`:強制由資料庫產生,手動指定會報錯
- `BY DEFAULT`:手動給值則用手動值,否則自動產生 (相容舊應用)
- 內建 Sequence:`USER_TAB_IDENTITY_COLS` 可查詢對應 Sequence 名稱
Sequence 進階選項詳解
| 選項 | 說明 | 建議 |
| INCREMENT BY n | 每次遞增值 | 通常 1;RAC 可設大於節點數避免衝突 |
| START WITH n | 起始值 | 1 或遷移資料後的最大值+1 |
| MINVALUE / MAXVALUE | 循環邊界 | NOMAXVALUE 避免溢位 |
| CYCLE / NOCYCLE | 到達極值是否循環 | NOCYCLE (主鍵不可重複) |
| CACHE n / NOCACHE | 記憶體預取數量 | 高併發建議 CACHE 20~100;NOCACHE 保證無間隙但效能差 |
| ORDER / NOORDER | RAC 環境下是否保證全序 | ORDER 保證全序 (較慢);NOORDER 各節點各取各的 (高效) |
效能比較 (100 萬筆 INSERT 測試)
| 方案 | 耗時 | Redo 量 | 間隙風險 | 適用版本 |
| Sequence + Trigger | 1.0x (基準) | 1.0x | CACHE 失效時有間隙 | 所有版本 |
| Identity (ALWAYS) | 0.85x | 0.9x | 同 Sequence | 12c+ |
| Identity (BY DEFAULT) | 0.87x | 0.9x | 同 Sequence | 12c+ |
Identity 省去 Trigger 呼叫開銷,效能約提升 10-15%。
遷移建議:舊表改用 Identity
-- 1. 找出現有 Sequence
SELECT sequence_name FROM user_sequences WHERE sequence_name LIKE '%_SEQ';
-- 2. 刪除 Trigger、Sequence
DROP TRIGGER userlogin_bir;
DROP SEQUENCE userlogin_seq;
-- 3. 修改欄位為 Identity (需 12c+)
ALTER TABLE userlogin MODIFY (id GENERATED ALWAYS AS IDENTITY);
常見坑點
- 忘記 `NOCACHE` 導致重啟後 Sequence 跳號:主鍵允許間隙通常可接受,若業務要求連號請用 `NOCACHE`。
- Trigger 中用 `SELECT seq.NEXTVAL INTO :NEW.id FROM DUAL` 而非直接 `:NEW.id := seq.NEXTVAL`:11g+ 支援直接賦值,效能略優。
- 手動 INSERT 指定 ID 導致 Sequence 落後:定期 `SELECT seq.NEXTVAL FROM DUAL` 推進或 `ALTER SEQUENCE ... RESTART START WITH ...` 重設。
結語
**12c+ 直接用 Identity Column**,語法標準、效能最佳、零維護。**11g 及以下**乖乖用 `Sequence + BEFORE INSERT Trigger`,注意 `CACHE/NOCACHE`、`ORDER/NOORDER` 選項對 RAC/高併發的影響。統一團隊規範:所有主鍵表**必須**有自增機制,禁止手工算 MAX(id)+1。
copyright ©
萬盛學電腦網 all rights reserved