UPDATE使用随机子查询更新时所有行值相同 如何逐行执行子查询

问题原因 #

你原来的SQL中子查询没有和外层users表的行产生关联，数据库优化器会将其判定为独立的常量表达式，只会执行一次并缓存结果应用到所有更新行，所以才会出现所有行的姓名都相同的问题。

解决方法 #

方案1：子查询绑定外层字段（通用快速修改） #

只需要在子查询的WHERE条件里引用任意一个外层users表的字段，强制数据库判定子查询结果和每行相关，必须逐行执行即可，不同数据库的写法差异只在随机函数上：

• PostgreSQL 版本：

UPDATE users 
SET 
  first_name = (SELECT firstname FROM temp_names WHERE users.id IS NOT NULL ORDER BY random() LIMIT 1),
  last_name = (SELECT lastname FROM temp_names WHERE users.id IS NOT NULL ORDER BY random() LIMIT 1);

• MySQL 版本：

UPDATE users 
SET 
  first_name = (SELECT firstname FROM temp_names WHERE users.id = users.id ORDER BY rand() LIMIT 1),
  last_name = (SELECT lastname FROM temp_names WHERE users.id = users.id ORDER BY rand() LIMIT 1);

方案2：批量关联更新（性能更优） #

如果表数据量较大，逐行执行子查询的性能会比较差，可以通过先随机排序再关联的方式实现，效率更高，同时支持users表行数大于temp_names表的场景，以下是PostgreSQL示例：

WITH shuffled_users AS (
  -- 给用户表按随机顺序编序号
  SELECT id, ROW_NUMBER() OVER (ORDER BY random()) AS rn FROM users
),
shuffled_names AS (
  -- 给姓名表按随机顺序编序号，同时统计姓名总数量
  SELECT 
    firstname, 
    lastname, 
    ROW_NUMBER() OVER (ORDER BY random()) AS rn,
    COUNT(*) OVER () AS total_cnt
  FROM temp_names
)
UPDATE users u
SET 
  first_name = sn.firstname,
  last_name = sn.lastname
FROM shuffled_users su
-- 序号取模实现循环匹配姓名
JOIN shuffled_names sn ON su.rn = (sn.rn % sn.total_cnt) + 1
WHERE u.id = su.id;

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