如何从数据库中回显随机行? [英] How to echo random rows from database?

问题描述

我有一个数据库表，其中包含约1.6亿行.

该表有两列:id和listing.

我只需要使用PHP从listing列中显示1000个随机行并将它们放入<span>标记中即可.像这样:

<span>Row 1</span>
<span>Row 2</span>
<span>Row 3</span>

我一直在尝试使用ORDER BY RAND()进行此操作，但是要花这么长时间才能加载到如此大的数据库上，而且我找不到其他解决方案.

我希望有一种快速/简便的方法来做到这一点.我无法想象仅仅回显1000个随机行是不可能的……谢谢！

解决方案

ORDER BY RAND()是一个适用于小型数据库的mysql函数，但是如果您运行的行大于10k，则应该在程序内部构建函数使用mysql预制函数或以特殊方式组织数据的方法.

我的建议:通过自动增量id保持mysql数据索引，或添加其他增量和唯一行.

然后构建一个选择函数:

<?php
//get total number of rows
$result = mysql_query('SELECT `id` FROM `table_name`', $link); 
$num_rows = mysql_num_rows($result); 

$randomlySelected = [];

for( $a = 0; $a < 1000; $a ++ ){

        $randomlySelected[$a] = rand(1,$num_rows);

}

//then select data by random ids
$where = "";

$control = 0;
foreach($randomlySelected as $key => $selectedID){

    if($control == 0){

        $where .= "`id` = '". $selectedID ."' ";

    } else {

        $where .= "OR `id` = '". $selectedID ."'";

    }
    $control ++;
}


$final_query = "SELECT * FROM `table_name` WHERE ". $where .";";
$final_results = mysql_query($final_query);    

?>

如果缺少该1.6亿个数据库中的某些增量ID，那么如果一个随机选择的ID数组少于所需数量，则可以轻松地添加一个函数来添加另一个随机ID(可能是while循环). /p>

如果您需要进一步的帮助，请告诉我.

I have a database table with about 160 million rows in it.

The table has two columns: id and listing.

I simply need to used PHP to display 1000 random rows from the listing column and put them into <span> tags. Like this:

<span>Row 1</span>
<span>Row 2</span>
<span>Row 3</span>

I've been trying to do it with ORDER BY RAND() but that takes so long to load on such a large database and I haven't been able to find any other solutions.

I'm hoping that there is a fast/easy way to do this. I can't imagine that it'd be impossible to simply echo 1000 random rows... Thanks!

解决方案

ORDER BY RAND() is a mysql function working fine with small databases, but if you run anything larger then 10k rows, you should build functions inside your program instead of using mysql premade functions or organise your data in special manners.

My suggestion: keep your mysql data indexed by auto increment id, or add other incremental and unique row.

Then build a select function:

<?php
//get total number of rows
$result = mysql_query('SELECT `id` FROM `table_name`', $link); 
$num_rows = mysql_num_rows($result); 

$randomlySelected = [];

for( $a = 0; $a < 1000; $a ++ ){

        $randomlySelected[$a] = rand(1,$num_rows);

}

//then select data by random ids
$where = "";

$control = 0;
foreach($randomlySelected as $key => $selectedID){

    if($control == 0){

        $where .= "`id` = '". $selectedID ."' ";

    } else {

        $where .= "OR `id` = '". $selectedID ."'";

    }
    $control ++;
}


$final_query = "SELECT * FROM `table_name` WHERE ". $where .";";
$final_results = mysql_query($final_query);    

?>

If some of your incremental IDs out of that 160 million database are missing, then you can easily add a function to add another random IDs (a while loop probably) if an array of randomly selected ids consists of less then required.

Let me know if you need some further help.

这篇关于如何从数据库中回显随机行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！