SQL如何为联系人表每条记录随机分配匿名表中的姓名

你写的SQL中RAND()函数在整个UPDATE语句执行阶段只会被计算1次，不会为contacts表的每一条记录单独生成随机值，最终所有联系人都会匹配到匿名姓名表中同一个id对应的姓名，才会出现全表同名的结果。
另外你用ROUND(RAND()*99)+1的写法存在概率偏差：首尾两个id被选中的概率只有其余id的一半，换成FLOOR(1 + RAND()*100)可以保证1-100的所有id被选中的概率完全均等。

通用兼容写法 #

这个写法在绝大多数主流MySQL版本中都可以正常运行，子查询会为contacts表的每一行单独生成随机id匹配姓名：

UPDATE contacts
SET nom = (
  SELECT nom
  FROM anonymes
  WHERE id_anonyme = FLOOR(1 + RAND() * 100)
  LIMIT 1
);

• 如果你的匿名表id_anonyme不是连续的1-100序列，可以把子查询替换为SELECT nom FROM anonymes ORDER BY RAND() LIMIT 1，不需要做id计算，适配性更强。
• 如果遇到部分数据库查询优化器缓存随机值导致仍然出现同名问题，可以给子查询加一个和外层表的关联，强制数据库逐行计算随机值：

UPDATE contacts c
SET c.nom = (
  SELECT a.nom
  FROM anonymes a
  WHERE a.id_anonyme = FLOOR(1 + RAND() * 100) * c.id / c.id
  LIMIT 1
);

注意把上面SQL里的c.id替换成你contacts表的实际主键字段名即可。

高版本数据库简洁写法 #

如果你用的是MySQL 8.0.14+、PostgreSQL等支持LATERAL（横向关联）语法的版本，可以用更直观的写法，性能也更稳定：

UPDATE contacts c
SET c.nom = a.nom
FROM LATERAL (
  SELECT nom
  FROM anonymes
  ORDER BY RAND()
  LIMIT 1
) a;

两张表数据量都很小，这几种写法的执行效率没有明显差异，都可以快速完成更新。

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