SQL中如何高效实现数据表字段的随机更新操作？

原脚本耗时过高的原因 #

你现在的写法属于相关子查询，每更新personnels表的一行数据，就会单独执行一次全表扫描+随机排序取top1的操作，表数据量越大，重复执行的子查询次数越多，整体耗时会呈指数级增长。

最优解决方案（SQL Server场景，适配NEWID()语法） #

通过CTE预先生成打乱顺序的名字映射，仅扫描两次表即可完成全量更新，时间复杂度为线性O(n)：

WITH ShuffledNames AS (
    -- 第一步：把所有first_name打乱，生成有序行号
    SELECT 
        first_name,
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
    FROM personnels
),
OriginalRows AS (
    -- 第二步：给原表的每行数据也生成有序行号
    SELECT 
        id, -- 替换为你表的实际主键字段名
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM personnels
    -- 如果只需要更新部分行，在这里加WHERE过滤条件即可
)
-- 第三步：通过行号关联完成随机更新
UPDATE p
SET p.first_name = sn.first_name
FROM personnels p
INNER JOIN OriginalRows o ON p.id = o.id
INNER JOIN ShuffledNames sn ON o.rn = sn.rn;

适配其他数据库的修改点 #

只需要替换随机排序函数即可：

• MySQL：将NEWID()替换为RAND()
• PostgreSQL：将NEWID()替换为RANDOM()

额外优化建议 #

• 确保原表主键有索引，关联更新的速度会大幅提升
• 如果需要更新的数据量极大，可以分批次批量更新，避免锁表时间过长

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