已知行索引时如何更新或删除SQLite数据表中的指定行

关系型数据库的表没有内置稳定的行序号概念，无排序条件下查询返回的行顺序不固定，这也是大多数教程都推荐用列值条件更新的核心原因。自增ID的设计目的是唯一标识行，本身就不保证连续性，删除操作后出现间隙是正常逻辑，用它充当行序号本身就是不合理的用法。

以下是不同场景下的可行方案：

临时单次按当前顺序更新指定行 #

如果不需要长期固定行序号，只是偶尔需要按当前排序的第N行执行更新，使用ROW_NUMBER()窗口函数生成临时序号匹配即可：

MySQL 写法示例 #

UPDATE 你的表名 t1
INNER JOIN (
    SELECT 主键列, ROW_NUMBER() OVER (ORDER BY 确定排序的列 ASC) AS row_num
    FROM 你的表名
) t2 ON t1.主键列 = t2.主键列
SET t1.要更新的列 = '新的取值'
WHERE t2.row_num = 2; -- 此处填写要更新的行序号，2代表第二行

PostgreSQL 写法示例 #

WITH numbered_rows AS (
    SELECT 主键列, ROW_NUMBER() OVER (ORDER BY 确定排序的列 ASC) AS row_num
    FROM 你的表名
)
UPDATE 你的表名 t1
SET 要更新的列 = '新的取值'
FROM numbered_rows t2
WHERE t1.主键列 = t2.主键列 AND t2.row_num = 2;

注意：ORDER BY后必须填写能确定唯一排序规则的列（比如创建时间、主键），否则每次生成的行序号可能发生变化，匹配到错误的行

需要长期维护连续行序号 #

如果业务逻辑必须依赖连续的行序号，可以单独新增一个sort_sequence整数列，不要复用自增主键：

• 删除行后，执行批量更新补全间隙：

-- 假设删除的是原序号为3的行，将所有大于3的序号统一减1
UPDATE 你的表名 SET sort_sequence = sort_sequence - 1 WHERE sort_sequence > 3;

• 如果删除操作频繁、数据量较大，不建议实时重排序号，可以每次操作时都用上面的临时生成行号方案匹配，避免批量更新的性能开销。

不推荐的方案 #

部分数据库提供物理行标识符（比如PostgreSQL的ctid、MySQL的_rowid）可以直接定位行，但这些标识会在表重构、数据整理、存储优化时自动变更，稳定性极差，仅适合临时单表操作，禁止在生产业务逻辑中使用。

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