使用php&随机数据MySQL的

问题描述：

我具有如下所示的mysql数据库结构:

I have mysql database structure like below:

CREATE TABLE test (
    id int(11) NOT NULL auto_increment,
    title text NULL,
    tags text NULL,
    PRIMARY KEY (id)
);

关于字段标签的数据以逗号分隔的文本形式存储，例如html，php，mysql，网站，html等. 现在我需要创建一个数组，其中包含从随机记录中随机选择的大约50个标签.

data on field tags is stored as a comma separated text like html,php,mysql,website,html etc... now I need create an array that contains around 50 randomly selected tags from random records.

当前，我正在使用rand()从数据库中选择15个随机mysql数据，然后将15个记录中的所有标签保存在一个数组中.然后，我使用array_rand()将数组随机化，并仅选择50个随机标签.

currently I am using rand() to select 15 random mysql data from database and then holding all the tags from 15 records in an array. Then I am using array_rand() for randomizing the array and selecting only 50 random tags.

$query=mysql_query("select * from test order by id asc, RAND() limit 15");
$tags="";
while ($eachData=mysql_fetch_array($query)) {
    $additionalTags=$eachData['tags'];
    if ($tags=="") {
        $tags.=$additionalTags;
    } else {
        $tags.=$tags.",".$additionalTags;
    }
}

$tags=explode(",", $tags);
$newTags=array();
foreach ($tags as $tag) {
    $tag=trim($tag);
    if ($tag!="") {
        if (!in_array($tag, $newTags)) {
            $newTags[]=$tag;
        }
    }
}

$random_newTags=array_rand($newTags, 50);

现在，我在数据库上拥有大量记录，因此， rand()的执行速度非常慢，有时无法正常运行.因此，谁能让我知道如何正确处理这种情况，以便我的页面正常工作.

Now I have huge records on the database, and because of that; rand() is performing very slow and sometimes it doesn't work. So can anyone let me know how to handle this situation correctly so that my page will work normally.

答

从不ORDER BY RAND()-性能令人恐惧.而是在PHP中进行随机化.之所以这样，是因为您的ID是自动递增的(可能不是最好的方法):

Never ORDER BY RAND() - it's horrible for performance. Instead do the randomizing in PHP. Something like this, since your ID is auto incrementing (may not be the best approach):

$count = mysql_fetch_assoc(mysql_query("select count(1) as count from test"));
$range = range(0, $count['count']);

$selection = array_rand($range, 50);
$sel_list = implode(',', $selection);

$query = mysql_query("select * from test where id in ($sel_list)");

顺便说一句，为什么要将标记放在字符串列表中，只是以后再爆炸该字符串?只需从一开始就将它们放入数组中即可.

By the way, why are you putting your tags in a string list, only to explode that string later? Just put them into an array from the start.

相关推荐