SQLite删除表第n行并更新rowID/自增列的实现方法

首先得明确：SQLite的rowid（包括绑定它的自增主键列）是用来唯一标识行的，不是做显示用的连续序号的，所以删除行后不会自动重排，这是设计特性，避免数据关联混乱。要实现删除后显示序号连续，有两种可行方案：

方案1：查询时动态生成连续序号（推荐） #

不用修改数据库里的任何数据，每次查询待办列表时，用窗口函数生成临时的连续序号，完全满足GUI显示需求：

SELECT 
  ROW_NUMBER() OVER (ORDER BY 创建时间 DESC) AS display_num, -- 按你的排序规则来，比如创建时间、优先级
  * 
FROM tasks;

不管你删多少行，只要重新执行这个查询，display_num都会是从1开始的连续数字，完全符合你要的效果。而且不用动底层数据，不会破坏行的唯一性，逻辑最简单。

方案2：手动维护自定义序号列（仅适合单用户场景） #

如果你一定要把连续序号存在数据库里，那得自己加一个普通整数列（比如task_order，不要设为自增），然后删除后手动更新后续行的序号：

1. 删除第n行：

DELETE FROM tasks WHERE task_order = n;

2. 立即更新所有序号大于n的行，让它们的序号减1：

UPDATE tasks SET task_order = task_order - 1 WHERE task_order > n;

注意：这两步要放在同一个事务里执行，避免中途出错导致序号混乱：

BEGIN TRANSACTION;
DELETE FROM tasks WHERE task_order = n;
UPDATE tasks SET task_order = task_order - 1 WHERE task_order > n;
COMMIT;

但这种方法有明显缺点：如果有多个操作同时进行（比如多端同步），很容易出现序号冲突；而且每次删除都要写更新逻辑，比方案1麻烦得多。

为什么不能用rowid实现？ #

SQLite的rowid是系统自动维护的唯一标识，删除行后，被删掉的rowid不会被复用，后续新增的行也不会填补空缺，自增主键列（INTEGER PRIMARY KEY AUTOINCREMENT）也是绑定rowid的，所以不可能自动重排。强行修改rowid会破坏数据库的内部约束，绝对不建议这么做。

内容的提问来源于stack exchange，提问作者3rdgrade-dropout