使用MySQL选择随机行 [英] Selecting random rows with MySQL

问题描述

我看到了很多与此主题有关的主题，但在理解如何做上却没有成功.

I saw many topics about this subject and I have been unsuccessful in understanding how to do it.

例如，如果我有此表:

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

并且我只想显示一个"类中的X个随机行，我该怎么做?

and I want to show only X random rows from class "one", how can I do that?

注意:这是一张大桌子，所以我不想使用ORDER BY RAND.

NOTE: it's a big table, so I don't want to use ORDER BY RAND.

推荐答案

大多数人建议的ORDER BY RAND()解决方案无法扩展到大型表，如您所知.

The ORDER BY RAND() solution that most people recommend doesn't scale to large tables, as you already know.

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反模式:避免以下方面的陷阱中介绍了该解决方案和其他解决方案:数据库编程.

如果要使用PHP进行此操作，则可以执行以下操作(未测试):

If you want to do this with PHP, you could do something like this (not tested):

<?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();

这篇关于使用MySQL选择随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！