使用 Microsoft SQL Server 进行全文索引文件

引言

本文介绍 SQL Server 2000 (2005) 的全文搜索功能。它是一种易于使用、速度极快且可扩展的解决方案，可用于索引和搜索各种文档内容。例如，Word、Excel、Adobe 可移植文档格式 (PDF) 和 HTML 文件。

必备组件

要完成本示例，您需要 Microsoft SQL Server 2000 服务器（至少）访问权限、拥有 DB Owner 权限的数据库，当然还有客户端工具。

创建表

为了索引存储在数据库表中的文件，我们需要创建两个表字段。在第一个字段中，我们将以二进制格式存储文档的内容；在第二个字段中，我们将存储文件的扩展名，例如“.doc”或“.xls”。
最好存储文件的完整名称和大小信息，因为在实际情况中您可能会用到它们，但全文索引并不需要它们。

存储文件大小是可选的，因为您可以使用 DataLength 函数查询它们，但这可能需要很长时间，比从字段中读取这些值要慢得多。

因此，我们的 create table 脚本可以是这样的

CREATE TABLE [dbo].[Doc] (
 [ID]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
 [Extension] [varchar] (10) NOT NULL ,
 [Content] [image] NOT NULL ,
 [FileSize] [int] NOT NULL ,
 [FileName] [nvarchar] (500) NOT NULL ,
 [Stamp] [timestamp] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Doc] WITH NOCHECK ADD 
 CONSTRAINT [PK_Doc] PRIMARY KEY  CLUSTERED 
 (
  [ID]
 )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Doc] ADD 
 CONSTRAINT [DF_Doc_ID] DEFAULT (newid()) FOR [ID]
GO

一个简单的想法是使用唯一标识符类型作为文档表的主键。如果您是 Web 开发人员，这在很多情况下会很有用。您可能希望在制作下载页面时在 URL（查询字符串）中使用此 ID，并且您不希望用户通过简单地增加 URL 末尾的参数来有机会在不看到所有广告的情况下下载所有文档。

请注意，脚本已创建主键。这始终非常重要，因为它可以保证您无法将具有相同 ID 的两个记录插入数据库（表是记录的集合，您无法区分具有相同值的记录，除了使用游标），并且良好的聚集索引可以真正提高 SELECT 语句的性能。

此外，表中还有一个时间戳字段——称为 stamp。这对于我们来说是必需的，稍后我们将讨论它。

创建全文索引

我认为没有人能凭记忆说出全文索引创建语句的正确格式。因此，我们将通过 Microsoft SQL Server Enterprise Manager 来创建此索引。
首先选择您的服务器，然后选择您的数据库。选择表，然后在右侧搜索您创建的表（Doc）。
右键单击该表后，从“Full-Text Index table”菜单中选择“Define Full-Text indexing on a table”子菜单。

完成这些操作后，将出现一个向导。
在“Select an index step”中，选择您的主键。稍后，当我们查询表时，我们将从全文搜索引擎中获取这个（唯一的）索引值作为结果。

在“Select Table Columns”步骤中，勾选“Content”列，并在该行的文档类型列中选择扩展名字段。然后转到另一行，因为只有在您更改选择后，“Next”按钮才会激活。

在此步骤中，我们向服务器提供了可索引的列，并定义了每种文档的类型。

在“Select a Catalog”步骤中，您可以创建一个新的全文目录或选择一个现有的目录。我建议您为这些数据创建一个新目录，因为随着文档数量的增加，收集的索引可能会非常大。

在“Select or Create Population Schedules”中，您可以安排目录或表的增量和完全填充。在这种情况下，您可以将其留空，因为我们将使用 SQL Server 的更改跟踪功能。这意味着，我们在发生 insert 或 update 后立即告知 SQL Server 更新其全文目录。
在实际情况中，通常建议每周或每天进行一次完全填充，每天或更频繁地进行增量填充，但这取决于您的服务器利用率。
原因是更改跟踪和增量更新功能无法识别是否有人使用了 WriteText 或 UpdateText 语句。

在设计表时，我们在其中放入了一个时间戳字段。时间戳字段对于增量索引更新是必需的，没有该字段，所有增量更新都将执行完全更新。全文索引引擎可以发现此属性更改上的更新。这是因为全文引擎不会注意到 WriteText 和 UpdateText 语句。这些语句不会像正常的 Insert 和 Update 那样更新时间戳字段。

通过连续单击“Next”按钮几次，然后单击“Finish”，完成向导。

定义了全文索引后，我们必须打开更改跟踪功能。我们可以通过在 Enterprise Manager 中右键单击表名，然后从 Full-Text Index Table 菜单中选择 Change Tracking，再选择“Update index in background”来完成此操作。从那时起，全文索引引擎将监视我们的表以获取更改，并在必要时更新其索引。

我们索引创建的一个有趣结果

if (select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')) <> 1 
exec sp_fulltext_database N'enable' 
GO

if not exists (select * from dbo.sysfulltextcatalogs where name = N'Doc')
exec sp_fulltext_catalog N'Doc', N'create' 
GO

exec sp_fulltext_table N'[dbo].[Doc]', N'create', N'Doc', N'PK_Doc'
GO

exec sp_fulltext_column N'[dbo].[Doc]', N'Content', N'add', 1033, N'Extension' 
GO

exec sp_fulltext_table N'[dbo].[Doc]', N'activate'  
GO

测试

现在我们插入一些记录，然后创建一些 select 查询。让我们在 Query Analyzer 中运行此 insert 脚本。

INSERT INTO [DOC] ([Extension], [Content], [FileSize], [FileName]) 
    VALUES ('.txt', 'Hello John! It''s me: Garfield!', 30, 'Cartoon1.txt')

INSERT INTO [Doc] ([Extension], [Content], [FileSize], [FileName]) 
    VALUES ('.txt', 'Oh my god!', 30, 'Shout.txt')

INSERT INTO [DOC] ([Extension], [Content], [FileSize], [FileName]) 
    VALUES ('.txt', 'NOWAN's web site: http://www.nowan.hu/', 30, 'nowan.txt')

这些是简单的 insert，您可以自行检查。插入记录后，您可以运行 SELECT 语句来检查表。

SELECT * FROM [Doc]

全文搜索

要创建全文查询，您需要了解以下语句

• Contains
• FreeText
• ContainsTable
• FreeTextTable

前两个语句有两个参数。第一个是列名，第二个是要搜索的字符串。这些函数返回布尔值。

后两个语句更有趣。这些函数返回具有两列的表：Key 和 Rank。这意味着我们可以获取被搜索记录的唯一 ID，还可以获取命中概率（Rank）。

SELECT * FROM ContainsTable([doc], Content, '"nowan"')

containstable 和 freetexttable 的另一个优点是，您可以提供复杂的表达式作为搜索字符串。例如，您可以使用“OR”和“AND”逻辑项。

当然，这些语句的结果表可以与实际表连接。所以，如果我们想从原始表中获取原始数据行，我们可以使用如下的 select 语句。

SELECT Doc.* FROM [Doc] 
    INNER JOIN ContainsTable([doc], Content, '"nowan"') AS FT 
    ON Doc.ID = FT.[Key]

可索引文件类型

SQL Server 通常可以从文本文件和 Microsoft Office 文件创建索引。此类型列表可以通过为旧版索引服务设计的 iFilters 进行扩展。您也可以从 Adobe 获取 iFilter 来索引 PDF 文件。

匈牙利语版本

您可以在 这里找到本文的匈牙利语版本。