MySQL 搜索查询 - “保留"不工作 [英] MySQL Search Query - "Keep" not working

问题描述

我对我们使用了一段时间的跟随查询有点困惑，以下有效

搜索火"的查询

SELECT product FROM product_descriptions WHERE MATCH(product) AGAINST('+fire*' IN BOOLEAN MODE) LIMIT 5

退货

Fire Storage 31020 S1 E消防储存 31020 S1 K消防储存 31021 S1 E消防储存 31021 S1 K消防储存 31022 S1 E

但是以下搜索查询保持"

SELECT product FROM product_descriptions WHERE MATCH(product) AGAINST('+keep*' IN BOOLEAN MODE) LIMIT 5

未返回任何结果，但我们在表中有产品，例如防火门保持关闭标志"、保持清除标志"

到目前为止我发现的唯一不起作用的词是工作保持"

以下搜索适用于steel"、door"，因为我认为这可能是双字符的问题

解决方案

Keep is a stop-word

您可能想改用 Lucene.它的停用词列表要小得多，而且您可以完全删除它们:
如何指示 Lucene 中的 StandardAnalyzer 不删除停用词?

或者你可以添加

ft_stopword_file = ""

或链接一个空文件empty_stopwords.txt"到您的 .cnf/my.ini 文件，重新启动 mysql 引擎并重建索引:

除此之外，如果您还没有准备好，请将最小单词降低到 3 (ft_min_word_len=3) 以便能够搜索 3 个字母的单词.

较短的词 (3,2) 会显着增加查询时间，尤其是在全文索引列字段很大的情况下.

Im a little confused with the follow query we have been using for a while, The following works

Search query for "fire"

SELECT product FROM product_descriptions WHERE MATCH(product) AGAINST('+fire*' IN BOOLEAN MODE) LIMIT 5

Returns

Fire Storage 31020 S1 E
Fire Storage 31020 S1 K
Fire Storage 31021 S1 E
Fire Storage 31021 S1 K
Fire Storage 31022 S1 E

However the following search query for "keep"

SELECT product FROM product_descriptions WHERE MATCH(product) AGAINST('+keep*' IN BOOLEAN MODE) LIMIT 5

Returns no results however we have products in table such as "Fire Door Keep Shut Sign", "Keep Clear Sign"

The only word that doesnt work i've found so far is the work "keep"

The following searches work "steel", "door" as i thought it maybe a problem with double characters

解决方案

Keep is a stop-word

Wikipedia:

In computing, stop words are words which are filtered out prior to, or after, processing of natural language data (text). [...] Some tools specifically avoid removing them to support phrase search. Any group of words can be chosen as the stop words for a given purpose. For some search machines, these are some of the most common, short function words, such as the, is, at, which, and on. In this case, stop words can cause problems when searching for phrases that include them, particularly in names such as 'The Who', 'The The', or 'Take That'. Other search engines remove some of the most common words—including lexical words, such as "want"—from a query in order to improve performance.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html

You can find the actually used list in the storage/myisam/ft_static.c file.

This fact is stated in http://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

You might want to use Lucene instead. It's list of stop-words is much smaller, plus you can remove them completely:
How to instruct StandardAnalyzer in Lucene to not to remove stop words?

Or you can add

ft_stopword_file = ""

or link an empty file "empty_stopwords.txt" to your .cnf/my.ini file, restart the mysql engine and rebuild the indices:

I addition to that, if you haven't allready, lower the min word to 3 (ft_min_word_len=3) to be able to search for 3 letter words.

Shorter words (3,2) will increase the query time dramatically, especially if the fulltext indexed column fields are large.

这篇关于MySQL 搜索查询 - “保留"不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！