使用php&随机数据MySQL的 [英] random data using 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.

这篇关于使用php&随机数据MySQL的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！