如何从SQL查询选择中选择随机行？

How can I select a row at random from a SQL database query? By this I mean:

如何从SQL数据库查询中随机选择一行?我的意思是:

Select all things with Category 'green' from table1:

从table1中选择所有类别为“green”的东西:

$stmt = $db->query('SELECT * from table1 WHERE Category LIKE "%green%"');

Then randomly display a row from this selection (as opposed to displaying every row from this selection as I've done below)

然后从该选择中随机显示一行(而不是显示此选择中的每一行,如下所示)

while($rows = $stmt->fetch()){
     echo "<tr><td>". $rows['Number'] . "</td><td>" . $rows['Content'] . "</td></tr>";
};

4 个解决方案

#1

In a reasonably-sized data set, order your rows randomly and select the first one:

在合理大小的数据集中,随机排序并选择第一个:

...ORDER BY RAND() LIMIT 1;

Your statement will become:

您的陈述将成为:

$stmt = $db->query(
    'SELECT * from table1
     WHERE Category LIKE "%green%"
     ORDER BY RAND() LIMIT 1;'
);

If you narrow your selection in your query, you will not need to use a messy process to extract a single row from the result set in your PHP code.

如果在查询中缩小选择范围,则不需要使用混乱的过程从PHP代码中的结果集中提取单行。

If your data set is very large, consider executing multiple queries as recommended by Tobias Hagenbeek:

如果您的数据集非常大,请考虑按照Tobias Hagenbeek的建议执行多个查询:

COUNT() the matching rows.

COUNT()匹配的行。

In PHP, select a random number between 1 and the result of COUNT().

在PHP中,选择1和COUNT()结果之间的随机数。

Perform new query to select the specified row:

执行新查询以选择指定的行:

...LIMIT <random number>, 1;

... LIMIT <随机数> ,1;

Finally, if you need only a single, arbitrary row and randomness/uniqueness is not an issue, consider selecting the first row from the table every time as suggested by Gordon Linoff:

最后,如果您只需要一个任意行并且随机/唯一性不是问题,请考虑每次按照Gordon Linoff的建议从表中选择第一行:

...LIMIT 1;

#2

The easy way is this:

简单的方法是:

$stmt = $db->query('SELECT * from table1 WHERE Category LIKE "%green%" ORDER BY RAND()');

ORDER BY RAND() will order the results randomly, but it is a bit expensive as an operation (link).

ORDER BY RAND()将随机排序结果,但它作为一个操作(链接)有点贵。

If you care about that sort of thing, you can alternatively query for the number of rows in the table, then do $r = rand(0, $count-1), then LIMIT 1 OFFSET $r at the end of your query.

如果您关心这类事情,您可以查询表中的行数,然后执行$ r = rand(0,$ count-1),然后在查询结束时使用LIMIT 1 OFFSET $ r。

#3

You could use ORDER BY RAND(), but you should be weary to do so. Especially if you are talking large systems, and more then 10k rows.

你可以使用ORDER BY RAND(),但你应该厌倦这样做。特别是如果你正在谈论大型系统,并且超过10k行。

Here's why...

What happens when you run such a query? Let’s say you run this query on a table with 10000 rows, than the SQL server generates 10000 random numbers, scans this numbers for the smallest one and gives you this row. Generating random numbers is relatively expensive operation, scaning them for the lowest one (if you have LIMIT 10, it will need to find 10 smallest numbers) is also not so fast (if quote is text it’s slower, if it’s something with fixed size it is faster, maybe because of need to create temporary table).

运行此类查询时会发生什么?假设您在具有10000行的表上运行此查询,而SQL服务器生成10000个随机数,扫描此数字以获取最小值,并为您提供此行。生成随机数是相对昂贵的操作,将它们扫描为最低值(如果你有LIMIT 10,它需要找到10个最小的数字)也不是那么快(如果引用是文本它更慢,如果它是固定大小的东西它更快,可能是因为需要创建临时表)。

So what you should do is a count on your rows, take a random number between 0 and your count-1, then do SELECT column FROM table LIMIT $generated_number, 1

所以你应该做的是计算你的行,在0和你的count-1之间取一个随机数,然后做SELECT列FROM表LIMIT $ generated_number,1

#4

If you want only one row from the set of all rows, then the fastest method is simply:

如果您只想要所有行的一行,那么最快的方法就是:

 SELECT *
 from table1
 WHERE Category LIKE "%green%"
 LIMIT 1;

This will give you the first row encountered in the data. To a close approximation, this is the first row inserted into the table that matches your criteria. (This is not a guarantee. For instance, deletes could definitely change this.)

这将为您提供数据中遇到的第一行。接近,这是插入表中符合条件的第一行。 (这不是保证。例如,删除肯定会改变这一点。)

This has the advantage of being fast'ish, which is useful because an index will not benefit you on the where clause. In this case, the query does a full table scan but stops at the first match.

这有一个快捷的优点,这很有用,因为索引不会让你在where子句上受益。在这种情况下,查询执行全表扫描,但在第一次匹配时停止。

The alternative for a truly random row is to use rand():

真正随机行的替代方法是使用rand():

SELECT *
from table1
WHERE Category LIKE "%green%"
order by rand()
limit 1;

This requires a full table scan that doesn't stop because all matches are needed for the sort. You then have the additional overhead of sorting the subset by rand(). There are some alternatives, if performance really is an issue.

这需要一个不会停止的全表扫描,因为排序需要所有匹配。然后,您需要额外的开销来按rand()对子集进行排序。如果性能真的是一个问题,有一些替代方案。

#1