MySQL Update更新序列序号重复值 两种分组序号实现方法

执行前先备份目标表数据，以下方案在MySQL 5.7、8.0版本均验证可用。示例中存储序号的字段为desire，分组字段为column_col，实际使用时替换成自己的表名、字段名即可；如果分组字段名是MySQL关键字column，记得字段名两侧加反引号转义。

需求1：同组分配统一序号，不同组按出现顺序递增不重复 #

效果为相同column_col值的记录序号完全一致，不同分组按出现顺序分配1、2、3…的不重复序号。

MySQL 5.7/8.0 通用兼容写法（用户变量实现） #

-- 初始化会话变量
SET @group_num := 0;
SET @last_group_val := NULL;

-- 执行更新
UPDATE 你的表名
SET 
  desire = IF(
    @last_group_val = column_col,
    @group_num,
    @group_num := @group_num + 1
  ),
  -- 注意：这行必须放在序号计算的后面，顺序不能调换
  column_col = (@last_group_val := column_col)
-- 必须加排序，保证同组记录连续排列，顺序和业务预期一致
ORDER BY column_col, 你的排序字段（如主键id、创建时间）;

MySQL 8.0+ 推荐写法（窗口函数实现，无变量风险） #

WITH seq_calc AS (
  SELECT
    表主键字段,
    DENSE_RANK() OVER(ORDER BY column_col, 你的排序字段) AS calc_val
  FROM 你的表名
)
UPDATE 你的表名 t
JOIN seq_calc s ON t.表主键字段 = s.表主键字段
SET t.desire = s.calc_val;

需求2：组内从1开始连续自增，跨组重置计数 #

效果为相同column_col值的分组内，记录按顺序分配1、2、3…的连续序号，切换分组时序号重置为1重新计数。

MySQL 5.7/8.0 通用兼容写法（用户变量实现） #

-- 初始化会话变量
SET @inner_num := 0;
SET @last_group_val := NULL;

-- 执行更新
UPDATE 你的表名
SET
  desire = IF(
    @last_group_val = column_col,
    @inner_num := @inner_num + 1,
    @inner_num := 1
  ),
  -- 注意：这行必须放在序号计算的后面，顺序不能调换
  column_col = (@last_group_val := column_col)
-- 先按分组字段排序保证同组连续，再按组内规则排序保证序号顺序正确
ORDER BY column_col, 组内排序字段（如主键id、排序权重、创建时间）;

MySQL 8.0+ 推荐写法（窗口函数实现，无变量风险） #

WITH seq_calc AS (
  SELECT
    表主键字段,
    ROW_NUMBER() OVER(PARTITION BY column_col ORDER BY 组内排序字段) AS calc_val
  FROM 你的表名
)
UPDATE 你的表名 t
JOIN seq_calc s ON t.表主键字段 = s.表主键字段
SET t.desire = s.calc_val;

常见问题排查 #

• 序号重复、乱序：绝大多数情况是缺失ORDER BY子句，MySQL无排序时遍历记录的顺序不可控，必须按业务规则加排序保证顺序正确
• 全表序号均为1：检查SET子句的顺序，必须先计算序号值，再给记录上一分组值的变量赋值，顺序写反就会触发该问题
• 并发执行时序号错乱：用户变量是会话级隔离的，多会话并发更新同一张表的序号时会出现变量值串扰，建议执行前加表锁，或优先使用8.0+窗口函数方案
• 序号不连续：如果更新时加了WHERE条件过滤无效记录，不会影响最终序号的连续性，窗口函数和用户变量方案都会只针对符合条件的记录计算连续序号

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