SQL Server全文包含某些字词不起作用 [英] SQL Server fulltext contains not working in some words

问题描述

我有一个全文搜索表.通常效果很好.但是，在某些情况下，它会失败.

I have a fulltext search table. It generally works well. But, in some cases it failed.

例如；当我搜索' "red*" NEAR "color*" '时，它可以正常工作.

For example; when I search for ' "red*" NEAR "color*" ' it works properly.

但是，如果我搜索'"the*" NEAR "red*"'，它将失败.它对于以the

However, if I search for '"the*" NEAR "red*"' it fails. It didn't work for any terms which starts with the

-- working case
SELECT *
FROM MyTable
WHERE CONTAINS(MyColumn, ' "red*" NEAR "color*" ')

-- failed case
SELECT *
FROM MyTable
WHERE CONTAINS(MyColumn, ' "the*" NEAR "red*" ')

有人知道为什么吗?

推荐答案

您遇到的是全文搜索中停用词(也称为干扰词)的概念.大多数全文搜索引擎都会列出正在搜索中的非常常见的单词，因为它们不够具体，不足以使其具有相关性.

What you are experiencing is the concept of stopwords (aka noise words) in full text search. Most full text search engines have a list of very common words that are being ingored in the search, because they are not specific enough to be considered relevant.

在SQL Server中，您可以通过此查询显示已配置的英语停用词列表(我敢打赌'the'是该列表的一部分):

In SQL Server you can display the list of configured stopwords for English language with this query (and I bet that 'the' is part of that list):

select * from sys.fulltext_stopwords where language_id = 1033

通过

You can manage the stop words, by creating a custom list.

也可以禁用停用词，尽管我不建议这样做:

It is also possible to disable stopwords, although I would not recommend that:

alter fulltext index on mytable set stoplist = off

这篇关于SQL Server全文包含某些字词不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！