如何实现学校名称关键词匹配并按匹配度、价格升序排序的SQL查询

现有基础信息 #

• 数据库名：school
• 存储表名：schoollist（注意原有代码中误写为schoolist，会报表不存在错误）
• 表字段：学校名称（sc_name）、地址（sch_address）、价格（price）
• 表示例数据：

|-------name---------|----address----|----price--|
| Wright school      | Oslo, Norway  |  870      |
| metro  school      | Oslo, Norway  |  880      |
| unit   school      | Oslo, Norway  |  670      |
| Oslo   school      | Oslo, Norway  |  540      |
| Wright oslo school | Oslo, Norway  |  510      |

需求规则 #

搜索关键词Wright Oslo时，需满足：

1. 仅返回学校名称匹配关键词的结果，地址字段不参与匹配
2. 同时匹配所有关键词的结果优先展示，同匹配度下价格更低的排在前面
3. 仅匹配部分关键词的结果按匹配度从高到低展示，同匹配度下价格更低的排在前面
4. 不匹配核心关键词的结果全部排除，最终期望返回结果：

|-------name---------|----address----|----price--|
| Wright Oslo school | Oslo, Norway  |  510      |
| Wright school      | Oslo, Norway  |  870      |

原有代码问题 #

• 变量覆盖问题：foreach循环中每次重写$sql变量，最终仅保留最后一个关键词的查询逻辑，无法实现多关键词联合匹配排序
• 匹配范围错误：全文检索同时查询名称和地址字段，会返回仅地址匹配关键词的无关结果
• 过滤逻辑缺失：没有设置核心关键词必选规则，导致仅匹配非核心关键词的结果（如仅匹配Oslo的Oslo school）被返回
• 排序逻辑错误：仅判断单个关键词的后缀匹配，没有统计多关键词命中数量，也未加入价格升序规则
• 语法错误：MySQL全文检索AGAINST语法不支持%通配符，和LIKE语法混用会报错
• 安全隐患：直接拼接用户输入到SQL中，即使做了转义也存在SQL注入风险，推荐使用预处理语句

正确实现代码 #

核心逻辑：将搜索词按空格拆分后，第一个词作为核心关键词，必须在名称中匹配，其余词作为加分项；排序时统计所有关键词的总命中数，命中数越高排序越靠前，同命中数按价格升序排列。

if(isset($_POST['searchbtn']) ){
    $searchContent = trim($_POST['search']);
    if(empty($searchContent)){
        header("Location: index.php");
        exit();
    }
    // 拆分搜索关键词，过滤空值（避免多个连续空格生成空关键词）
    $searchArray = array_filter(explode(" ", $searchContent), function($item){
        return !empty(trim($item));
    });
    $searchArray = array_values($searchArray); // 重置数组下标
    $bindParams = [];
    $types = '';
    // 拼接必选WHERE条件：必须匹配第一个核心关键词
    $firstWord = mysqli_real_escape_string($db, strtolower($searchArray[0]));
    $whereStr = "LOWER(sc_name) LIKE ?";
    $bindParams[] = "%{$firstWord}%";
    $types .= 's';
    // 拼接排序统计逻辑：每个关键词命中加1分
    $sortCond = [];
    foreach ($searchArray as $word) {
        $word = mysqli_real_escape_string($db, strtolower($word));
        $sortCond[] = "CASE WHEN LOWER(sc_name) LIKE ? THEN 1 ELSE 0 END";
        $bindParams[] = "%{$word}%";
        $types .= 's';
    }
    $sortStr = "(" . implode(" + ", $sortCond) . ") DESC, price ASC";
    // 预处理SQL，避免注入
    $sql = "SELECT sc_name, sch_address, price FROM `schoollist` WHERE {$whereStr} ORDER BY {$sortStr}";
    $stmt = mysqli_prepare($db, $sql);
    mysqli_stmt_bind_param($stmt, $types, ...$bindParams);
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    // 后续遍历$result输出结果即可
}

效果验证 #

使用关键词Wright Oslo查询时：

1. WHERE条件过滤掉所有名称不含wright的学校（metro school、unit school、Oslo school），仅剩余2条符合基础条件的数据
2. 排序计算命中分：Wright oslo school同时命中wright和oslo得2分，Wright school仅命中wright得1分
3. 同分数下按价格升序排列，最终返回顺序和期望完全一致

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