只需访问数据库一次,就可以立即获取所有内容,这很可能是最大的瓶颈。

$query = "SELECT * FROM $table_name WHERE  $field_name  LIKE '".$slug."%'";

然后将结果放入一个数组中(比如 $slugs )

//we only bother doing this if there is a conflicting slug already
if(mysqli_num_rows($result) !== 0 && in_array($slug, $slugs)){
    $max = 0;

    //keep incrementing $max until a space is found
    while(in_array( ($slug . '-' . ++$max ), $slugs) );

    //update $slug with the appendage
    $slug .= '-' . $max;
}

我们使用 in_array() 检查,就好像鼻涕虫 my-slug 这个 LIKE 还将返回行,例如

my-slug-is-awesome
my-slug-is-awesome-1
my-slug-rules

等等,这将导致问题 in_array()中 检查可以确保我们只检查输入的鼻涕虫。

为什么我们不计算结果和+1?

这是因为,如果你有多个结果,并删除了一些,你的下一个鼻涕虫很可能会发生冲突。

例如。

my-slug
my-slug-2
my-slug-3
my-slug-4
my-slug-5

删除-3和-5留给我们

my-slug
my-slug-2
my-slug-4

因此,这给了我们3个结果,下一个插入将是 my-slug-4 它已经存在。

为什么我们不直接使用 ORDER BY 和 LIMIT 1 ?

我们不能只做 order by 在查询中,因为 ^缺少 自然排序会使 my-slug-10 等级低于 my-slug-4型 因为它逐个字符进行比较 4 高于 1

例如。

m = m
y = y
- = -
s = s
l = l
u = u
g = g
- = -
4 > 1 !!!
  < 0 (But the previous number was higher, so from here onwards is not compared)

只需使用一个查询即可为您完成所有繁重的工作。。。

$slug = preg_replace("/-$/","",preg_replace('/[^a-z0-9]+/i', "-", strtolower($title)));

$query = "SELECT COUNT(*) AS NumHits FROM $table_name WHERE  $field_name  LIKE '$slug%'";
$result = mysqli_query($db_connect, $query) or die(mysqli_error($db_connect));
$row = $result->fetch_assoc();
$numHits = $row['NumHits'];

return ($numHits > 0) ? ($slug . '-' . $numHits) : $slug;

您只需选择数量最大的段塞,然后将其增加1:

$query = "SELECT $field_name FROM $table_name WHERE  $field_name LIKE '".$slug."-[0-9]*' ORDER BY $field_name DESC LIMIT 1";

[0-9]* 在查询中指的是任何数量。

此查询将选择具有 $slug 在开始和最大的数字。

之后,您可以解析结果,得到数字并增加它。

在这种情况下,您将只有一个查询和许多未使用的性能。

更新

这行不通,因为 slug-8 将比 slug-11 。但不知道怎么解决。也许吧 ORDER BY 身份证件 DESC ?

更新2

查询也可以按长度排序,它将正常工作。感谢Jack:

$query = "SELECT $field_name FROM $table_name WHERE  $field_name LIKE '".$slug."-[0-9]*' ORDER BY LENGTH($field_name), $field_name DESC LIMIT 1";

更新3

还增加了对原始段塞的检查。多亏了海伍德。

$query = "SELECT $field_name FROM $table_name WHERE $field_name = '".$slug."' OR $field_name LIKE '".$slug."-[0-9]*' ORDER BY LENGTH($field_name), $field_name DESC LIMIT 1";

你为什么不创建一个slug,然后把剩下的涉及到MySQL索引的工作留下来呢。这是一个 slugify 函数(它是Symfony框架使用的稍微修改过的版本)。

function slugify( $text ) {
    $text = preg_replace('~[^\\pL\d]+~u', '-', $text);  
    $text = trim($text, '-');
    $text = iconv('utf-8', 'ASCII//IGNORE//TRANSLIT', $text);   
    $text = strtolower(trim($text));
    $text = preg_replace('~[^-\w]+~', '', $text);

    return empty($text) ? substr( md5( time() ), 0, 8 ) : $text;
}

MySQL部分可以用 trigger (更改表名和列名)。

BEGIN
     declare original_slug varchar(255);
     declare slug_counter int;
     set original_slug = new.slug;
     set slug_counter = 1;
     while exists (select true from post where slug = new.slug) do
        set new.slug = concat(original_slug, '-', slug_counter); 
        set slug_counter = slug_counter + 1;
     end while;
END

MySQL Insert row, on duplicate: add suffix and re-insert

对于一个部分,我会创建一个对象,处理创建段塞和处理数字的部分:

// generate new slug:
$slug = new NumberedSlug('Creating Unique Page Title Slugs in PHP');
echo $slug, "\n", $slug->increase(), "\n";

// read existing slug:
$slug = new NumberedSlug('creating-unique-page-title-slugs-in-php-44');
echo $slug->getNumber(), "\n";

输出:

creating-unique-page-title-slugs-in-php
creating-unique-page-title-slugs-in-php-1
44

对于数据库的另一部分,这已经大大简化了您的代码(请仔细检查,我很快就完成了)。还可以看看如何从您实际拥有的Mysqli对象中获益(但不要按原样使用):

function createSlug($title, $table_name, $field_name, Mysqli $mysqli = NULL)
{
    $mysqli || $mysqli = $GLOBALS['db_connect'];

    $slug = new NumberedSlug($title);

    do
    {
        $query = "SELECT 1 FROM $table_name WHERE  $field_name  = '" . $slug . "'";

        if (!$result = $mysqli->query($query)) {
            throw new RuntimeException(var_export($mysqli->error_list, true));
        }

        if ($result->num_rows) {
            $slug->increase();
        }

    } while ($result->num_rows);

    return $slug;
}

但正如其他人已经写的那样,你应该首先从数据库中一次获得所有编号的slug,然后在必要时选择一个唯一的slug。这将减少数据库调用的次数。此外,代码更加紧凑:

function createSlug2($title, $table_name, $field_name, Mysqli $mysqli = NULL)
{
    $mysqli || $mysqli = $GLOBALS['db_connect'];

    $slug = new NumberedSlug($title);

    $query = "SELECT $field_name FROM $table_name WHERE $field_name LIKE '$slug-_%'";

    if (!$result = $mysqli->query($query)) {
        throw new RuntimeException(var_export($mysqli->error_list, true));
    }

    $existing = array_flip(call_user_func_array('array_merge', $result->fetch_all()));

    $slug->increase();

    while (isset($existing[$slug])) 
    {
        $slug->increase();
    }

    return $slug;
}

See it in action.

我对答案并不完全满意,所以我想出了一个稍微不同的方法。

(SELECT CONCAT({$slug}, '-', counter) FROM (
  SELECT (@row_number:=@row_number + 1) AS counter, ev.*
  FROM (
    SELECT REPLACE(slug, {$slug}-, '') AS remainder
    FROM products, (SELECT @row_number:=0) AS t
    WHERE slug LIKE '{$slug}%'
  ) ev
  ORDER BY LENGTH(remainder), remainder
) sr
WHERE counter <> remainder)
LIMIT 1

这基本上是这样做的,它检查DB中所有与新段塞相似的现有值,并将其与行号匹配以检查间隙,如果没有找到,它将使用第一个相同的段塞生成的最大值,该段塞被推到最后(注意:我们将 slug- 而不是 slug )

$query = "SELECT * FROM $table_name WHERE  $field_name  LIKE '".$slug."%'";
$result = mysqli_query($db_connect, $query) or die(mysqli_error($db_connect));
//EDITED BASED ON COMMENT SUGGESTIONS
//create array of all matching slug names currently in database
$slugs = array();
while($row = $result->fetch_row()) {
    $slugs[] = $row['field_name'];
}
//test if slug is in database, append - '1,2,..n' until available slug is found
if(in_array($slug, $slugs)){
    $count = 1;
    do{
       $testSlug = $slug . '-' . $count;
       $count++;
    } while(in_array($testSlug, $slugs));
    $slug = $testSlug;
}
//insert slug 

您应该能够在使用LIKE关键字的单个数据库调用中做到这一点,这将减少执行时间。

你可以使用 Fbeen/UniqueSlugBundle 。这个捆绑包很轻,可以做它需要做的事情。