删除数据库记录后，如何保持autoincrement ID的连续递增？

我创建了一个id设为自增（autoincrement）的SQLite数据库，但删除记录后新增记录时，新记录的id会从最后一条被删除记录的id继续递增，而不是从当前最后一条可用记录的id开始。

初始数据表 #

执行删除操作的代码：

with sqlite3.connect("names.db") as connection:
    cursor = connection.cursor()
    cursor.execute("DELETE FROM names WHERE id > 1;")

之后执行插入操作：

with sqlite3.connect("names.db") as connection:
    cursor = connection.cursor()
    cursor.execute("INSERT INTO names (name, surname) VALUES(?, ?);", ("Bob", "Marley"))

预期结果 #

实际结果 #

SQLite的AUTOINCREMENT（大写）机制本身就是不会复用已删除ID值的。它会维护一个内部的sqlite_sequence表，专门记录每个自增主键表的最大ID值。删除记录只会移除数据表中的行，但不会更新sqlite_sequence里的最大值。插入新记录时，SQLite会基于这个保存的最大值生成下一个ID，而非扫描数据表找当前最大的可用ID。

如果你的表仅使用INTEGER PRIMARY KEY（未加AUTOINCREMENT），SQLite会尝试复用已删除的ID；但一旦显式指定AUTOINCREMENT，就会强制遵循“永不复用ID”的规则——这是设计特性，目的是保证ID的唯一性和递增顺序，避免特定场景下的ID冲突。

根据需求分两种情况处理：

1. 无需严格禁止复用ID（推荐） #

如果只是需要自增ID，且可以复用已删除的ID空位，移除AUTOINCREMENT关键字，仅保留INTEGER PRIMARY KEY定义表结构即可。此时SQLite会自动从当前数据表的最大ID开始递增，也会复用已删除的ID。

修改表结构的语句（若允许调整表结构）：

CREATE TABLE names (
    id INTEGER PRIMARY KEY,
    name TEXT,
    surname TEXT
);

2. 必须保留AUTOINCREMENT且需重置ID序列 #

若因业务需求必须保留AUTOINCREMENT，可手动更新sqlite_sequence表重置最大值：

with sqlite3.connect("names.db") as connection:
    cursor = connection.cursor()
    # 获取当前数据表的最大ID
    cursor.execute("SELECT MAX(id) FROM names;")
    max_id = cursor.fetchone()[0] or 0
    # 更新sqlite_sequence表
    cursor.execute("UPDATE sqlite_sequence SET seq = ? WHERE name = 'names';", (max_id,))
    connection.commit()

执行该操作后，新插入记录的ID会从max_id + 1开始。

注意：手动修改sqlite_sequence需谨慎，需确保无并发写入场景，否则可能引发ID冲突。

内容的提问来源于stack exchange，提问作者Echo