全文搜索查询不返回SQL Server中的结果 [英] Full text search query not returning result in SQL Server

问题描述

我为我的表创建了一个目录和全文索引：

I have created a catalog and fulltext index for my table as :

create fulltext catalog testsearch

CREATE FULLTEXT INDEX ON test_master
    (name)
    KEY INDEX PK_test_master
    ON testsearch

但是当我尝试检索值为

But when I try to retrieve values as

select * from test_master where contains(name, ''product'')

查询只是返回表''test_master''的列，但没有填充任何行..我也没有收到任何错误..我已经尝试再次填充我的索引，我确信我有一个值''产品' '在列''名称''.. plz帮助我

The query just returns me the columns of the table ''test_master'' but no rows are populated.. I''m not getting any error either.. i have tried populating my index again and i am sure that i have a value ''product'' in column ''name''.. plz help me

推荐答案

只要您在桌面上管理全文索引

As long as you''ve managed fulltext index on your table

USE [20121212141159365]
DECLARE @searchWord [nvarchar](128)
SET @searchWord = N''POINT (-97.990837 26.159519)''
SELECT [GeogCol2]
  FROM [20121212141159365].[qanda].[fulltextSearchable] WHERE
	CONTAINS([GeogCol2],@searchWord)
GO

否则你会收到这样的错误：



Msg 7601，Level 16，State 2，Line 6

不能在表或索引视图上使用CONTAINS或FREETEXT谓词[20121212141159365 ]。[qanda]。[fulltextSearchable]因为它不是全文索引。





如果索引不是设置正确，你可能会遇到这样的消息：



消息9928，等级16，状态1，行1

计算列''GeogCol2''不能用于全文搜索，因为它是不确定的或不精确的非持久计算列。



或完全不同的东西：



消息7653，等级16，状态1，行1

''--------''不是有效的索引强制执行全文搜索键。全文搜索键必须是唯一的，不可为空的单列索引，该索引不脱机，未在非确定性或不精确的非持久计算列上定义，没有过滤器，最大大小为900字节。为全文键选择另一个索引。



如果竞争是由代码中的拼写错误确定的，您甚至可能会遇到如下错误：< br $>


消息7609，等级17，状态5，行1

未安装全文搜索，或者不能使用全文组件加载。



跳过以上所有内容，快速验证是否安装了FULL-TEXT SEARCH：

Otherwise you''ll get an error like this:

Msg 7601, Level 16, State 2, Line 6
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view [20121212141159365].[qanda].[fulltextSearchable] because it is not full-text indexed.

[edit]
If the index is not set up correctly, you''ll probably have encountered a message like this:

Msg 9928, Level 16, State 1, Line 1
Computed column ''GeogCol2'' cannot be used for full-text search because it is nondeterministic or imprecise nonpersisted computed column.

Or something completely different:

Msg 7653, Level 16, State 1, Line 1
''--------'' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defined on a non-deterministic or imprecise nonpersisted computed column, does not have a filter, and has maximum size of 900 bytes. Choose another index for the full-text key.

And if competancy is determined by something like a spelling error in the code, you might even encounter an error like:

Msg 7609, Level 17, State 5, Line 1
Full-Text Search is not installed, or a full-text component cannot be loaded.

Skip all of the above to verify quickly whether FULL-TEXT SEARCH is even installed:

EXEC sp_fulltext_service @action='upgrade_option', @value=1;
GO

除了我在这里使用的例子是oogusbay，因为几何类型是根据定义，不是全文可搜索的，使用此表格是有利于理解所以我会在这里继续：

Except for the fact that the example I use here is oogusbay because geometry type is not full-text searchable by definition, using this form is expedient to understanding so I''ll continue it here:

USE [20121212141159365]
CREATE FULLTEXT CATALOG rwood_131_FTCat
CREATE UNIQUE INDEX ui_131 ON [20121212141159365].[qanda].[fulltextSearchable](idx) 
CREATE FULLTEXT INDEX ON [20121212141159365].[qanda].[fulltextSearchable](
						[GeogCol2]
						)
						rwood_131_FTCat
						KEY INDEX ui_131 ON rwood_131_FTCat
						WITH CHANGE_TRACKING AUTO 
SELECT * FROM sys.fulltext_indexes
[edit end]

最后一点应该可以解决问题。也就是说，如果你设法在开始时安装FULL-TEXT SEARCH。

That last bit should do the trick. That is, if you''ve managed to install FULL-TEXT SEARCH in the beginning.

这篇关于全文搜索查询不返回SQL Server中的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！