Android SQLite数据库升级时为新增position列设置连续行号

嘿，刚好能通过单条SQL语句搞定这个需求，不用游标查询再循环更新，分两种场景给你方案：

方案1：支持窗口函数的数据库（SQLite 3.25.0+、MySQL 8.0+等） #

如果你的数据库版本支持窗口函数，用ROW_NUMBER()是最直观高效的方式，直接按_id排序生成连续序号，再调整为从0开始：

UPDATE your_table
SET position = (
    SELECT row_num - 1
    FROM (
        SELECT _id, ROW_NUMBER() OVER (ORDER BY _id) AS row_num
        FROM your_table
    ) AS sorted_rows
    WHERE sorted_rows._id = your_table._id
);

原理说明： #

• ROW_NUMBER() OVER (ORDER BY _id)会给每条记录按_id从小到大分配一个从1开始的连续序号
• 减1后就得到你要的从0开始的position值
• 子查询通过_id关联原表，精准匹配每条记录的更新值

方案2：不支持窗口函数的旧版数据库（比如SQLite 3.25.0以下） #

如果你的数据库版本比较老，不支持窗口函数，可以用自连接计数的方式实现：

UPDATE your_table t1
SET position = (
    SELECT COUNT(*)
    FROM your_table t2
    WHERE t2._id < t1._id
);

原理说明： #

• 对于每条t1记录，统计所有_id比它小的t2记录数量，这个数量刚好就是从0开始的连续值（最小的_id对应的计数是0，下一个是1，以此类推）
• 这种写法不需要窗口函数，兼容性更强，只是数据量很大时效率会比窗口函数略低一点

使用注意事项 #

1. 执行这条SQL前，一定要先备份数据，避免更新出错导致数据丢失
2. 把your_table替换成你实际的表名
3. 在数据库升级的代码里，直接调用execSQL()执行这条语句即可，不需要额外的游标操作

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