如何调整SQL查询，按字符串长度或首字符相关性优先排序筛选位置数据？

先解决核心安全问题 #

你的原查询直接拼接$_GET["keyword"]到SQL语句中，存在严重的SQL注入风险，所有用户输入必须通过预处理语句绑定参数，不能直接拼接。下面的方案都会基于安全的预处理写法。

方案1：按字符串长度升序排序（优先展示短名称） #

短名称（如单独的城市名）通常更符合用户搜索需求，我们可以先按名称长度从小到大排序，再保留原有的名称升序作为次要排序规则：

// 用PDO实现预处理（推荐）
$keyword = "%{$_GET['keyword']}%";
$query = "
    SELECT ydzdn_terms.term_id, ydzdn_terms.name 
      FROM ydzdn_terms 
INNER JOIN ydzdn_term_taxonomy 
        ON ydzdn_terms.term_id = ydzdn_term_taxonomy.term_id 
     WHERE ydzdn_term_taxonomy.taxonomy = 'location' 
       AND ydzdn_terms.name LIKE ? 
  ORDER BY LENGTH(ydzdn_terms.name) ASC, ydzdn_terms.name ASC 
     LIMIT 0,5";

// 执行预处理语句
$stmt = $pdo->prepare($query);
$stmt->execute([$keyword]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

方案2：按首字符匹配相关性排序（优先展示以关键词开头的结果） #

如果希望优先展示以用户输入关键词开头的结果（比如输入"wake forest"时，先显示"wake forest, NC"，再显示包含关键词的其他结果），可以用CASE WHEN定义排序权重：

$keyword = $_GET['keyword'];
$startsWith = "{$keyword}%";
$contains = "%{$keyword}%";

$query = "
    SELECT ydzdn_terms.term_id, ydzdn_terms.name 
      FROM ydzdn_terms 
INNER JOIN ydzdn_term_taxonomy 
        ON ydzdn_terms.term_id = ydzdn_term_taxonomy.term_id 
     WHERE ydzdn_term_taxonomy.taxonomy = 'location' 
       AND ydzdn_terms.name LIKE ? 
  ORDER BY 
    CASE WHEN ydzdn_terms.name LIKE ? THEN 1 ELSE 2 END ASC,
    LENGTH(ydzdn_terms.name) ASC,
    ydzdn_terms.name ASC 
     LIMIT 0,5";

// 执行预处理语句
$stmt = $pdo->prepare($query);
$stmt->execute([$contains, $startsWith]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

两种方案的组合说明 #

• 方案1适合短名称优先级更高的场景，比如用户搜"wake forest"时，"Wake Forest"（纯城市名）会比"123 Wake Forest St"排在前面。
• 方案2进一步强化了相关性：先展示以关键词开头的结果，再按长度排序，兼顾了匹配精准度和短名称优先级。

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