MySQL 與 Oracle 自動遞增欄位建立對比：AUTO_INCREMENT vs Sequence+Trigger

MySQL：AUTO_INCREMENT 一鍵搞定

CREATE TABLE article (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255)
);

INSERT INTO article (title) VALUES ('a');  -- id=1
INSERT INTO article (title) VALUES ('b');  -- id=2
INSERT INTO article (title) VALUES ('c');  -- id=3
-- 也可明確指定 NULL
INSERT INTO article VALUES (NULL, 'd');    -- id=4

特性：

• 自動從 1 遞增，可 `ALTER TABLE article AUTO_INCREMENT=100` 重設起始值
• `LAST_INSERT_ID()` 取得剛插入的 ID
• InnoDB 持久化自增計數器 (8.0+)，重啟不回滾

Oracle：Sequence + Trigger 手動實作

1. 建立 Sequence

CREATE SEQUENCE seq_test_ids
    MINVALUE 1
    MAXVALUE 99999999
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    ORDER;

2. 建立 Trigger (BEFORE INSERT)

CREATE OR REPLACE TRIGGER tri_test_id
BEFORE INSERT ON test_table
FOR EACH ROW
DECLARE
    nextid NUMBER;
BEGIN
    IF :NEW.id IS NULL OR :NEW.id = 0 THEN
        SELECT seq_test_ids.NEXTVAL INTO nextid FROM dual;
        :NEW.id := nextid;
    END IF;
END tri_test_id;
/
-- 注意：Oracle 需斜線 / 結束 PL/SQL 區塊

3. 測試

INSERT INTO test_table (col1) VALUES ('x');  -- id 自動填 1
INSERT INTO test_table (col1) VALUES ('y');  -- id 自動填 2

對比總結

Oracle 12c+ 新特性：Identity Column

CREATE TABLE article (
    id NUMBER GENERATED ALWAYS AS IDENTITY,
    title VARCHAR2(255)
);
-- 相當於內建 Sequence + Trigger，語法簡化

遷移建議

• MySQL → Oracle：改寫為 Sequence + Trigger 或 12c Identity
• Oracle → MySQL：直接用 `AUTO_INCREMENT`，注意序列值衝突需 `ALTER TABLE` 重設
• 異構同步工具 (GoldenGate, CDC) 需正確映射自增邏輯