您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL、SQL Server、Oracle 三大資料庫主鍵自增身分識別完整設定指南

編輯：MySQL 資料庫相關知識

跨資料庫主鍵自增策略統一認知

主鍵自增是關聯式資料庫最基礎也最關鍵的設計決策。三大主流資料庫實作機制迥異：MySQL `AUTO_INCREMENT`、SQL Server `IDENTITY`、Oracle `SEQUENCE + TRIGGER`。本文系統對比語法、參數、行為差異與進階操作 (如指定值插入、重置種子)。

1. MySQL：AUTO_INCREMENT

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    name VARCHAR(15)
);
INSERT INTO customers (name) VALUES ('name1'), ('name2');
SELECT id FROM customers;  -- 1, 2

隱含種子=1、增量=1，不可自訂 (需 `ALTER TABLE ... AUTO_INCREMENT=100` 變更起始值)。
字串字面值雙引號/單引號皆可。
InnoDB：自增值持久化到 Redo Log，重啟後不回滾 (8.0+)。

2. SQL Server：IDENTITY (種子, 增量)

CREATE TABLE customers (
    id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    name VARCHAR(15)
);
INSERT INTO customers (name) VALUES ('name1'), ('name2');
-- 指定值插入 (需開啟 IDENTITY_INSERT)
SET IDENTITY_INSERT customers ON;
INSERT INTO customers (id, name) VALUES (100, 'specified');
SET IDENTITY_INSERT customers OFF;

IDENTITY(種子, 增量) 靈活控制起始與步長 (如 `IDENTITY(1000, 10)`)。
字串必須用單引號，雙引號視為識別碼 (依 `QUOTED_IDENTIFIER`)。
SET IDENTITY_INSERT ON 允許顯式插入身分值，同一時間僅一張表可開啟。
重置種子：`DBCC CHECKIDENT ('customers', RESEED, 0);` (下一筆為 1)。

3. Oracle：SEQUENCE + TRIGGER (12c 前) / IDENTITY COLUMN (12c+)

傳統模式 (11g/12c 相容)

-- 1. 建立序列
CREATE SEQUENCE customer_id_seq INCREMENT BY 2 START WITH 1;

-- 2. 建表 (無自增屬性)
CREATE TABLE customers (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(15)
);

-- 3. 建立觸發器自動填充
CREATE OR REPLACE TRIGGER trg_customers
BEFORE INSERT ON customers FOR EACH ROW
BEGIN
    SELECT customer_id_seq.NEXTVAL INTO :NEW.id FROM dual;
END;

-- 4. 插入測試
INSERT INTO customers (name) VALUES ('test');
SELECT id FROM customers;  -- 1, 3, 5... (步長 2)

CURRVAL 取當前值，NEXTVAL 取下一值並遞增。
序列獨立於表，可跨表共用、緩存 (`CACHE 20`)、循環 (`CYCLE`)、不緩存 (`NOCACHE` 保證不跳號但效能較差)。
觸發器有微小效能開銷，高併發建議用序列直接在 INSERT 語句指定。

現代模式 (Oracle 12c+ Identity Column)

CREATE TABLE customers (
    id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
    name VARCHAR2(15)
);
-- 或 GENERATED BY DEFAULT ON NULL (允許顯式指定 NULL 觸發自增)
INSERT INTO customers (name) VALUES ('auto');

語法接近 ANSI 標準，無需觸發器，效能最佳。
`GENERATED ALWAYS` 禁止顯式插入；`GENERATED BY DEFAULT ON NULL` 相容舊遷移。

跨庫對比總表

特性	MySQL AUTO_INCREMENT	SQL Server IDENTITY	Oracle SEQUENCE	Oracle 12c+ IDENTITY
語法簡潔度	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐ (需序列+觸發器)	⭐⭐⭐⭐⭐
種子/增量可配置	❌ (僅 ALTER 起始值)	✅ IDENTITY(seed, incr)	✅ 序列屬性全控	✅ 子句指定
指定值插入	直接 INSERT id	SET IDENTITY_INSERT ON	直接 INSERT seq.NEXTVAL	GENERATED BY DEFAULT ON NULL
重置/同步種子	ALTER TABLE ... AUTO_INCREMENT=n	DBCC CHECKIDENT RESEED	ALTER SEQUENCE ... RESTART	同序列
效能/緩存	InnoDB 內存計數器	內存緩存 (可追蹤旗標)	CACHE/NOCACHE 靈活	內建緩存
跨表共用	❌	❌	✅ 序列獨立物件	❌
跳號行為	重啟可能重用 (舊版)	回滾/失敗會跳號	可控 (NOCACHE 不跳)	同序列

常見進階操作

取得剛插入的自增值：
- MySQL: `LAST_INSERT_ID()` (連接級)、`SELECT LAST_INSERT_ID();`
- SQL Server: `SCOPE_IDENTITY()` (作用域級，最安全)、`@@IDENTITY` (連接級，觸發器會干擾)、`IDENT_CURRENT('table')` (表級，併發不準)
- Oracle: `sequence.CURRVAL` (需同 Session 先呼叫 NEXTVAL)、`RETURNING id INTO :var` (DML returning)
刪除自增屬性 (SQL Server)： 無直接 `ALTER COLUMN ... DROP IDENTITY`。折中方案：新增欄位 → 複製資料 → 刪舊欄 → 重命名 → 重建 PK。
大量匯入效能： 關閉索引/約束 → 批次插入 → 重建索引；或使用 `BULK INSERT` / `LOAD DATA` / `SQL*Loader` 等大量載入工具。