使用MySQL随机选择行

Question

我看到了很多关于这个主题的话题，但我一直无法理解如何做。

例如，如果我有这个表：

+------+-------+-------+
| id   | name  | class |
+------+-------+-------+
|    5 | test  | one   | 
|   10 | test2 | one   | 
|   12 | test5 | one   | 
|    7 | test6 | two   | 
+------+-------+-------+

我想展示"one"类别中的X个随机行，该怎么做？

注意：这是一个大表格，所以我不想使用"ORDER BY RAND"。

- Daniel

---

可能是从MySQL的大表中快速选择随机行的重复问题。 - outis

Answer 1

大多数人推荐的ORDER BY RAND()解决方案在处理大型表时不具有可扩展性，正如您已经知道的那样。

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM mytable)));
SET @sql := CONCAT('SELECT * FROM mytable LIMIT 1 OFFSET ', @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

我在我的书中详细介绍了这个解决方案和其他解决方案，SQL反模式卷1：避免数据库编程陷阱。

---

如果您想用PHP实现这个功能，可以按如下方式操作（未经测试）：

<?php
$mysqli->begin_transaction();
$result = $mysqli->query("SELECT COUNT(*) FROM mytable")
$row = $result->fetch_row(); 
$count = $row[0]; 
$offset = mt_rand(0, $count);
$result = $mysqli->query("SELECT * FROM mytable LIMIT 1 OFFSET $offset");
...
$mysqli->commit();

- Bill Karwin

---

@Lumbendil：我支持在适当的情况下使用参数。在上面的例子中，SQL注入的风险是最小的。我们知道该值来自于“rand()”，而该函数仅返回整数。 - Bill Karwin

但是，如果想要检索多行数据，则可以多次执行查询而无需在多个场合上进行分析。 - Lumbendil

对于大表，如果我想使用order by rand()选择一行随机行，这将非常有用：select * from (select from 大表 where 某些条件) new_table order by rand() limit 0, 1; - thekosmix

@thekosmix，你不应该需要做那个子查询，因为无论如何，WHERE条件在应用ORDER BY之前都会限制行数。 - Bill Karwin

@BillKarwin在PHP中使用rand()处理大型数据集时效果非常糟糕。我建议改用mt_rand()，因为PHP文档声称它的速度比rand()快4倍。我发现它生成的随机结果比rand()更加随机。 - BizLab

Answer 2

select ID, NAME, CLASS
from YOURTABLE
where CLASS='one'
order by rand()
limit $x

按rand()排序并不是特别高效，但这是最小/最快的方法。

- Marc B

---

注意：我使用了大表，并且使用ORDER BY RAND()对表中的所有行进行排序，这会导致速度非常慢。 - Daniel

是的，但它只会对班级为“one”的行进行排序，而不是整个表格。 - Marc B

类“one”可以包含数千行。 顺便问一下，我能拿到您的即时通讯和Skype吗？ - Daniel

Answer 3

SELECT * FROM `table` WHERE `class`="one" ORDER BY RAND() LIMIT 5

- Marco

---

注意：我使用了大表，并且进行了ORDER BY RAND()，这会对表中的所有行产生影响，导致查询速度非常慢。 - Daniel