如何用PHP或SQL按匹配优先级排序MySQL查询结果？

问题描述 #

我现在用PHP查询MySQL数据库，使用的脚本如下：

$sql1 = $con->prepare("SELECT item FROM product WHERE item LIKE ?"); 
$q = '%' . $q . '%'; 
$sql1->bind_param("s", $q); 
$sql1->execute(); 
$sql1->bind_result($item); 
while ($sql1->fetch()) { 
    echo "<div>$item</div>";
}

当搜索关键词$q = 'test tube'时，当前结果按MySQL默认字母顺序排序为：

brush for test tube
test tube glass
test tube plastic

但我希望重新排序，让以搜索关键词开头的结果排在最前面，期望输出是：

test tube glass
test tube plastic
brush for test tube

请问该怎么实现呢？

解决方案 #

嘿，这个需求其实不难搞定，我给你两种可行的方案，你可以根据自己的场景选：

方案一：在MySQL查询阶段直接排序（更高效）

其实不用等到PHP端再处理，我们可以在SQL语句里加个自定义排序规则，让符合前缀匹配的记录优先。修改你的查询语句，用CASE给不同匹配情况分配排序权重：

$q = 'test tube';
$prefixMatch = $q; // 用于匹配前缀的完整关键词
$likeMatch = '%' . $q . '%'; // 原模糊匹配条件

$sql1 = $con->prepare("
    SELECT item 
    FROM product 
    WHERE item LIKE ?
    ORDER BY 
        -- 以关键词开头的记录排前面，权重0；其他权重1
        CASE WHEN item LIKE ? THEN 0 ELSE 1 END,
        -- 同权重的再按原字母顺序排序
        item ASC
");
// 绑定两个参数：模糊匹配的条件，以及前缀匹配的条件
$sql1->bind_param("ss", $likeMatch, $prefixMatch);
$sql1->execute();
$sql1->bind_result($item);

while ($sql1->fetch()) {
    echo "<div>$item</div>";
}

这样数据库会直接返回你想要的排序结果，效率更高，尤其是数据量比较大的时候，比PHP端排序省内存也更快。

方案二：PHP端拿到结果后再排序（适合需要自定义更多逻辑的场景）

如果你确实需要把结果先拿到PHP数组里再处理，那可以用usort()自定义排序规则：

$q = 'test tube';
$likeMatch = '%' . $q . '%';

$sql1 = $con->prepare("SELECT item FROM product WHERE item LIKE ?");
$sql1->bind_param("s", $likeMatch);
$sql1->execute();
$sql1->bind_result($item);

// 先把所有查询结果存入数组
$items = [];
while ($sql1->fetch()) {
    $items[] = $item;
}

// 用usort自定义排序逻辑
usort($items, function($a, $b) use ($q) {
    // 判断当前字符串是否以关键词开头
    $aStarts = strpos($a, $q) === 0;
    $bStarts = strpos($b, $q) === 0;

    // 排序规则：前缀匹配的排前面，都匹配或都不匹配则按字母顺序
    if ($aStarts && !$bStarts) {
        return -1; // a排b前面
    } elseif (!$aStarts && $bStarts) {
        return 1; // b排a前面
    } else {
        return strcmp($a, $b); // 按自然字母顺序排序
    }
});

// 输出排序后的结果
foreach ($items as $item) {
    echo "<div>$item</div>";
}

小建议 #

如果你的数据量不小，优先选方案一，数据库的排序优化做得更好，性能更高。如果只是小量数据，或者需要在PHP里做更多额外处理，方案二也完全没问题。

内容的提问来源于stack exchange，提问作者Newbee123