PHP MySQL自定义搜索引擎排序异常：需优先展示精确匹配结果

嘿，看来你遇到了搜索引擎排序逻辑不符合预期的问题——现在系统优先把关键词在内容末尾的结果排前面，但你想要先展示完全匹配的条目，再按匹配位置合理排序对吧？我来给你一套实用的解决方案：

核心思路：用MySQL的CASE语句自定义排序权重 #

我们可以给不同匹配情况设置不同的权重值，让MySQL按照权重从高到低排序。优先级我们可以这样定义：

• 权重10：内容完全等于关键词的条目（最高优先级）
• 权重5：关键词出现在内容开头的条目（比如content LIKE '关键词%'）
• 权重3：关键词出现在内容中间的条目（既不是完全匹配，也不是开头/末尾）
• 权重1：关键词出现在内容末尾的条目（最低优先级，也就是你现在默认的情况）

具体SQL查询示例 #

假设你的数据表叫search_results，存储内容的字段是content，PHP中获取的关键词变量是$keyword（一定要注意SQL注入防护，下面会讲），对应的SQL语句可以这么写：

SELECT id, content
FROM search_results
WHERE content LIKE ?
ORDER BY 
    CASE
        WHEN content = ? THEN 10
        WHEN content LIKE CONCAT(?, '%') THEN 5
        WHEN content LIKE CONCAT('%', ?, '%') AND content NOT LIKE CONCAT('%', ?) THEN 3
        WHEN content LIKE CONCAT('%', ?) THEN 1
        ELSE 0
    END DESC,
    id ASC; -- 权重相同时可以按ID或者其他字段排序

PHP代码实现（以PDO为例，避免SQL注入） #

在PHP中，一定要用预处理语句来绑定参数，防止SQL注入风险：

// 假设已经建立了PDO连接
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'username', 'password');

$keyword = $_GET['search']; // 从GET获取搜索关键词
$likeKeyword = "%{$keyword}%";

// 准备预处理语句
$stmt = $pdo->prepare("
    SELECT id, content
    FROM search_results
    WHERE content LIKE ?
    ORDER BY 
        CASE
            WHEN content = ? THEN 10
            WHEN content LIKE CONCAT(?, '%') THEN 5
            WHEN content LIKE CONCAT('%', ?, '%') AND content NOT LIKE CONCAT('%', ?) THEN 3
            WHEN content LIKE CONCAT('%', ?) THEN 1
            ELSE 0
        END DESC,
        id ASC
");

// 绑定参数（注意参数顺序要和SQL中的?对应）
$stmt->bindParam(1, $likeKeyword, PDO::PARAM_STR);
$stmt->bindParam(2, $keyword, PDO::PARAM_STR);
$stmt->bindParam(3, $keyword, PDO::PARAM_STR);
$stmt->bindParam(4, $keyword, PDO::PARAM_STR);
$stmt->bindParam(5, $keyword, PDO::PARAM_STR);
$stmt->bindParam(6, $keyword, PDO::PARAM_STR);

// 执行查询并获取结果
$stmt->execute();
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

额外优化建议 #

• 如果你的数据量比较大，LIKE '%xxx%'会导致全表扫描，性能下降。这种情况下可以考虑使用MySQL的全文索引，结合MATCH AGAINST来实现更高效的搜索和排序，全文索引会自动计算匹配度得分，你可以直接按得分排序。
• 可以根据实际需求调整权重值，比如如果想要中间匹配比末尾优先级高很多，可以把权重设为8而不是3，灵活调整即可。

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