从另一个表中随机更新MySQL表的最佳方法

Question

以下是一个MySQL（伪代码）举例，我有两张表：

member { id, name }
names { name }

成员表中有100个成员和10个名字。我想从这些名字中随机选择一个来更新成员表。目前我已经有了下面的代码，但是不确定是否有更好的方法可以实现。

UPDATE member SET name = (SELECT name FROM names ORDER BY RAND() LIMIT 1);

代码将从脚本中执行，因此我希望避免使用函数等。谢谢您提前的帮助。

- Ian

---

我觉得看起来没问题。只需在MySQL中检查行为-确保子选择实际上为您提供随机名称，而不是为整个集合使用一个随机值。 - YXD

Answer 1

您可以避免使用 rand() 排序，只需在名字表中添加 id 列并使用以下代码即可：

```sql SELECT * FROM names ORDER BY id ASC ```

UPDATE member SET name = (SELECT name FROM names WHERE id=floor(1 + rand()*10 ) );

如果只有10个名称，使用rand()排序的结果不会更快，但是如果您想从更大的名称集中进行选择，则会看到差异，因为rand()排序很快就变得效率低下，并且您需要对members中的每一行进行排序。

更新： 似乎在where子句中使用rand()会产生不可预测的结果。 请改用以下方法：

UPDATE member m1
JOIN ( select id, floor(1+rand()*10) as rnd from member ) m2 on m1.id=m2.id
JOIN names n on n.id = m2.rnd
SET m1.name=n.name

受影响的行数可能会有所不同，如果随机名称与表中已有的名称匹配，则不计为更新。

- piotrm

---

+1 很好：我之前一直在寻找这样的东西！ - Lukas Eder

大部分情况下它是有效的，但是每隔几次它会返回NULL而不是一个值。查看控制台日志，它说“子查询返回多行” - 我已经检查了id，它们是自动增量并且是唯一的。我尝试将10调整为9，因为mysql文档建议应该是*(j-i)，但仍然相同。有什么想法吗？ - Ian

更新答案，Devart的解决方案也不错，受影响的行数因相同原因而异。 - piotrm

Answer 2

尝试改进piotrm的解决方案。看起来它有效;-)

CREATE TABLE member (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE names (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);

INSERT INTO member VALUES 
  (1, NULL),
  (2, NULL),
  (3, NULL),
  (4, NULL),
  (5, NULL),
  (6, NULL),
  (7, NULL),
  (8, NULL),
  (9, NULL),
  (10, NULL),
  (11, NULL),
  (12, NULL),
  (13, NULL),
  (14, NULL),
  (15, NULL);

INSERT INTO names VALUES 
  (1, 'text1'),
  (2, 'text2'),
  (3, 'text3'),
  (4, 'text4'),
  (5, 'text5'),
  (6, 'text6'),
  (7, 'text7'),
  (8, 'text8'),
  (9, 'text9'),
  (10, 'text10');

UPDATE
  member m1
  JOIN (SELECT id, @i:=FLOOR(1 + RAND() * 10), (SELECT name FROM names n WHERE n.id = @i) name FROM member) m2
    ON m1.id = m2.id
SET
  m1.name = m2.name;

- Devart

---

仍然存在同样的问题。如果您创建所有表并运行更新，则会填充所有表。但是，如果您仅重新运行更新语句并查看更新的行数，则会有所不同。我需要让所有15条记录都获得新名称。 - Ian

是的，有15行被更新了，但并不是所有行都获得了新值。我认为可以使用存储函数来更新所有行。 - Devart