MySQL按“最佳匹配"排序

问题描述

我有一个包含单词的表和一个输入字段，可使用实时搜索来搜索该表.当前，我使用以下查询来搜索表:

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?

示例:搜索" hab "当前会返回

An example: searching for 'hab' currently returns

1. a 字母
2. h 位
3. r ehab

1. a lphabet
2. h abit
3. r ehab

但是我想这样:

1. hab (首先是因为"hab"是开头)
2. alp hab 等(第二个原因是'hab'在单词的中间)
3. re hab (最后一个原因是"hab"在单词的结尾)

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

或至少通过这种方式:

1. hab (首先是因为"hab"是开头)
2. re hab (第二个，因为"hab"从第三个字母开始)
3. alp hab 等(最后是因为"hab"最晚从第四个字母开始)

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

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

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

推荐答案

要以第一方式(从单词的开头，单词的中间，单词的结尾)进行操作，请尝试以下操作:

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

要以第二种方式(匹配字符串的位置)进行操作，请使用 LOCATE函数:

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

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

例如，如果多个单词以hab开头，您可能还需要平局.为此，我建议:

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

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

对于以hab开头的多个单词，将以hab开头的单词组合在一起并按字母顺序排序.

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

这篇关于MySQL按“最佳匹配"排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！