RAND()的多行替代 [英] Multiple rows alternative for RAND()

问题描述

我在从具有50k行的数据库中选择100条随机行的查询中遇到了问题.

I am having problems with my query for selecting 100 random rows from a database with 50k of rows.

首先，我只使用了RAND()函数，但这变得太慢了.

First i only used the function RAND() but that became too slow.

现在我正在使用此查询:

Now i am using this query:

$query = mysql_query("SELECT t1.* FROM users AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM users WHERE picture != '' && age <= 50 && age >= 18 && username != '')) AS id) AS t2 WHERE picture != '' && age <= 50 && age >= 18 && username != '' && t1.id >= t2.id LIMIT 100");
while($q = mysql_fetch_assoc($query)) 
{ echo $q['id'].'<br>'; }

但是有时它会给出一堆空行.

But sometimes it gives a bunch of empty rows.

有人可以告诉我怎么了吗?

Can anybody tell me whats going wrong?

谢谢！

更新，我也在尝试此查询，但它给了我一个错误.

UPDATE I am trying this query too but it gives me an error.

$query = mysql_query("SELECT id FLOOR(1 + RAND() * x.m_id) 'rand_ind' FROM users (SELECT MAX(t.id) - 1 'm_id' FROM users t) x WHERE picture != '' && age <= 50 && age >= 18 && username != '' ORDER BY rand_ind LIMIT 100");

mysql_fetch_assoc():提供的参数不是有效的MySQL结果资源

推荐答案

如果表中的ID是连续的，为什么不直接生成介于0到"max_rows"之间的x个随机数.

If your id's in the table are sequential, why not just generate x random numbers between 0 and "max_rows".

然后做类似的事情

'SELECT ... FROM .... WHERE id in (' . implode(',', $randomNumbers) . ')';

它将返回带有生成ID的行.

It will return the rows with the generated id's.

这篇关于RAND()的多行替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！