問題描述
我已經多次重溫這個問題，但我從來沒有真正找到正確的答案.
I have revisited this problem many times, and I have never really found a proper answer.
是否可以執行按相關性返回 ACTUAL 準確排序結果的 MySQL 搜索?
我正在嘗試創建一個 ajax 搜索表單，該表單在用戶輸入輸入字段時提出建議，但沒有找到僅使用純 MySQL 查詢的合適解決方案.我知道有可用的搜索服務器，例如 ElasticSearch，我想知道如何僅使用原始 MySQL 查詢來執行此操作.
I am trying to create an ajax search form which makes suggestions as the user types into an input field, and have found no decent solution to this using only pure MySQL queries. I know there are search servers available such as ElasticSearch, I want to know how to do it with a raw MySQL query only.
我有一張學校科目表.少于 1200 行，這永遠不會改變.讓我們執行一個基本的 FULLTEXT 搜索，用戶開始輸入Bio".
I have a table of school subjects. There are less than 1200 rows and this will never change. Let's perform a basic FULLTEXT search where the user starts typing "Bio".
查詢(Bio...") - 全文布爾模式
SELECT name, MATCH(name) AGAINST('bio*' IN BOOLEAN MODE) AS relevance
FROM subjects
WHERE MATCH(name) AGAINST('bio*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 10
結果
name | relevance
--------------------------------------------------------
Biomechanics, Biomaterials and Prosthetics | 1
Applied Biology | 1
Behavioural Biology | 1
Cell Biology | 1
Applied Cell Biology | 1
Developmental/Reproductive Biology | 1
Developmental Biology | 1
Reproductive Biology | 1
Environmental Biology | 1
Marine/Freshwater Biology | 1
為了顯示這些結果有多糟糕，這里與一個簡單的 LIKE 查詢進行了比較，該查詢顯示了所有未顯示的更相關的結果:
To show how bad these results are, here is a comparison with a simple LIKE query which shows all the more relevant results which weren't shown:
查詢(Bio...")- LIKE
SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name
結果
name | relevance
--------------------------------------------------------
Bio-organic Chemistry | 1
Biochemical Engineering | 1
Biodiversity | 1
Bioengineering | 1
Biogeography | 1
Biological Chemistry | 1
Biological Sciences | 1
Biology | 1
Biomechanics, Biomaterials and Prosthetics | 1
Biometry | 1
并且您已經看到有多少主題沒有被建議，即使這些主題更有可能是用戶想要的.
And already you see how many subjects are not suggested, even though these are more likely what the user will be looking for.
然而，使用 LIKE 的問題是如何像 FULLTEXT 那樣跨多個單詞和單詞中間進行搜索.
The problem with using LIKE however, is how to search across multiple words and in the middle of words like FULLTEXT does.
我想要實現的基本排序是這樣的:
The basic ordering I would want to implement is something like:
- 以搜索詞開頭的第一個詞
- 以搜索詞開頭的第二個詞
- 詞不在詞首的詞
- 如果沒有進一步的相關性，所有內容通常按字母順序排列
所以我的問題是，如何通過跨多個單詞的 MySQL 搜索為用戶獲得合理排序的建議列表?
So my question is, how does one go about getting a sensibly sorted list of suggestions for the user with a MySQL search across multiple words?
推薦答案
你可以使用字符串函數，例如:
You could use string functions, such as:
select id, name
from subjects
where name like concat('%', @search, '%')
order by
name like concat(@search, '%') desc,
ifnull(nullif(instr(name, concat(' ', @search)), 0), 99999),
ifnull(nullif(instr(name, @search), 0), 99999),
name;
這會為您提供包含@search 的所有條目.首先是開頭的那些，然后是空格之后的那些，然后是出現的位置，然后是字母.
This gets you all entries containing @search. First those that have it at the beginning, then those that have it after a blank, then by the position of the occurrence, then alphabetical.
name like concat(@search, '%') desc 順便用了 MySQL 的布爾邏輯.1 = 真，0 = 假，所以按降序排列會首先得到真.
name like concat(@search, '%') desc uses MySQL's boolean logic by the way. 1 = true, 0 = false, so ordering this descending gives you true first.
SQL 小提琴:http://sqlfiddle.com/#!9/c6321a/1
這篇關于MySQL - 如何獲得具有準確相關性的搜索結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！