mySQL 返回可能重复的随机行 [英] mySQL return random rows with possible duplicates

问题描述

我正在尝试随机化一定数量的行，但假设数据库中只有 4 行，我需要获得 6 行我想要的随机行(即使表中有超过 6 行)产生重复的行.

I'm trying to randomise a certain number of rows but lets say there are only 4 rows in the database and i need to get 6 random rows I want the possibility (even if there are more than 6 rows in the table) to produce duplicate rows.

这在 mySQL 中容易实现吗?

Is this easily achieved in mySQL ?

我目前的查询是这样的:

My current query is like this:

SELECT * FROM winners ORDER BY RAND() LIMIT 6

这个想法是一个用户可以赢得不止一次.:)

The idea is a user can win more than once. :)

希望能帮到你！！

推荐答案

任何涉及 ORDER BY RAND() 的解决方案都是不赞成的，因为它不能使用索引，而且基本上是对整体进行排序表(可能会变得非常大)只是为了选择一行.

Any solution involving ORDER BY RAND() is frowned upon, because it can't use an index and it basically sorts the whole table (which may grow very large) just to pick one row.

更好的解决方案是在 MIN(id) 和 MAX(id) 之间生成一个随机数，这是您选择的随机行.随着您的桌子变大，这将成为越来越大的优势.

The better solutions involve generating a random number between MIN(id) and MAX(id) and that's your chosen random row. As your table gets larger, this becomes a bigger and bigger advantage.

选择一个随机 ID 效率更高，我建议一次只选择六个随机 ID，然后一次查找那些行.因此，您有机会多次选择给定的行.

It's so much more efficient to pick a random ID, that I'd recommend just picking six random ID's one at a time, and then looking up those rows one at a time. Therefore you have a chance of picking a given row more than once.

如果您不能保证所有 ID 都是连续的，您可以选择比随机选择大的第一个 ID.所以在伪代码中:

If you aren't guaranteed that all your ID's are consecutive, you can pick the first ID that is greater than the random pick. So in pseudocode:

$MIN, $MAX = SELECT MIN(ID), MAX(ID) FROM winners
FOR LOOP FROM 1 to 6
    $R = $MIN+RANDOM($MAX-$MIN)
    $WINNER[] = SELECT * FROM winners WHERE id >= $R LIMIT 1

这篇关于mySQL 返回可能重复的随机行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！