删除记录后重新计算关联表行index_value的最优实现方案

现有一张表存储用户ID（user_id）、对应用户的索引值（index_value）以及其他字段（some_value），原表结构及数据如下：

当删除部分记录（例如some_value为03的行）后，需要重新计算每个user_id下的index_value，使其从0开始连续递增，预期结果如下：

请问实现该需求的最优方式是什么？

核心思路是利用窗口函数按user_id分组，对每组内的记录按原有顺序（比如原index_value或some_value的排序逻辑）重新计算连续索引，再更新表中的index_value字段。

1. 确认分组排序逻辑 #

首先要明确每组内记录的排序依据，比如保留删除前的先后顺序，通常可以用原index_value或者some_value作为排序字段，确保索引更新后记录顺序和删除前一致。

2. 不同数据库的具体实现 #

MySQL 8.0+/PostgreSQL/SQL Server

这类支持窗口函数的数据库，用ROW_NUMBER()生成分组内的连续序号，再减1得到从0开始的索引：

步骤1：验证新索引值（先查询确认）

SELECT 
    user_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY index_value) - 1 AS new_index_value,
    some_value
FROM your_table_name;

将ORDER BY后的index_value替换为实际需要的排序字段（比如some_value），确保顺序符合预期。

步骤2：执行更新操作

• MySQL 8.0+：

WITH updated_index AS (
    SELECT 
        user_id,
        some_value,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY index_value) - 1 AS new_index
    FROM your_table_name
)
UPDATE your_table_name t
JOIN updated_index ui 
    ON t.user_id = ui.user_id AND t.some_value = ui.some_value
SET t.index_value = ui.new_index;

• PostgreSQL：

WITH updated_index AS (
    SELECT 
        ctid, -- 用ctid唯一标识行，有主键的话建议替换为主键字段
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY index_value) - 1 AS new_index
    FROM your_table_name
)
UPDATE your_table_name t
SET index_value = ui.new_index
FROM updated_index ui
WHERE t.ctid = ui.ctid;

• SQL Server：

WITH updated_index AS (
    SELECT 
        user_id,
        some_value,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY index_value) - 1 AS new_index
    FROM your_table_name
)
UPDATE t
SET t.index_value = ui.new_index
FROM your_table_name t
INNER JOIN updated_index ui 
    ON t.user_id = ui.user_id AND t.some_value = ui.some_value;

低版本MySQL（<8.0）兼容方案

如果数据库不支持窗口函数，用变量来实现分组计数：

SET @prev_user = NULL;
SET @row_num = 0;

UPDATE your_table_name t
JOIN (
    SELECT 
        user_id,
        some_value,
        @row_num := CASE WHEN @prev_user = user_id THEN @row_num + 1 ELSE 0 END AS new_index,
        @prev_user := user_id
    FROM your_table_name
    ORDER BY user_id, index_value
) ui ON t.user_id = ui.user_id AND t.some_value = ui.some_value
SET t.index_value = ui.new_index;

注意事项 #

• 若表有主键，优先用主键关联更新，避免some_value重复导致错误更新。
• 执行更新前务必先运行查询语句验证新索引的正确性，避免误操作。

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