MYsql FULLTEXT查询产生意想不到的排名;为什么？ [英] MYsql FULLTEXT query yields unexpected ranking; why?

问题描述

我正在尝试使用标签进行全文搜索，但它对我无法正常使用chek附图

查询是：

  SELECT *， 
 MATCH（tags）AGAINST（'tag3 tag6 tag4'）AS得分
从项目
 ORDER BY得分DESC 
  

为什么分数不在正确的顺序字段中排序？如果你检查第二行有我搜索的所有标签，而第一个字段没有tag3关键字。

我的意思是id字段的顺序应该是：5,1,2 .. etc和NOT 1,5,2..etc

我的错误在哪里？

然后我想先搜索标签字段，然后如果没有结果我想搜索相同的关键字作为FULLTEXT里面的描述字段，所以用户将搜索标签和描述如果标签不匹配，是否有可能在同一个查询或我需要两个分开的查询？

解决方案

在本文档中 http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html 说：对于非常小的表格，单词分布并没有充分反映它们的语义价值，这种模式有时会产生奇怪的结果。

如果您的商品表很小 - 例如一张样品表 - 您可能遇到了这个问题并且得到了一个奇怪的结果。 b
$ b

您可能希望尝试此查询 IN BOOLEAN MODE 以查看您的结果是否符合您的预测。试试这个。

  SELECT *，
 MATCH（tags）AGAINST（'tag3 tag6 tag4'IN BOOLEAN MODE）AS得分
从项目
 ORDER BY得分DESC 
  

布尔模式禁用单词分布排名。注意，你应该理解自然语言和布尔模式之间的区别，一旦你有一个体面大小的表，明智地选择使用哪一个。如果您正在寻找博客所拥有的标签类型，布尔可能是最好的选择。

i'm trying fulltext search with tags but it doesn't works properly for me chek attached image please

The query is:

 SELECT *, 
         MATCH(tags) AGAINST ('tag3 tag6 tag4') AS score 
    FROM items
ORDER BY score DESC

why does the score is not ordering in the right order fields? if you check the second row has all the tags i searched while the first field does not have tag3 keyword .

i mean id fields order should be : 5,1,2 .. etc and NOT 1,5,2..etc

where is my mistake?

then i would like to search first in tags field then if no results i would like to search the same keyword as FULLTEXT inside description field, so users will search both in tags and description if tags does not match , is it possible in the same query or i need two separated queries?

解决方案

In this document http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html it says "For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results."

If your items table is small -- a sample table, for example -- you are probably hitting this problem and getting a "bizarre" result.

You may wish to try this query IN BOOLEAN MODE to see if your results match your prediction. Try this.

    SELECT *, 
           MATCH(tags) AGAINST ('tag3 tag6 tag4' IN BOOLEAN MODE) AS score 
      FROM items
  ORDER BY score DESC

The Boolean mode disables the word-distribution ranking. Notice that you should understand the difference between natural-language and Boolean modes, and once you have a decently sized table, make a smart choice about which one to use. If you're searching for the kind of tags that blogs have in them, Boolean may be the way to go.

这篇关于MYsql FULLTEXT查询产生意想不到的排名;为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！