SQLite AUTOINCREMENT 關鍵字詳解：自動遞增機制與行號差異

SQLite AUTOINCREMENT 關鍵字概述

在 SQLite 中，AUTOINCREMENT 是一個用於表欄位自動遞增的關鍵字。當建立表格時，可在特定整數欄位上指定該關鍵字，即可實現該欄位值的自動增加。需注意只有整數型欄位才能使用 AUTOINCREMENT。

基本語法

CREATE TABLE table_name (
    column1 INTEGER AUTOINCREMENT,
    column2 datatype,
    column3 datatype,
    ...
    columnN datatype
);

實戰範例

以下範例建立一個 COMPANY 表，並插入 7 筆資料：

sqlite> CREATE TABLE COMPANY(
    ID INTEGER PRIMARY KEY AUTOINCREMENT,
    NAME TEXT NOT NULL,
    AGE INT NOT NULL,
    ADDRESS CHAR(50),
    SALARY REAL
);

INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Allen', 25, 'Texas', 15000.00);
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Teddy', 23, 'Norway', 20000.00);
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Mark', 25, 'Rich-Mond', 65000.00);
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('David', 27, 'Texas', 85000.00);
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('Kim', 22, 'South-Hall', 45000.00);
INSERT INTO COMPANY (NAME, AGE, ADDRESS, SALARY) VALUES ('James', 24, 'Houston', 10000.00);

執行後，ID 欄位將自動產生 1 到 7 的唯一值：

ID        NAME      AGE       ADDRESS     SALARY
---------- ---------- ---------- ---------- ----------
1         Paul       32        California  20000.0
2         Allen      25        Texas       15000.0
3         Teddy      23        Norway      20000.0
4         Mark       25        Rich-Mond   65000.0
5         David      27        Texas       85000.0
6         Kim        22        South-Hall  45000.0
7         James      24        Houston     10000.0

核心差異：AUTOINCREMENT vs ROWID

SQLite 內部隱藏的 rowid 也會自動遞增，但兩者行為有關鍵不同：

1. 刪除資料後的 ID 重用行為

• AUTOINCREMENT：絕不重用已刪除的 ID 值，確保 ID 唯一且單調遞增。
• rowid：新資料的 rowid 為現有最大 rowid + 1，若最大值被刪除，新資料可能取用先前被刪除的 ID。

2. 達到最大值時的處理

• AUTOINCREMENT：當自增值達到上限（9223372036854775807），插入會失敗並返回 SQLITE_FULL 錯誤碼。
• rowid：會在未使用的 ID 範圍內隨機尋找可用值（可能是先前被刪除的 ID）。

最佳實踐建議

• 若業務要求 ID 永不重複且嚴格遞增（如訂單號、流水號），請使用 INTEGER PRIMARY KEY AUTOINCREMENT。
• 若僅需簡單主鍵且不介意 ID 重用，INTEGER PRIMARY KEY（隱含 rowid）效能略優，儲存空間也較小。
• 避免在高併發寫入場景下頻繁刪除大量資料後再插入，以免觸及 AUTOINCREMENT 上限。