FreeTextTable不返回记录

Question

我有一个平面表来执行全文搜索，即使是最简单的搜索也不起作用。我已经尝试了很多事情，我也得到了同样的结果。然而，令人惊讶的是，类似的全文搜索工作在同一数据库中的其他表上。

下面是我的表格结构，我还添加了全文索引细节，以供快速参考。

​

下面是我的查询的搜索结果，我尝试了多个没有任何运气的东西，只有当我从列中提供确切的值时，查询才能工作。

即使我在查询中使用一个列，即Fname，搜索结果也是一样的。

我可能错过了一些基本的东西，但我不知道那是什么东西。下面是它的创建表脚本。

CREATE TABLE [dbo].[SampleTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CID] [int] NOT NULL,
    [VID] [int] NOT NULL,
    [FID] [int] NOT NULL,
    [TID] [int] NOT NULL,
    [FName] [nvarchar](200) NOT NULL,
    [IName] [nvarchar](200) NOT NULL,
    [Contents] [nvarchar](max) NOT NULL,
    [LatestVersion] [int] NOT NULL,
 CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'climate.c','climate_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateTreeLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateGeneralLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gage.c','gage_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validateAquifer','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','lid_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidProc','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidGroup','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','link_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','conduit_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','pump_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','orifice_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','weir_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','node_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','divider_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'project.c','project_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'rdii.c','validateRdii','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'shape.c','shape_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'snow.c','snow_validateSnowmelt','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'subcatch.c','subcatch_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'table.c','table_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'transect.c','transect_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'climate.c','climate_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateTreeLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'flowrout.c','validateGeneralLayout','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gage.c','gage_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validateAquifer','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'gwater.c','gwater_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','lid_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidProc','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'lid.c','validateLidGroup','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','link_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','conduit_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','pump_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','orifice_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'link.c','weir_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','node_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'node.c','divider_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'project.c','project_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'rdii.c','validateRdii','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'shape.c','shape_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'snow.c','snow_validateSnowmelt','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'subcatch.c','subcatch_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'table.c','table_validate','',5);
insert into SampleTable (CID, VID, FID, TID, FName, IName, Contents, LatestVersion) values (-1,1,1,1,'transect.c','transect_validate','',5);

如果你需要更多的细节，请告诉我。

更新1:因此，进一步挖掘这个问题，我发现了类似于我的问题。

• server可自由导出的部分单词
• FreeTextTable对搜索不适用word进行排序

因此，如果正确地理解了这些URL，那么FreeTextTable就不会在一个单词上工作，这肯定不是真的，因为我在另一个表上使用了一个单词查询，而且它工作得很好。

​

Answer 1

您正在尝试执行FreeTextTable不支持的子字符串匹配。相反，在通配符中使用ContainsTable。

select * from ContainsTable(SampleTable, ([FName],[IName]), 'clima*')

此搜索将匹配以下字符串：

• “气候”
• “气候群”
• “诸如此类的气候”
• “诸如此类的气候”
• “攀援”
• “无聊的攀爬”

但这不符合：

• “X气候”

FYI FreeTextTable用于自然语言类型的搜索--同义词、单数/复数形式、共轭词是匹配的，搜索中只需要一个术语就可以匹配。ContainsTable匹配准确的单词(没有同义词等)，允许布尔运算符(和，OR，NOT)，通配符(*，?)支持，并且可以执行邻近搜索(word1在word2的N个字内)。