小巧、快速、可靠
任選三項
SQLite 自動遞增

1. 摘要

  1. AUTOINCREMENT 關鍵字會產生額外的 CPU、記憶體、磁碟空間和磁碟 I/O 負擔，如果沒有絕對必要，應避免使用。通常不需要使用。

  2. 在 SQLite 中，類型為 INTEGER PRIMARY KEY 的欄位是 ROWID 的別名（WITHOUT ROWID 表格除外），ROWID 永遠是 64 位元有號整數。

  3. 在 INSERT 時，如果 ROWID 或 INTEGER PRIMARY KEY 欄位沒有明確指定值，系統會自動填入一個未使用的整數，通常是目前使用中最大的 ROWID 加一。無論是否使用 AUTOINCREMENT 關鍵字，都是如此。

  4. 如果 AUTOINCREMENT 關鍵字出現在 INTEGER PRIMARY KEY 之後，會變更自動 ROWID 指派演算法，以防止在資料庫生命週期中重複使用 ROWID。換句話說，AUTOINCREMENT 的目的是防止重複使用已刪除列的 ROWID。

2. 背景

在 SQLite 中，表格列通常有 64 位元有號整數 ROWID，在同一個表格中的所有列中都是唯一的。（WITHOUT ROWID 表格例外）

您可以使用 ROWID、_ROWID_ 或 OID 等特殊欄位名稱存取 SQLite 表格的 ROWID。除非您宣告一般表格欄位使用這些特殊名稱之一，否則使用該名稱會參考宣告的欄位，而不是內部 ROWID。

如果一個表格包含 INTEGER PRIMARY KEY 類型的欄位，那麼該欄位就會成為 ROWID 的別名。然後你可以使用四個不同的名稱中的任何一個來存取 ROWID，上述說明的原始三個名稱或給予 INTEGER PRIMARY KEY 欄位的名稱。所有這些名稱都是彼此的別名，且在任何情況下都能同樣順利運作。

當新列插入 SQLite 表格時，ROWID 可以指定為 INSERT 陳述式的一部分，或者可以由資料庫引擎自動指派。若要手動指定 ROWID，只要將其包含在要插入的值清單中即可。例如 

CREATE TABLE test1(a INT, b TEXT);
INSERT INTO test1(rowid, a, b) VALUES(123, 5, 'hello');

如果在插入時未指定 ROWID，或者如果指定的 ROWID 值為 NULL，則會自動建立適當的 ROWID。通常的演算法是給予新建立的列一個 ROWID，其值比插入前表格中最大的 ROWID 大 1。如果表格最初為空，則會使用 ROWID 1。如果最大的 ROWID 等於最大的可能整數 (9223372036854775807)，則資料庫引擎會開始隨機挑選正的候選 ROWID，直到找到一個先前未使用的 ROWID。如果在合理嘗試次數後仍找不到未使用的 ROWID，則插入操作會失敗，並出現 SQLITE_FULL 錯誤。如果沒有明確插入任何負的 ROWID 值，則自動產生的 ROWID 值將永遠大於零。

上面說明的正常 ROWID 選擇演算法將產生單調遞增的唯一 ROWID，只要你從未使用過最大的 ROWID 值，且從未刪除 ROWID 最大的表格中的項目。如果你曾經刪除列或曾經建立一個具有最大可能 ROWID 的列，則先前已刪除列的 ROWID 可能會在建立新列時重複使用，而新建立的 ROWID 可能不會嚴格按照遞增順序排列。

3. AUTOINCREMENT 關鍵字

如果某欄位有 INTEGER PRIMARY KEY AUTOINCREMENT 類型，則會使用稍微不同的 ROWID 選取演算法。為新列選取的 ROWID 會比該表格中之前存在過最大的 ROWID 大一。如果該表格從未包含過任何資料，則會使用 ROWID 1。如果先前已插入最大的 ROWID，則不允許新的 INSERT，而且任何嘗試插入新列的動作都會失敗，並出現 SQLITE_FULL 錯誤。只會考慮來自已提交之前交易的 ROWID 值。已回滾的 ROWID 值會被忽略，而且可以重複使用。

SQLite 使用名為「sqlite_sequence」的內部表格來追蹤最大的 ROWID。如果尚未存在，則每當建立包含 AUTOINCREMENT 欄位的正常表格時，就會自動建立 sqlite_sequence 表格。在第一次寫入 AUTOINCREMENT 表格時，會建立 sqlite_sequence 表格中對應於具有 AUTOINCREMENT 欄位的表格的一列，並在任何後續寫入增加最大 rowid 時更新該列。可以使用一般的 UPDATE、INSERT 和 DELETE 陳述式來修改 sqlite_sequence 表格的內容。但是，修改此表格可能會擾亂 AUTOINCREMENT 金鑰產生演算法。在進行此類變更之前，請務必了解您在做什麼。sqlite_sequence 表格不會追蹤與 UPDATE 陳述式相關的 ROWID 變更，只會追蹤 INSERT 陳述式。

AUTOINCREMENT 關鍵字所實作的行為與預設行為略有不同。使用 AUTOINCREMENT 時，自動選取 ROWID 的列保證會擁有在同一個資料庫的同一個資料表中從未使用過的 ROWID。而且自動產生的 ROWID 保證會單調遞增。在某些應用程式中，這些是很重要的特性。但如果您的應用程式不需要這些特性，您可能應該維持預設行為，因為使用 AUTOINCREMENT 需要在插入每列時執行額外的作業，因此會導致 INSERT 執行得稍微慢一些。

請注意，「單調遞增」並不表示 ROWID 總是會增加一。一是最常見的增量。不過，如果插入失敗（例如）因為唯一性約束，失敗的插入嘗試的 ROWID 可能不會在後續的插入中重複使用，導致 ROWID 順序中出現間隙。AUTOINCREMENT 保證自動選取的 ROWID 會遞增，但不會保證它們會連續。

由於 AUTOINCREMENT 關鍵字會變更 ROWID 選取演算法的行為，因此不允許在 WITHOUT ROWID 資料表或任何非 INTEGER PRIMARY KEY 的資料表欄位上使用 AUTOINCREMENT。嘗試在 WITHOUT ROWID 資料表或非 INTEGER PRIMARY KEY 欄位上使用 AUTOINCREMENT 會導致錯誤。