MySQL全文搜索不能用于某些词，如'house'

问题描述

我已经在3个字段的一小部分记录上设置了全文索引（尝试了3个结合，并得到相同的结果），并且有些词语返回结果正常，但某些词语如'house ''和'澳大利亚'不会（有趣的是，'澳大利亚'和'家'）。

这似乎是奇怪的行为。如果我添加WITH QUERY EXPANSION，我会得到结果，但它们现在不是最相关的。

任何人都知道这是为什么？否则，我将不得不求助于使用LIKE搜索，并且我宁愿将相关性包括在内。

解决方案

它可能有以下几点：

• MySQL有一个默认的停用词列表，它不包含在全文搜索中 - 。 'house'和'australia'似乎不在该列表中，但您搜索的其他单词可能会受其影响。


• 任何超过50％行或者是3个字母或者更少的长度在数据库中也被认为是一个停用词。如果您只是在只有几行的桌子上测试全文搜索，那么50％的东西特别容易被发现。

如果您在Google搜索mysql停用词，您会发现更多关于它的信息，因为它是吸引大量用户的其中一项。

I've setup a full text index on a small selection of records across 3 fields (tried a combination of the 3 as well and had the same result), and some words return results fine, but certain ones such as 'house' and 'australia' don't (interestingly, 'australian' and 'home' do).

It seems like odd behaviour. If I add "WITH QUERY EXPANSION" I get results, but they aren't the most relevant now.

Anyone got any idea why this is? Otherwise I'm going to have to resort to using LIKE searches, and I'd prefer to have relevancy included.

解决方案

It could be a couple of things:

• MySQL has a default list of 'stop words' that aren't included in a full text search - http://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html . 'house' and 'australia' don't seem to be in that list, but other words you search for might be affected by it.
• Any word that appears in over 50% of the rows or is 3 letters or in less in length in the database is also considered a stop word. The 50% thing is particularly easy to be caught out by if you're just testing the full text search out on a table with only a few rows in.

If you search Google for "mysql stop words" you'll find a lot more about it, as it's one of those things that catches a lot of people out.

这篇关于MySQL全文搜索不能用于某些词，如'house'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！