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屋!