MySQL查询按最佳匹配排序，除UNION外是否有更优实现方案？

当然有更高效简洁的方式！你不需要用UNION做两次查询，只需要通过排序权重控制，在单个查询里就能实现精准的最佳匹配排序，性能和可读性都更好。

核心思路 #

我们可以通过ORDER BY子句结合条件判断和字符串定位函数，给不同匹配程度的结果分配不同的优先级：

1. 最高优先级：字段以搜索字符串开头的记录（比如01234匹配012%）
2. 次优先级：字段包含搜索字符串但不是开头的记录，再按匹配位置的先后排序（位置越靠前，匹配度越高）
3. 可选补充：最后按字段本身的字典序排序，保证结果的稳定性

具体SQL语句 #

针对你的场景，搜索字符串为012时，SQL可以这么写：

SELECT *
FROM my_table
WHERE field LIKE '%012%'
ORDER BY
    -- 第一优先级：以012开头的排最前面
    CASE WHEN field LIKE '012%' THEN 0 ELSE 1 END,
    -- 第二优先级：匹配的起始位置越靠前，排序越靠前
    LOCATE('012', field),
    -- 第三优先级：按字段本身的字典序排序（可选，根据需求调整）
    field;

为什么比UNION更好？ #

• 性能更优：只需要一次表扫描，而UNION会执行两次查询再合并结果，数据量大时差异明显
• 逻辑更清晰：排序规则一目了然，后续要调整匹配优先级（比如增加对特定前缀的权重）非常方便
• 避免重复问题：UNION会自动去重，但如果你的表中存在既满足开头匹配又满足包含匹配的记录（虽然这种情况很少），单个查询不会产生重复，也不需要额外的去重开销

额外建议 #

注意直接拼接字符串到SQL里存在SQL注入风险，建议使用预处理语句（比如PDO或MySQLi的参数绑定）来传递搜索参数，示例如下（以PDO为例）：

$str = '012';
$stmt = $pdo->prepare("
    SELECT *
    FROM my_table
    WHERE field LIKE CONCAT('%', :str, '%')
    ORDER BY
        CASE WHEN field LIKE CONCAT(:str, '%') THEN 0 ELSE 1 END,
        LOCATE(:str, field),
        field
");
$stmt->bindParam(':str', $str);
$stmt->execute();

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