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.




 选择*从曲目中匹配(艺术家,标题)反对('+ 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全文布尔模式搜索返回太多结果