小巧、快速、可靠。
三选二。
SQLite 自动递增

1. 概要

  1. AUTOINCREMENT 关键字会增加额外的 CPU、内存、磁盘空间和磁盘 I/O 负担，如果没有严格需要，应避免使用。通常情况下，不需要使用它。

  2. 在 SQLite 中，类型为 INTEGER PRIMARY KEY 的列是 ROWID 的别名（WITHOUT ROWID 表除外），ROWID 始终是 64 位有符号整数。

  3. 在 INSERT 操作中，如果 ROWID 或 INTEGER PRIMARY KEY 列没有显式给出值，则它将自动填充一个未使用的整数，通常是当前使用的最大 ROWID 加 1。无论是否使用 AUTOINCREMENT 关键字，情况都如此。

  4. 如果 AUTOINCREMENT 关键字出现在 INTEGER PRIMARY KEY 之后，则会更改自动 ROWID 分配算法，以防止在数据库的生命周期内重用 ROWID。换句话说，AUTOINCREMENT 的目的是防止重用先前删除行的 ROWID。

2. 背景

在 SQLite 中，表行通常具有 64 位有符号整数 ROWID，该 ROWID 在同一表的所有行中是唯一的。（WITHOUT ROWID 表是例外情况。）

您可以使用以下特殊列名之一访问 SQLite 表的 ROWID：ROWID、_ROWID_ 或 OID。除非您声明普通表列使用其中一个特殊名称，否则使用该名称将引用已声明的列，而不是内部 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 大 1 的 ROWID。如果表最初为空，则使用 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 大 1。如果该表以前从未包含任何数据，则使用 ROWID 1。如果以前已经插入了最大可能的 ROWID，则不允许进行新的 INSERT 操作，任何尝试插入新行的操作都将失败，并出现 SQLITE_FULL 错误。仅考虑先前已提交事务中的 ROWID 值。已回滚的 ROWID 值将被忽略，可以重复使用。

SQLite 使用名为 "sqlite_sequence" 的 内部表 来跟踪最大 ROWID。sqlite_sequence 表是在创建包含 AUTOINCREMENT 列的普通表时自动创建的，如果该表不存在。第一次写入具有 AUTOINCREMENT 列的表时，会创建一个与该表相对应的 sqlite_sequence 表中的行，并且在任何后续写入（这些写入会增加最大 rowid）时会更新该行。可以使用普通的 UPDATE、INSERT 和 DELETE 语句修改 sqlite_sequence 表的内容。但是，对该表进行修改可能会扰乱 AUTOINCREMENT 键生成算法。在进行此类更改之前，请确保您知道自己在做什么。sqlite_sequence 表不会跟踪与 UPDATE 语句相关的 ROWID 更改，只会跟踪 INSERT 语句相关的更改。

AUTOINCREMENT 关键字实现的行为与默认行为略有不同。使用 AUTOINCREMENT，具有自动选择的 ROWID 的行保证具有以前从未由同一数据库中的同一表使用过的 ROWID。并且保证自动生成的 ROWID 单调递增。这些是在某些应用程序中重要的属性。但是，如果您的应用程序不需要这些属性，您应该坚持使用默认行为，因为使用 AUTOINCREMENT 要求在插入每行时执行额外的操作，从而导致 INSERT 操作运行速度略慢。

请注意，"单调递增"并不意味着 ROWID 始终恰好增加 1。1 是通常的增量。但是，如果插入操作由于（例如）唯一性约束而失败，则失败的插入尝试的 ROWID 可能会在后续插入操作中不被重用，导致 ROWID 序列中出现间隙。AUTOINCREMENT 保证自动选择的 ROWID 会递增，但不能保证它们是连续的。

由于 AUTOINCREMENT 关键字会更改 ROWID 选择算法的行为，因此不允许在 WITHOUT ROWID 表上或除 INTEGER PRIMARY KEY 之外的任何表列上使用 AUTOINCREMENT。任何尝试在 WITHOUT ROWID 表上或在除 INTEGER PRIMARY KEY 列之外的列上使用 AUTOINCREMENT 的操作都会导致错误。