業務痛點
MySQL AUTO_INCREMENT、SQL Server IDENTITY(1,1)、PostgreSQL SERIAL 皆為原生自增列。Oracle 11g 及之前**無原生自增**，遷移項目需統一 DAO 層寫法，觸發器+序列是經典解法。
經典方案：序列 + BEFORE INSERT 觸發器
-- 1. 創建序列
CREATE SEQUENCE seq_t_user_id START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
-- 2. 創建表（無自增屬性）
CREATE TABLE t_user (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
create_time DATE DEFAULT SYSDATE
);
-- 3. 觸發器：插入前自動填充 ID
CREATE OR REPLACE TRIGGER trg_t_user_bir
BEFORE INSERT ON t_user
FOR EACH ROW
WHEN (NEW.id IS NULL) -- 關鍵：僅在應用未提供 ID 時自動賦值，兼容手動指定 ID 場景
BEGIN
SELECT seq_t_user_id.NEXTVAL INTO :NEW.id FROM dual;
END;
/
關鍵設計細節
| 細節 | 做法 | 理由 |
|---|
| WHEN 子句 | WHEN (NEW.id IS NULL) | 允許應用顯式指定 ID（如數據遷移、測試構造），不覆蓋 |
| 序列屬性 | NOCACHE 或 CACHE 20 | RAC 環境 NOCACHE 避免間隙；單機 CACHE 提升性能 |
| 觸發器類型 | BEFORE INSERT FOR EACH ROW | 行級、插入前、可修改 :NEW |
| 命名規範 | trg__bir/biu/bdr| 可讀性、易維護 |
12c/19c/21c 現代原生方案：Identity 列
-- 12c+ 標準語法
CREATE TABLE t_user (
id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
name VARCHAR2(50)
);
-- 12c+ 完整選項
CREATE TABLE t_user (
id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 CACHE 20 NOORDER NOCYCLE) PRIMARY KEY,
name VARCHAR2(50)
);
模式對比：
| 模式 | 語法 | 行為 | 適用場景 |
|---|
| ALWAYS | GENERATED ALWAYS AS IDENTITY | **禁止**顯式插入 ID，報錯 | 純系統生成，嚴格管控 |
| BY DEFAULT | GENERATED BY DEFAULT AS IDENTITY | 允許顯式插入，NULL 時自動生成 | 兼容遷移、測試構造 |
| BY DEFAULT ON NULL | GENERATED BY DEFAULT ON NULL AS IDENTITY | **僅 NULL 時自動生成**，非 NULL 時用指定值 | **推薦：最兼容、最靈活** |
遷移兼容性策略
- **新建表**：直接用
GENERATED BY DEFAULT ON NULL AS IDENTITY，DAF 層無感知。
- **舊表改造**：
ALTER TABLE t_user MODIFY id GENERATED BY DEFAULT ON NULL AS IDENTITY（12c+），自動創建關聯序列，原觸發器可保留或刪除（Identity 優先級更高）。
- **跨版本部署**：代碼中檢測版本
SELECT version FROM v$instance，12c+ 用 Identity，11g 用觸發器+序列，DAO 層統一不寫 ID 或寫 NULL。
性能與間隙說明
- 序列/Identity **不保證連續無間隙**（緩存失效、回滾、實例重啟均導致間隙），**業務勿依賴連續性**。
- 高併發插入建議
CACHE 100-1000 減少序列鎖爭用（SQ_ENQ 等待）。
- RAC 環境序列採用
ORDER/NOORDER 權衡全局有序 vs 性能。
- 上一篇文章： Oracle DBA 考點精析：ALTER SYSTEM DEFERRED 僅對新會話生效的機制與實證
- 下一篇文章： Oracle 日期時間插入完全指南：TO_DATE 格式模板、省略成分默認值、顯示格式化與 NLS 陷阱
Copyright © 程式碼之家 All Rights Reserved |
