MySQL MySQL按“最佳匹配”排序

MySQL order by "best match"

提问by eevaa

I have a table that contains words and an input field to search that table using a live search. Currently, I use the following query to search the table:

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

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

Is there a way to order the results so that the ones where the string is found at the beginning of the word come first and those where the string appears later in the word come last?

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

An example: searching for 'hab' currently returns

一个例子：搜索“ hab”当前返回

1. alphabet
2. habit
3. rehab

1. 一个字母表
2. ^ h升技
3. [REHAB

but I'd like it this way:

但我喜欢这样：

1. habit (first because 'hab' is the beginning)
2. alp habet (second because 'hab' is in the middle of the word)
3. re hab(last because 'hab' is at the end of the word)

1. habit（首先因为'hab'是开始）
2. alp habet（第二个是因为“hab”在单词中间）
3. re hab（最后因为'hab'在词的末尾）

or at least this way:

或者至少这样：

1. habit (first because 'hab' is the beginning)
2. re hab(second because 'hab' starts at the third letter)
3. alp habet (last because 'hab' starts latest, at the fourth letter)

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

Would be great if anyone could help me out with this!

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

回答by Ed Gibbs

To do it the first way (starts word, in the middle of the word, ends word), try something like this:

要使用第一种方式（单词开始，单词中间，单词结束），请尝试以下操作：

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

To do it the second way (position of the matched string), use the LOCATEfunction:

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

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

You may also want a tie-breaker in case, for example, more than one word starts with hab. To do that, I'd suggest:

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

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

In the case of multiple words starting with hab, the words starting with habwill be grouped together and sorted alphabetically.

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

回答by Robert

Try this way:

试试这个方法：

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

回答by spencer7593

You could use the INSTRfunction to return the starting position of the search string within the word,

您可以使用该INSTR函数返回单词中搜索字符串的起始位置，

 ORDER BY INSTR(word,searchstring)

To make the resultset more deterministic when the searchstring appears in the same position in two different words, add a second expression to the ORDER BY:

当搜索字符串出现在两个不同单词的相同位置时，要使结果集更具确定性，请向 ORDER BY 添加第二个表达式：

 ORDER BY INSTR(word,searchstring), word

(For example, searchstring habappears in second position of both chablisand shabby)

（例如，搜索字符串hab出现在两个的第二位置chablis和shabby）