PHP后端MySQL CASE排序语句无法执行问题排查

问题背景 #

我有一段可正常运行的PHP后端代码，通过POST获取serviceSelection参数，拆分关键词后构建UNION查询，分别匹配services表的service字段和service_sub_keywords字段并返回结果。但当我将第一个查询的ORDER BY逻辑改为CASE语句以优化结果排序时，该CASE语句并未生效，请问是否存在语法问题？

原有代码片段 #

$serviceInput = $_POST['serviceSelection'];
$words = explode(' ', $serviceInput); 
$serviceQuery = "(SELECT service FROM services WHERE";
$sql_1 = '';
foreach($words as $word) {
    $sql_1 .= " AND service LIKE '%{$word}%'"; 
}
$sql_1 = substr($sql_1, 4); 
$sql_1 .= " ORDER BY service ASC LIMIT 15) ";
$sql_2 = " UNION "; 
$sql_3 = '';
$sql_3 = "(SELECT service FROM services WHERE";
$sql_4 = '';
foreach($words as $word) {
    $sql_4 .= " AND service_sub_keywords LIKE '%{$word}%'"; 
}
$sql_4 = substr($sql_4, 4); 
$sql_4 .= " ORDER BY service ASC LIMIT 10) ";

$serviceQuery = $serviceQuery.$sql_1.$sql_2.$sql_3.$sql_4; //STRING EVERYTHING TOGETHER
$serviceQuery = $db->prepare($serviceQuery);
$serviceQuery->execute();

修改后的CASE语句部分 #

$serviceQuery = "(SELECT service FROM services WHERE";
$sql_1 = '';
foreach($words as $word) {
    $sql_1 .= " AND service LIKE '%{$word}%'"; 
}
$sql_1 = substr($sql_1, 4); 
$sql_1 .= " ORDER BY
              CASE
                WHEN service LIKE '{$word}%' THEN 1
                WHEN service LIKE '%{$word}' THEN 3
                ELSE 2
              END, service ASC) ";

核心问题分析 #

1. 变量作用域错误：CASE语句中的{$word}变量，在foreach循环结束后仅保留最后一个关键词，导致排序逻辑只对最后一个关键词生效，前面的关键词排序规则完全失效，看起来就像CASE语句没起作用。
2. 严重SQL注入风险：直接将用户输入拼接到SQL字符串中，即使调用了prepare，也只是形式上的预编译，无法阻止SQL注入攻击。

修复方案 #

修复后的完整代码 #

$serviceInput = $_POST['serviceSelection'];
$words = explode(' ', trim($serviceInput));
// 过滤拆分后产生的空关键词
$words = array_filter($words, function($w) {
    return !empty(trim($w));
});

// 构建第一个查询的WHERE条件与排序权重
$whereConditions1 = [];
$orderWeights = [];
$params = [];

foreach ($words as $index => $word) {
    $paramKey = ":word{$index}";
    // 使用参数绑定构建WHERE条件
    $whereConditions1[] = "service LIKE CONCAT('%', {$paramKey}, '%')";
    // 为每个关键词生成排序权重规则
    $orderWeights[] = "CASE
        WHEN service LIKE CONCAT({$paramKey}, '%') THEN 10
        WHEN service LIKE CONCAT('%', {$paramKey}) THEN 3
        ELSE 2
    END";
    $params[$paramKey] = $word;
}

$sql1 = "(SELECT service FROM services WHERE " . implode(' AND ', $whereConditions1) . "
          ORDER BY (" . implode(' + ', $orderWeights) . ") ASC, service ASC LIMIT 15)";

// 构建第二个查询
$whereConditions2 = [];
foreach ($words as $index => $word) {
    $paramKey = ":subword{$index}";
    $whereConditions2[] = "service_sub_keywords LIKE CONCAT('%', {$paramKey}, '%')";
    $params[$paramKey] = $word;
}

$sql2 = " UNION (SELECT service FROM services WHERE " . implode(' AND ', $whereConditions2) . "
          ORDER BY service ASC LIMIT 10)";

$serviceQuery = $sql1 . $sql2;
$stmt = $db->prepare($serviceQuery);
$stmt->execute($params);

关键修复点 #

• 修正排序逻辑：在foreach循环内为每个关键词生成独立的排序规则，通过implode(' + ', $orderWeights)将所有关键词的权重累加，确保所有关键词都参与排序计算。
• 彻底防护SQL注入：使用参数绑定传递用户输入，所有变量通过占位符替换，避免直接拼接SQL字符串。
• 优化排序权重：将前缀匹配的权重设为更高值（10），确保前缀匹配的结果优先展示，后缀匹配权重设为3，包含匹配权重设为2，排序逻辑更符合预期。
• 过滤空关键词：避免拆分后产生的空字符串导致无效SQL条件，提升查询稳定性。

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