SQLite数据库ID复用问题：删除数据后填补空缺ID

我的数据库创建语句如下：

CREATE TABLE $_tableName(
        id INTEGER NOT NULL PRIMARY KEY,
        category TEXT,
        content TEXT,
        date TEXT
        )

当前删除任意数据后，新插入数据的ID会延续之前的最大值。比如原有ID为1-10的数据，删除ID为3的内容后，新添加数据的ID会变为11。我希望新数据能存入ID为3的空缺位置。

我的Flutter操作代码：

Future<int> add(NoteDbModel saveDbModel) async {
    Database db = await instance.database;
    return await db.insert(_tableName, saveDbModel.toMap());
}

Future<int> remove(int id) async {
    Database db = await instance.database;
    return await db.delete(_tableName, where: 'id = ?', whereArgs: [id]);
}

因为SQLite的INTEGER PRIMARY KEY默认会自动递增，不会主动复用空缺ID，要实现填补空缺的需求，需要手动查询并指定插入的ID，具体步骤如下：

1. 查询最小空缺ID
   使用以下SQL语句可以找出当前最小的未被使用的ID：

   SELECT MIN(t1.id + 1) AS next_id
   FROM $_tableName t1
   LEFT JOIN $_tableName t2 ON t1.id + 1 = t2.id
   WHERE t2.id IS NULL
   

   如果没有空缺（所有ID连续存在），这个查询会返回NULL，此时就用当前最大ID加1。

2. 修改Flutter插入方法
   在插入新数据前先获取空缺ID，赋值给模型的id字段后再执行插入，修改后的代码如下：

   Future<int> add(NoteDbModel saveDbModel) async {
     Database db = await instance.database;
     
     // 查询最小空缺ID
     final gapResult = await db.rawQuery('''
       SELECT MIN(t1.id + 1) AS next_id
       FROM $_tableName t1
       LEFT JOIN $_tableName t2 ON t1.id + 1 = t2.id
       WHERE t2.id IS NULL
     ''');
     
     int nextId;
     if (gapResult.isNotEmpty && gapResult.first['next_id'] != null) {
       nextId = gapResult.first['next_id'] as int;
     } else {
       // 无空缺时取最大ID加1，若表为空则从1开始
       final maxResult = await db.rawQuery('SELECT MAX(id) AS max_id FROM $_tableName');
       int maxId = maxResult.first['max_id'] as int? ?? 0;
       nextId = maxId + 1;
     }
     
     // 将确定的ID赋值给模型
     saveDbModel.id = nextId;
     return await db.insert(_tableName, saveDbModel.toMap());
   }
   

3. 额外注意

   • 确保NoteDbModel类支持手动设置id，且toMap()方法会包含该字段。
   • 若存在并发插入场景，建议用事务包裹查询和插入操作，避免ID冲突：

     return await db.transaction((txn) async {
       // 此处放入查询空缺ID和插入数据的逻辑
     });
     

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