如何在MySQL中为一列多行更新指定的不同唯一值？

我有一张名为data_table的数据表，初始样本数据如下：

希望对temp_id列执行update操作，为各行设置从1001开始的连续唯一值，更新后的数据如下：

根据不同数据库版本，提供以下两种实现方式：

支持窗口函数的数据库（MySQL 8.0+、PostgreSQL、SQL Server） #

使用ROW_NUMBER()窗口函数生成连续序号，加上999的偏移量得到目标temp_id值：

-- MySQL 8.0+
UPDATE data_table dt
JOIN (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM data_table
) t ON dt.id = t.id AND dt.name = t.name AND dt.type = t.type AND dt.temp_id = t.temp_id
SET dt.temp_id = t.rn + 999;

-- PostgreSQL
WITH ranked_data AS (
    SELECT 
        ctid,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM data_table
)
UPDATE data_table dt
SET temp_id = rd.rn + 999
FROM ranked_data rd
WHERE dt.ctid = rd.ctid;

-- SQL Server
WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM data_table
)
UPDATE ranked_data
SET temp_id = rn + 999;

MySQL 5.x（不支持窗口函数） #

使用用户变量生成连续递增的值：

SET @row_num = 999;
UPDATE data_table
SET temp_id = (@row_num := @row_num + 1)
ORDER BY id;

说明 #

• 以上方案默认按id排序生成temp_id，若需要其他排序规则，修改ORDER BY后的字段即可。
• 如果表中有主键或唯一标识列，用主键关联会比多字段关联更准确，避免重复数据导致的更新错误。

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