MySql中的快速随机行 [英] Fast random row in MySql

问题描述

我需要从MySql数据库生成一个随机的行，我在这里找到了例子：
http://akinas.com/pages/en/blog/mysql_random_row/

I need to generate a random row from my MySql database and i have found example here: http://akinas.com/pages/en/blog/mysql_random_row/

我想使用解决方案3，看起来像这样：

And i want to use solution 3 which looks like this:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM `users` LIMIT $offset, 1 " );

我的代码现在看起来像这样：

My code looks like this now:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users` ");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( "SELECT * FROM `users` WHERE profile_image='2' LIMIT $offset, 1 " );
$random_date = mysql_fetch_array($result);

echo $random_date['user_name']; //display username of random user

但是当我刷新页面：approximatly 7 of 10 times nonthing向上。没有用户名，也试图打印出用户的ID，但它也是空的。
在刷新时，数据库似乎没有得到任何东西，有时它从数据库获取数据。

But when i refresh the page: approximatly 7 of 10 times nonthing shows up. No username at all and also im trying to print out the id of the user but it's also empty. It seems that it's not getting anything at all from the database when refreshing and sometimes it get's data from the database. Any idea why this might happen?

推荐答案

在这种特殊情况下，问题是你使用两个不同查询;第一个查询是：

In this particular case, the problem is that you're working with two different queries; the first query is:

SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `users`

第二个包括一个条件：

SELECT * FROM `users` WHERE profile_image='2' LIMIT $offset, 1
                      ^^^^^^^^^^^^^^^^^^^^^^^

两个查询都应该在相同的结果集上操作。

Both queries should operate over the same result set.

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