如何基于现有行填充MySQL 8.x中的position(INT)列？

环境：MySQL 8.x

现有表结构 #

需求说明 #

• 按parent_fk分组，为每组内的行填充position值，从0开始递增1
• 同一parent_fk下的position值唯一
• 排序规则无严格要求，只需按脚本执行时的行顺序分配即可

目标状态示例 #

解决方案SQL #

1. 新增position列（若尚未添加）

ALTER TABLE your_table_name 
ADD COLUMN position INT DEFAULT NULL;

替换your_table_name为实际表名

2. 按分组填充position值

利用MySQL 8.x原生支持的窗口函数ROW_NUMBER()，实现分组内从0开始的序号填充：

UPDATE your_table_name t
JOIN (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY parent_fk ORDER BY id) - 1 AS new_position
    FROM your_table_name
) AS sub ON t.id = sub.id
SET t.position = sub.new_position;

• PARTITION BY parent_fk：按父键分组处理
• ORDER BY id：按主键id保证行顺序稳定（若无需固定顺序，可替换为ORDER BY (SELECT 1)，但主键排序更可靠）
• ROW_NUMBER() - 1：将默认从1开始的序号转为从0起始

验证填充结果

SELECT id, name, parent_fk, position 
FROM your_table_name 
ORDER BY parent_fk, position;

内容的提问来源于stack exchange，提问作者E-Riz