SQLite删除行后如何调整AUTOINCREMENT主键ID填补空缺？

结论 #

SQLite 没有内置自动填补自增主键空缺的机制，也不存在和 AUTOINCREMENT 效果相反的原生功能，该需求可以手动实现，但非常不推荐。

核心原因解释 #

AUTOINCREMENT 的设计目的就是保证生成的主键 ID 全局唯一、单调永久递增，即使数据被删除，旧 ID 也不会被复用，避免外键关联、数据溯源、跨系统同步等场景出现数据绑定错误：比如你删除了 ID=2 的行后如果复用该 ID，其他表中关联旧 ID=2 的业务数据会错误绑定到新的 ID=2 数据上，引发严重的一致性问题。

不带 AUTOINCREMENT 的 INTEGER PRIMARY KEY 主键仅会在最大 ID 被删除时，复用该最大 ID，也不会自动填补中间的空缺。比如你删除 ID=2 后最大 ID 仍为 5，下一次插入的 ID 还是 6；只有删除 ID=5，下一次插入才会复用 5。

手动实现方案（仅建议无任何外键关联、数据量极小的场景使用） #

你可以在删除行的逻辑中，手动更新后续行的 ID 并重置自增计数器，注意必须开启事务保证操作原子性，避免中途失败导致数据混乱：

1. 删除指定 ID 的行
2. 把所有大于被删 ID 的行的 ID 统一减 1
3. 重置 sqlite_sequence 表中对应表的自增计数器

你可以修改现有的 delete 方法，示例逻辑如下：

public boolean delete(int id) {
    SQLiteDatabase db = this.getWritableDatabase();
    // 开启事务
    db.beginTransaction();
    try {
        // 1. 删除指定行
        int deleteCount = db.delete(PROFILE_TABLE, PROFILE_ID + " = ?", new String[]{String.valueOf(id)});
        if (deleteCount <= 0) {
            return false;
        }
        // 2. 更新大于被删ID的所有行的ID
        db.execSQL("UPDATE " + PROFILE_TABLE + " SET " + PROFILE_ID + " = " + PROFILE_ID + " - 1 WHERE " + PROFILE_ID + " > ?", new Object[]{id});
        // 3. 重置自增序列
        db.execSQL("UPDATE sqlite_sequence SET seq = (SELECT MAX(" + PROFILE_ID + ") FROM " + PROFILE_TABLE + ") WHERE name = ?", new Object[]{PROFILE_TABLE});
        db.setTransactionSuccessful();
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    } finally {
        db.endTransaction();
        db.close();
    }
}

更推荐的替代方案 #

主键 ID 本身不应该承载业务序号的含义，如果你只是需要在展示、导出等场景获得连续的序号，完全可以在查询时动态生成，不需要修改实际存储的主键，性能更高且无一致性风险：
查询时使用 ROW_NUMBER() 窗口函数生成连续序号即可：

SELECT ROW_NUMBER() OVER (ORDER BY ID) AS serial_num, * FROM PROFILE_TABLE;

返回结果的 serial_num 字段就是你需要的连续序号，完全满足删除后自动补位的需求。

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