我正在研究从MYSQL表进行的随机选择。我发现ORDER BY RAND()在大型数据库上非常慢。我正在尝试不配合使用其他方法,但是在返回时始终保持空查询。我不习惯AS语句,所以我的代码可能有问题吗?
public function getSmilies( $limit = null, $random = null )
{
$query = "SELECT find, url, id FROM smilies";
if($random == 1)
{
$query .= " AS r1 JOIN (SELECT (RAND() * (SELECT MAX(id) FROM smilies)) AS id) AS r2 WHERE r1.id >= r2.id ORDER BY r1.id ASC";
}
if($limit != null)
{
$query .= " LIMIT " . (int)$limit;
}
$stmt = $this->link->prepare($query);
$stmt->execute();
$stmt->bind_result( $find, $url, $id );
while($stmt->fetch())
$row[] = array('find' => $find, 'url' => $url, 'id' => $id );
$stmt->close();
return $row;
}
当没有参数传递给函数时,代码运行良好,但是当我添加随机参数时,它似乎失败了。
MySQL查询:
SELECT * FROM smilies WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM smilies ) LIMIT 5;
完整的PHP代码:
public function getSmilies( $limit = null, $random = null )
{
$query = "SELECT find, url, id FROM smilies";
if($random == 1)
{
$query .= " WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM smilies)";
}
if($limit != null)
{
$query .= " LIMIT " . (int)$limit;
}
$stmt = $this->link->prepare($query);
$stmt->execute();
$stmt->bind_result( $find, $url, $id );
while($stmt->fetch())
$row[] = array('find' => $find, 'url' => $url, 'id' => $id );
$stmt->close();
return $row;
}
注意1:仅适用于唯一ID。
注意2:仅测试了MySQL查询,未测试PHP代码。
来源:http://akinas.com/pages/en/blog/mysql_random_row/(解决方案3)
编辑:使用PHP的另一个选项。
public function getSmilies( $limit = null, $random = null )
{
$query = "SELECT find, url, id FROM smilies";
if($limit != null)
{
$query .= " LIMIT " . (int)$limit;
}
$stmt = $this->link->prepare($query);
$stmt->execute();
$stmt->bind_result( $find, $url, $id );
while($stmt->fetch())
$row[] = array('find' => $find, 'url' => $url, 'id' => $id );
$stmt->close();
if($random == 1)
{
suffle($row);
}
return $row;
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句