php生成一个字符串并从mysql检查不存在

I want to generate a string to post a url. Then make the post url like: http://www.mydomain.com/post/afCeYk, and store this url in the mysql. In order to avoid a repeat url , I think first should check the mysql whether the url has already existed. In my code, I just check once, I can not ensure the second generate string hasn't already existed. So how do I make a loop?

我想生成一个字符串来发布网址。然后将帖子的URL设置为：http：//www.mydomain.com/post/afCeYk，并将此url存储在mysql中。为了避免重复url，我认为首先应该检查mysql是否已经存在url。在我的代码中，我只检查一次，我无法确保第二个生成字符串尚未存在。那我怎么做一个循环呢？

$shufstr = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
$rdstr = substr(str_shuffle($shufstr),0,6);
$query =  mysql_query("select * from table where post_url = '".$rdstr."'");
if(mysql_num_rows($query)>0){
    //insert the url rules into db  
}else{
    //generate a new string and check the db again  
}

3 个解决方案

#1

You should query the database once to collect all of the data from the table, then generate a string and check it against the array you get.

您应该查询数据库一次以收集表中的所有数据，然后生成一个字符串并根据您获得的数组进行检查。

As opposed to querying the database over and over, this has a performance benefit.

与反复查询数据库相反，这具有性能优势。

(not actual code)

（不是实际代码）

$url_list = query("SELECT `post_url` FROM `table`");
do {
    $random_string = generate_random_string();
}
while(!in_array($random_string, $url_list));

In addition, make sure no duplicate is entered by making the column UNIQUE.

此外，通过使列UNIQUE确保没有输入重复项。

#2

You can use a while loop, but it would get pretty slow after you have a few thousand URLs saved:

你可以使用while循环，但是在保存了几千个URL后它会变得非常慢：

$shufstr = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
$validString=false;
while(!$validString){
    $rdstr = substr(str_shuffle($shufstr),0,6);
    $query =  mysql_query("select * from table where post_url = '".$rdstr."'");
    if(mysql_num_rows($query)==0){ //This is also different from your code as you don't want to do the insert if there is 1+ row with that url. 
        $validString=true;
        //insert the url rules into db  
    }
}

#3

If it were my project, I would add a UNIQUE constraint on the post_url column itself, this will ensure that no duplicates will be entered from any point of entry (app, command line, etc). More info on MySQL unique.

如果它是我的项目，我会在post_url列本身添加一个UNIQUE约束，这将确保不会从任何入口点（app，命令行等）输入重复项。关于MySQL的更多信息。

#1