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

22 IT屋

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.

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)

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.

Thanks in advance

解决方案

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;

Using the first MATCH() we get the score in non-boolean search mode (more distinctive). The second MATCH() ensures we really get back only the results we want (with all 3 words).

So your query will become:

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



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



 选择*从曲目中匹配(艺术家,标题)反对('+ paul + van + dyk'在布尔模式下)
< / code>


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



预先感谢


解决方案

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



  SELECT列名,匹配(文本)反对('word1 word2 word3')
AS col1来自表1
匹配(文本)反对('+ word1 + word2 + word3'在布尔模式)
由col1 desc排序;


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



因此您的查询将变为:



  SELECT *,MATCH(艺术家,标题)AGAINST('paul van dyk')
从曲目
中获得的比分WHERE MATCH(艺术家,标题)
AGAINST(布尔模式下的'+ paul + van + dyk')
按分数描述排序;


希望;



如果有效或无效,您将获得更好的结果。请让我知道。


本文地址:IT屋 » MySQL全文布尔模式搜索返回太多结果