为重复记录更新递增序号列失败：所有记录值均为1的问题排查

适用MySQL 8.0+的正确UPDATE语句 #

WITH employee_with_row AS (
    SELECT 
        *,
        ROW_NUMBER() OVER() AS global_row_id,
        ROW_NUMBER() OVER(PARTITION BY emp_id, job_code ORDER BY (SELECT NULL)) AS cnt_check
    FROM employee
)
UPDATE employee t1
JOIN employee_with_row t2
    ON t1.emp_id = t2.emp_id
    AND t1.job_code = t2.job_code
    -- 用全局唯一行号确保每行只匹配一次
    AND (SELECT COUNT(*) FROM employee WHERE emp_id <= t1.emp_id AND (emp_id < t1.emp_id OR job_code <= t1.job_code)) = t2.global_row_id
SET t1.cnt_check = t2.cnt_check;

原语句为啥不行？ #

你写的UPDATE语句只靠emp_id和job_code关联，这会导致同一组（相同emp_id+job_code）的每一行，都能匹配到子查询里该组的所有行。执行UPDATE时，每行的cnt_check会被反复赋值，MySQL最后只会保留最后一次的赋值结果，再加上row_number()没指定排序规则，顺序随机，结果就全变成1了。

核心问题是缺少唯一匹配条件，必须让原表的每一行只对应子查询里的唯一一行，这样才能正确分配递增序号。上面的语句通过生成全局唯一的global_row_id解决了这个问题。

兼容MySQL 5.x的替代方案 #

如果用的是老版本MySQL，可以用用户变量生成序号：

-- 创建临时表存储带序号的行数据
CREATE TEMPORARY TABLE temp_employee AS
SELECT 
    emp_id,
    job_code,
    @global_id := @global_id + 1 AS global_id,
    IF(@prev_emp = emp_id AND @prev_job = job_code, @cnt := @cnt + 1, @cnt := 1) AS cnt_check,
    @prev_emp := emp_id,
    @prev_job := job_code
FROM employee,
     (SELECT @global_id := 0, @cnt := 0, @prev_emp := '', @prev_job := '') AS init
ORDER BY emp_id, job_code;

-- 通过全局ID匹配更新原表
UPDATE employee t1
JOIN (
    SELECT 
        emp_id,
        job_code,
        cnt_check,
        global_id,
        (SELECT COUNT(*) FROM employee WHERE emp_id <= t.emp_id AND (emp_id < t.emp_id OR job_code <= t.job_code)) AS emp_pos
    FROM temp_employee t
) t2
ON (SELECT COUNT(*) FROM employee WHERE emp_id <= t1.emp_id AND (emp_id < t1.emp_id OR job_code <= t1.job_code)) = t2.global_id
SET t1.cnt_check = t2.cnt_check;

DROP TEMPORARY TABLE temp_employee;

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