SQLite中id字段使用AUTOINCREMENT与不使用的差异是什么？

你创建了两张person表，主键定义分别如下：

-- 带AUTOINCREMENT的主键定义
CREATE TABLE person (
  id INTEGER PRIMARY KEY AUTOINCREMENT, 
  name TEXT
);

-- 不带AUTOINCREMENT的主键定义
CREATE TABLE person (
  id INTEGER PRIMARY KEY,
  name TEXT
);

执行插入语句后：

INSERT INTO person (name) 
VALUES ('John'), ('David'), ('Lisa');

两张表的查询结果完全一致，id均按1、2、3递增，但两者在底层逻辑上有以下关键差异：

• ID复用规则不同
  不带AUTOINCREMENT时，若删除表中当前最大ID的记录，后续插入新数据时SQLite会复用这个已被释放的ID；而带AUTOINCREMENT的表永远不会复用已删除的ID，新生成的ID只会比当前表中存在的最大ID更大（即便之前的大ID记录已被删除）。

• 依赖的系统表不同
  带AUTOINCREMENT的表会依赖SQLite内置的sqlite_sequence系统表，用来存储当前表的最大ID值；不带AUTOINCREMENT的表则直接从自身数据中获取最大ID来推导下一个值，不会读写sqlite_sequence表。

• ID上限处理不同
  不带AUTOINCREMENT的表，当ID达到SQLite INTEGER类型的最大值9223372036854775807后，后续插入会尝试复用未被使用的ID；而带AUTOINCREMENT的表达到这个上限后，新插入操作会直接失败，返回SQLITE_FULL错误。

• 手动指定ID的限制不同
  不带AUTOINCREMENT的表，插入时可以手动指定任意未被占用的ID值（包括比当前最大ID小的数值）；带AUTOINCREMENT的表，手动指定ID时必须大于当前表中的最大ID，否则插入操作会失败。

内容的提问来源于stack exchange，提问作者Super Kai - Kazuya Ito