如何用SELECT语句为MySQL的orders表批量随机更新多列地址字段？

你的原查询语句存在问题：LIMIT 1只会从addresses表中选出一个固定的随机地址，最终所有orders行都会被更新成同一个地址，无法实现“每行分配不同随机地址”的需求。

以下是针对不同场景的可行解决方案：

方案一：使用窗口函数（支持窗口函数的数据库，如MySQL 8+、PostgreSQL、SQL Server等） #

通过给两张表分别生成行号，orders表按顺序生成行号，addresses表按随机排序生成行号，再通过行号关联实现每行匹配不同的随机地址：

UPDATE orders o
-- 关联orders的行号子查询
JOIN (
    SELECT 
        order_id, -- 替换为orders表的唯一主键/标识列
        ROW_NUMBER() OVER () AS order_row
    FROM orders
) o_row ON o.order_id = o_row.order_id
-- 关联随机排序后的addresses行号子查询
JOIN (
    SELECT 
        address, address1, city, postcode,
        ROW_NUMBER() OVER (ORDER BY RAND()) AS addr_row
    FROM addresses
) a ON o_row.order_row = a.addr_row
SET 
    o.address = a.address,
    o.address1 = a.address1,
    o.city = a.city,
    o.postcode = a.postcode;

方案二：使用变量生成行号（适用于不支持窗口函数的旧版数据库，如MySQL 5.x） #

用自定义变量手动生成行号，实现和方案一相同的关联逻辑：

UPDATE orders o
JOIN (
    SELECT 
        id, -- 替换为orders表的唯一标识列
        @order_row := @order_row + 1 AS order_row
    FROM orders, (SELECT @order_row := 0) init_var
) o_row ON o.id = o_row.id
JOIN (
    SELECT 
        address, address1, city, postcode,
        @addr_row := @addr_row + 1 AS addr_row
    FROM addresses, (SELECT @addr_row := 0) init_var
    ORDER BY RAND()
) a ON o_row.order_row = a.addr_row
SET 
    o.address = a.address,
    o.address1 = a.address1,
    o.city = a.city,
    o.postcode = a.postcode;

注意事项 #

• 如果addresses表的行数少于orders表的行数，上述方案会有部分orders行无法匹配到地址。若需要循环使用addresses的地址，可将关联条件修改为：ON o_row.order_row % (SELECT COUNT(*) FROM addresses) = a.addr_row - 1
• 执行更新前建议先运行SELECT语句验证关联结果，确认地址分配符合预期，示例：

SELECT o.order_id, a.address, a.city
FROM orders o
JOIN (SELECT order_id, ROW_NUMBER() OVER () AS order_row FROM orders) o_row ON o.order_id = o_row.order_id
JOIN (SELECT address, city, ROW_NUMBER() OVER (ORDER BY RAND()) AS addr_row FROM addresses) a ON o_row.order_row = a.addr_row;

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