SQL按ID顺序更新序号，跳过已使用的order_number

要实现按规则更新order_number，核心是给目标记录（some_name为null）和可用序号分别添加行号，通过行号一一对应完成更新，具体方案如下：

核心思路 #

1. 给所有some_name为null的记录按id升序分配行号，确保后续序号按id顺序分配。
2. 生成未被some_name不为null的记录占用的最小递增序号列表，同样给每个序号分配行号。
3. 通过行号关联两个列表，将序号批量更新到对应记录的order_number字段。

完整更新SQL #

方式1：支持LIMIT子查询的MySQL版本 #

UPDATE my_table t
JOIN (
    -- 筛选目标记录并按id升序加行号
    SELECT id, @row_id := @row_id + 1 AS row_num
    FROM my_table, (SELECT @row_id := 0) init
    WHERE some_name IS NULL
    ORDER BY id ASC
) AS target_ids ON t.id = target_ids.id
JOIN (
    -- 生成未被占用的最小递增序号并加行号
    SELECT new_number, @row_num := @row_num + 1 AS row_num
    FROM (
        SELECT @ordnum := @ordnum + 1 AS new_number
        FROM my_table, (SELECT @ordnum := 0) init
        ORDER BY new_number
    ) AS seq
    LEFT JOIN (
        SELECT order_number
        FROM my_table
        WHERE some_name IS NOT NULL
    ) AS used ON seq.new_number = used.order_number
    WHERE used.order_number IS NULL
    ORDER BY seq.new_number
    LIMIT (SELECT COUNT(*) FROM my_table WHERE some_name IS NULL)
) AS available_nums ON target_ids.row_num = available_nums.row_num
SET t.order_number = available_nums.new_number;

方式2：不支持LIMIT子查询的MySQL版本 #

先统计目标记录数量，再执行更新：

-- 先获取需要更新的记录总数
SET @count = (SELECT COUNT(*) FROM my_table WHERE some_name IS NULL);

-- 执行更新
UPDATE my_table t
JOIN (
    SELECT id, @row_id := @row_id + 1 AS row_num
    FROM my_table, (SELECT @row_id := 0) init
    WHERE some_name IS NULL
    ORDER BY id ASC
) AS target_ids ON t.id = target_ids.id
JOIN (
    SELECT new_number, @row_num := @row_num + 1 AS row_num
    FROM (
        SELECT @ordnum := @ordnum + 1 AS new_number
        FROM my_table, (SELECT @ordnum := 0) init
        ORDER BY new_number
    ) AS seq
    LEFT JOIN (
        SELECT order_number
        FROM my_table
        WHERE some_name IS NOT NULL
    ) AS used ON seq.new_number = used.order_number
    WHERE used.order_number IS NULL
    ORDER BY seq.new_number
    LIMIT @count
) AS available_nums ON target_ids.row_num = available_nums.row_num
SET t.order_number = available_nums.new_number;

逻辑说明 #

• target_ids子查询：确保some_name为null的记录按id从小到大排序，每行对应唯一行号。
• available_nums子查询：生成连续正整数，排除已被some_name不为null的记录占用的序号，得到符合要求的最小递增序号列表，同样按顺序分配行号。
• 通过行号关联两个子查询，保证序号按id顺序逐一分配给目标记录。

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