MySQL全文布尔模式搜索返回太多结果 [英] MySQL Fulltext Boolean Mode search returns too many results

问题描述

我的全文搜索查询存在问题，我已经遍历了所有可以找到的所有论坛和主题，但仍然有这个问题.

I'm having an issue with my fulltext search query and I've pretty much gone through all the forums and threads I could find but I'm still having this issue.

我正在使用MySQL全文布尔模式搜索来返回基于两列(艺术家名称和曲目标题)的匹配行.用户可以在搜索栏中输入他们想要的任何短语，并且结果应该是仅在列的EITHER中包含搜索查询所有部分的行.

I am using the MySQL Fulltext Boolean Mode search to return matching rows based on two columns (artist name and track title). The user can enter any phrase they wish into the search bar, and the results are supposed to be only rows that contain ALL parts of the search query in EITHER of the columns.

这是我到目前为止所拥有的查询，它适用于大多数查询，但对于某些查询，它返回的结果过于宽松，我不确定为什么.

This is the query I have so far, and it works with most queries but for some it return results too loosely and I'm not sure why.

SELECT * FROM tracks WHERE MATCH(artist, title) AGAINST('+paul +van +dyk ' IN BOOLEAN MODE)

但是，此查询返回包含Paul的行，但不包含"Van"或"Dyk".再次，我希望查询仅返回包含艺术家"或曲目名称"列中所有关键字的行.

This query however, returns rows containing Paul, without the 'Van' or 'Dyk'. Again, I want the query to return only rows that contain ALL of the keywords in EITHER the Artist or Track Name column.

预先感谢

推荐答案

要增强布尔模式下结果的排序，可以使用以下命令:

To enhance sorting of the results in boolean mode you can use the following:

SELECT column_names, MATCH (text) AGAINST ('word1 word2 word3')
AS col1 FROM table1
WHERE MATCH (text) AGAINST ('+word1 +word2 +word3' in boolean mode) 
order by col1 desc;

使用第一 MATCH() ，我们会在非布尔搜索模式下获得分数(更具特色). 第二 MATCH() 确保我们只获得想要的结果(全部3个字).

因此您的查询将变为:

SELECT *, MATCH (artist, title) AGAINST ('paul van dyk')
    AS score FROM tracks
    WHERE MATCH (artist, title) 
    AGAINST ('+paul +van +dyk' in boolean mode) 
    order by score desc;

希望；您现在将获得更好的结果.

Hopefully; you will get better results now.

如果有效或无效；请让我知道.

If it works or do not work; please let me know.

这篇关于MySQL全文布尔模式搜索返回太多结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！