MySQL 按“最佳匹配”排序

Question

我有一个表，其中包含单词和一个输入字段，可以使用实时搜索来搜索该表。目前，我使用以下查询来搜索表：

 SELECT word FROM words WHERE word LIKE '%searchstring%' ORDER BY word ASC

有没有办法对结果进行排序，以便在单词开头找到字符串的结果排在最前面，而在单词后面出现的字符串排在最后？

示例：搜索“ hab ”当前返回

1. 一个 字母表
2. 习惯
3. 康复

但我喜欢这样：

1. hab it（首先因为’hab’是开始）
2. alp hab et （第二个因为’hab’在单词的中间）
3. re hab （最后因为 ‘hab’ 在词尾）

或者至少这样：

1. hab it（首先因为’hab’是开始）
2. re hab （第二个，因为“hab”从第三个字母开始）
3. alp hab et （最后因为“hab”开始最晚，在第四个字母）

如果有人可以帮助我解决这个问题，那就太好了！

原文由 eevaa 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

要做到这一点（开始单词，在单词中间，结束单词），尝试这样的事情：

 SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY
  CASE
    WHEN word LIKE 'searchstring%' THEN 1
    WHEN word LIKE '%searchstring' THEN 3
    ELSE 2
  END

第二种方式（匹配字符串的位置），使用 LOCATE 函数：

 SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY LOCATE('searchstring', word)

您可能还需要一个决胜局，例如，多个单词以 hab 开头。为此，我建议：

 SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY <whatever>, word

在多个以 hab 开头的单词的情况下，以 hab 开头的单词将被组合在一起并按字母顺序排序。

原文由 Ed Gibbs 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

试试这个方法：

 SELECT word
FROM words
WHERE word LIKE '%searchstring%'
ORDER BY CASE WHEN word = 'searchstring' THEN 0
              WHEN word LIKE 'searchstring%' THEN 1
              WHEN word LIKE '%searchstring%' THEN 2
              WHEN word LIKE '%searchstring' THEN 3
              ELSE 4
         END, word ASC

原文由 Robert 发布，翻译遵循 CC BY-SA 3.0 许可协议