MySQL查询如何按最佳匹配排序获取结果？

嘿，你这个需求完全不用靠UNION来实现哦！用基于匹配优先级的排序方式会更简洁高效，还能精准控制结果的排序逻辑，比两次查询合并的UNION靠谱多了～

为什么原来的UNION方案不够好？ #

• UNION本质是两次查询后合并结果，会额外多一次表扫描，效率不如单次查询；
• 如果用UNION（不带ALL）会自动去重，要是你的表中有重复字段值，可能会丢失数据；
• 第二个SELECT返回的结果顺序完全依赖表的存储顺序，没法自定义排序规则，灵活性很差。

最优解法：单次查询+权重排序 #

核心思路是给不同匹配程度的记录分配不同的排序权重，通过ORDER BY精准控制优先级。针对你的需求，我们可以这样写SQL：

SELECT field
FROM my_table
WHERE field LIKE '%$str%'
ORDER BY
  -- 第一优先级：字段以搜索串开头的，排最前面
  CASE WHEN field LIKE CONCAT('$str', '%') THEN 1 ELSE 2 END,
  -- 第二优先级：搜索串在字段中出现的位置越靠前，排序越靠前
  LOCATE('$str', field),
  -- 第三优先级：位置相同时，按字段本身的字典序排序（保证结果稳定）
  field

针对你的例子的效果 #

当$str='012'时：

1. 01234满足LIKE '012%'，排序权重为1，直接排第一；
2. 001234和X01234中012的出现位置都是第2位（1-based索引），这时候按字段字典序排序，001234比X01234小，所以001234排第二，X01234排第三，完全符合你的期望！

进阶优化（可选） #

如果你希望纯数字的字段优先于带字母的字段，可以再加一层排序条件：

SELECT field
FROM my_table
WHERE field LIKE '%$str%'
ORDER BY
  CASE WHEN field LIKE CONCAT('$str', '%') THEN 1 ELSE 2 END,
  -- 纯数字字段排前面
  CASE WHEN field REGEXP '^[0-9]+$' THEN 1 ELSE 2 END,
  LOCATE('$str', field),
  field

这种写法只需要扫描一次表，效率更高，排序规则也完全可控，比UNION方案实用多啦～

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