SQLite - AUTOINCREMENT

SQLite - AUTOINCREMENT 关键字

在 SQLite 中，你可以创建自动为每行新记录生成唯一整数的列。这通常用于主键（primary key）字段。有两种略微不同的实现方式，理解它们之间的区别对于数据库设计很重要。

默认行为：INTEGER PRIMARY KEY

默认情况下，如果你声明一个类型为 INTEGER PRIMARY KEY 的列，它将成为内部 rowid 的别名。这个 rowid 是一个 64 位有符号整数，会自动分配，并保证在表的所有行中是唯一的。

对于大多数应用程序来说，这已经足够了。rowid 会自动递增，如果你删除了具有最大 rowid 的行，该值可能会在将来的插入中被重新使用。这对于绝大多数用例来说是高效且充足的。

默认行为的语法

CREATE TABLE table_name(
   ID INTEGER PRIMARY KEY,
   -- other columns...
);

AUTOINCREMENT 关键字

AUTOINCREMENT 关键字可以添加到 INTEGER PRIMARY KEY 列。它施加了一个额外的约束：为主键生成的值将始终严格大于该表中该列曾使用过的最大值。

这意味着即使你删除了具有最高 ID 的行，该 ID 也永远不会被新行重新使用。SQLite 通过在名为 sqlite_sequence 的单独表中跟踪已使用的最高 ID 来实现这一点。

何时应该使用 AUTOINCREMENT？ 仅当你有一个严格要求，即主键一旦生成就绝不能被重用的情况。例如，如果你在外部系统或公共 URL 中公开这些 ID，并且需要它们永久稳定。对于内部使用，通常首选默认行为，因为它性能略好，并且使用的存储空间更少。

AUTOINCREMENT 的语法

CREATE TABLE table_name(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   -- other columns...
);

示例：演示区别

让我们创建两个表，一个采用默认行为，另一个采用 AUTOINCREMENT，以实际演示它们之间的区别。

-- 带有标准 INTEGER PRIMARY KEY 的表（最常见情况）
CREATE TABLE PROJECTS(
   ID INTEGER PRIMARY KEY,
   NAME TEXT NOT NULL
);

-- 带有 AUTOINCREMENT 以防止 ID 重用的表
CREATE TABLE INVOICES(
   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   AMOUNT REAL NOT NULL
);

现在，让我们插入一些数据，删除最后一行，然后再次插入。

--- 步骤 1：向两个表插入初始数据 ---
INSERT INTO PROJECTS (NAME) VALUES ('Project A'), ('Project B');
INSERT INTO INVOICES (AMOUNT) VALUES (100.50), (250.00);

sqlite> SELECT * FROM PROJECTS;
ID  NAME
--  ---------
1   Project A
2   Project B

sqlite> SELECT * FROM INVOICES;
ID  AMOUNT
--  ------
1   100.5
2   250.0

--- 步骤 2：从两个表中删除 ID 最高的行 ---
DELETE FROM PROJECTS WHERE ID = 2;
DELETE FROM INVOICES WHERE ID = 2;

--- 步骤 3：向每个表插入新行 ---
INSERT INTO PROJECTS (NAME) VALUES ('Project C');
INSERT INTO INVOICES (AMOUNT) VALUES (99.75);

--- 步骤 4：观察结果 ---
sqlite> SELECT * FROM PROJECTS;
ID  NAME
--  ---------
1   Project A
2   Project C  -- ID 2 被重复使用

sqlite> SELECT * FROM INVOICES;
ID  AMOUNT
--  ------
1   100.5
3   99.75      -- ID 2 被跳过，生成了一个新的 ID 3

最佳实践

除非你有明确的、定义良好的不可重用 ID 的需求，否则请优先使用不带 AUTOINCREMENT 关键字的标准 INTEGER PRIMARY KEY。它更高效，并且与大多数数据库系统的典型行为一致。