我已多次重新审视这个问题,而且我从未真正找到合适的答案 .
Is it possible to perform a MySQL search which returns ACTUAL accurately sorted results by relevancy?
我正在尝试创建一个ajax搜索表单,它在用户输入到输入字段时提出建议,并且仅使用纯MySQL查询找不到合适的解决方案 . 我知道有可用的搜索服务器,例如ElasticSearch,我想知道如何只使用原始MySQL查询 .
我有一张学校科目表 . 行数不到1200行,这永远不会改变 . 让我们执行一个基本的FULLTEXT搜索,用户开始输入“Bio” .
Query ("Bio...") - FULLTEXT BOOLEAN MODE
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
Results
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
查询的比较,它显示了未显示的所有更相关的结果:
Query ("Bio...") - LIKE
SELECT id, name
WHERE name LIKE 'bio%'
ORDER BY name
Results
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
并且您已经看到有多少主题没有被建议,即使这些主题更可能是用户将要寻找的 .
然而,使用 LIKE
的问题是如何搜索多个单词并在 FULLTEXT
之类的单词中间进行搜索 .
我想要实现的基本顺序是:
-
以搜索词开头的第一个单词
-
以搜索词开头的第二个单词
-
术语不在单词开头的单词
-
如果没有进一步的相关性,一般都是字母顺序的
所以我的问题是,如何通过跨多个单词的MySQL搜索为用户获取明智的排序建议列表?
4 回答
您可以使用字符串函数,例如:
这将获取包含@search的所有条目 . 首先是那些在开始时拥有它的那些,然后是那些在空白之后拥有它,然后通过发生的位置,然后按字母顺序排列的那些 .
name like concat(@search, '%') desc
顺便使用MySQL的布尔逻辑 . 1 = true,0 = false,因此按顺序排序会先给出真实值 .SQL小提琴:http://sqlfiddle.com/#!9/c6321a/1
对于其他登陆的人(就像我一样):根据我的经验,为了获得最佳效果,您可以根据搜索词的数量使用条件 . 如果只有一个单词使用LIKE'%word%',否则使用布尔全文搜索,如下所示:
我根据您描述的顺序尝试了这个 .
http://sqlfiddle.com/#!9/6bffa/1
我想也许你甚至可能想要包括@src的出现次数Count the number of occurrences of a string in a VARCHAR field?
这是我使用上述答案组合得到的最佳结果:
确保在列上设置全文索引(如果您最终使用的话,则为多列)并使用
OPTIMIZE table_name
重置索引 .关于这个的最好的事情是如果你键入
Jo
,那么名字Jo
的人将排名高于John
,这正是你想要的!