MySQL的FULLTEXT查询问题 [英] MySQL FULLTEXT query issue

问题描述

我试图使用mysql FULLTEXT进行查询，但不幸的是，它返回的空结果甚至包含那些输入关键字。

表格：user_skills ：

  + ---- + -------------------------------------------- -  + 
 | id |技能| 
 + ---- + ---------------------------------------- ------ + 
 | 1 |舞蹈表演者，DJ，艺人，活动策划师| 
 | 2 |动画，相机操作员，电影方向| 
 | 3 | DJ | 
 | 4 |制图员| 
 | 5 |化妆师| 
 | 6 | DJ，音乐制作人| 
 + ---- + ---------------------------------------- ------ + 
  

索引：

查询：

  SELECT id，skills 
 FROM user_skills 
 WHERE（MATCH（skills）AGAINST（'+ dj'IN BOOLEAN MODE ））
  

在运行上述查询之后，没有一个 DJ 行正在返回。在表中有3行，其值为 dj 。

解决方案

您的搜索字词为short

有些词在全文搜索中被忽略：

太短暂被忽略。对于
InnoDB搜索索引，通过全文搜索找到的单词的默认最小长度为三个字符，对于MyISAM，则为四个字符。您可以在创建
索引之前通过设置配置选项来控制
截止点：innodb search
索引的innodb_ft_min_token_size配置选项，或者MyISAM的ft_min_word_len。

。

布尔全文搜索具有以下特征：

它们不使用50％的门槛。

它们不会按照相关性递减的顺序自动排序。
您可以从前面的查询结果中看到：具有
最高相关性的行是包含MySQL两次的行，但最后列出的是
，而不是第一行。 >

即使没有FULLTEXT索引，它们也可以工作，但以这种方式执行的搜索
会很慢。

使用最小和最大字长全文参数。

https://dev.mysql.com/doc/refman/5.6/zh/fulltext-natural-language.html

https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

I'm trying to query using mysql FULLTEXT, but unfortunately its returning empty results even the table contain those input keyword.

Table: user_skills:

+----+----------------------------------------------+
| id |                    skills                    |
+----+----------------------------------------------+
|  1 | Dance Performer,DJ,Entertainer,Event Planner |
|  2 | Animation,Camera Operator,Film Direction     |
|  3 | DJ                                           |
|  4 | Draftsman                                    |
|  5 | Makeup Artist                                |
|  6 | DJ,Music Producer                            |
+----+----------------------------------------------+  

Indexes:

Query:

SELECT id,skills 
FROM user_skills 
WHERE ( MATCH (skills) AGAINST ('+dj' IN BOOLEAN MODE))  

Here once I run the above query none of the DJ rows are returning. In the table there are 3 rows with is having the value dj.

解决方案

Your search term is to short

as in mysql doc

Some words are ignored in full-text searches:

Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is three characters for InnoDB search indexes, or four characters for MyISAM. You can control the cutoff by setting a configuration option before creating the index: innodb_ft_min_token_size configuration option for InnoDB search indexes, or ft_min_word_len for MyISAM.

.

Boolean full-text searches have these characteristics:

They do not use the 50% threshold.

They do not automatically sort rows in order of decreasing relevance. You can see this from the preceding query result: The row with the highest relevance is the one that contains "MySQL" twice, but it is listed last, not first.

They can work even without a FULLTEXT index, although a search executed in this fashion would be quite slow.

The minimum and maximum word length full-text parameters apply.

https://dev.mysql.com/doc/refman/5.6/en/fulltext-natural-language.html

https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

这篇关于MySQL的FULLTEXT查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！