详解Sql2005 全文索引创建及应用(2)

14.7.2  使用FREETEXT搜索

14.7.3  使用CONTAINSTABLE搜索

14.7.4  使用FREETEXTTABLE搜索

14.7.5  搜索image字段

14.8  2005新增：与全文索引相关的T-SQL语句

14.8.1  创建全文目录

14.8.2  更改全文目录属性

14.8.3  创建全文索引

14.8.4  更改全文索引属性

14.8.5  删除全文索引

14.8.6  删除全文目录

14.9  小结

 

 

14.7.1.2  简单词的搜索方式

简单词的搜索方式就是搜索一个或多个特定的词或短语。

例一、搜索文章表的标题中含有“上海”的记录，其代码如下：

SELECT * FROM 文章

         WHERE CONTAINS(标题,'上海')

例二、搜索文章表的内容中含有“上海”或“广州”的记录，其代码如下：

SELECT * FROM 文章

         WHERE CONTAINS(内容,' "上海" OR "广州"')

注意例二与例一的不同，在CONTAINS谓词的第二个参数里，将“”上海” OR “广州””做为一个字符串传递给CONTAINS。使用以下代码将会出错：

SELECT * FROM 文章

         WHERE CONTAINS(内容,' 上海‘ OR  ’广州')

14.7.1.3  派生词的搜索方式

派生词的搜索方式主要用在英文当中，因为英文单词中含有现在式、过去式、将来式、单复数等不同的形式，使派生词的搜索方式可以将字段中包括该单词的所有形式的记录都搜索出来。例如使用派生词方式搜索包含单词download的记录，则会把包含download、downloading等派生词的记录都搜索出来。

例三、搜索文章表中内容中含有download及其派生词的记录，其代码如下：

SELECT * FROM 文章

         WHERE CONTAINS(内容,'FORMSOF(INFLECTIONAL,download)')

注意CONTAINS谓词的不同处，运行结果如图14.19所示。

 

图14.19 运行结果

在图14.19中可以看到只查询到一条记录，而并未将download的派生词也搜索进来，这是因为从一开始设置全文索引时，所采用的是数据库默认的断字符语言，即简体中文，而在中文中没有派生词，所以使用派生词方式搜索数据是起不到其作用的。此时可以通过修改全文索引的断字符语言来达到搜索效果。修改方式如下：

（1）右击全文索引所在的数据表，在弹出的快捷菜单中选择【全文索引】à【属性】弹出如图14.12所示【全文索引属性】对话框。

（2）在【全文索引属性】对话框里选择【列】选项，弹出如图14.20所示对话框，修改【内容】字段的【断字符语言】为“English”。

 

图14.20 修改断字符语言

（3）单击【确定】按钮完成操作。

再次运行例三中的代码，其运行结果如图14.21所示。

 

图14.21 正确的运行结果

14.7.1.4  前缀词的搜索方式

前缀词的搜索方式主要也是用在搜索英文中，例如搜索以“do”开头的单词，则可以将doctor、document、download等单词都搜索出来。

例四、搜索文章表中内容中含有“do”开头的单词的记录，其代码如下：

SELECT * FROM 文章

         WHERE CONTAINS(内容,' "do*" ')

此方式有点类似与like子句的通配符方式，但只能使用“*”，并且只能放在英文字母之后，如“*do”、“*do*”都是错误的表达方式。

14.7.1.5  加权词的搜索方式

当以多个字符串作为搜索条件搜索记录时，可以为不同的字符串加上一个加权值，这个加权值是介于0和1之间的数值，加权值越高的记录排在越前面。

例五、搜索文章表中内容中含有“download”、“上海”、“山西”的记录，并为不同的条件加上加权值，其代码如下：

SELECT * FROM 文章

         WHERE CONTAINS(内容,

                   'ISABOUT ("download" weight(0.9),

                   "上海"  weight(0.6),

                   "山西"  weight(0.5))')

或

SELECT * FROM 文章

         WHERE CONTAINS(内容,

                   'ISABOUT (download weight(0.9),

                   上海  weight(0.6),

                   山西  weight(0.5))')

事实上在该SELECT语句的返回结果集里，并没有按加权值的大小来排序，因为WEIGHT不影响CONTAINS查询的结果，只会影响CONTAINSTABLE查询中的排序。

说明：使用本例时最好将“内容”字段的“断字符语言”改回简体中文，否则会影响中文的搜索结果。

14.7.1.6  邻近词的搜索方式

邻近词的搜索方式可以搜索记录中位置相近的两个字符，例如要搜索文章标题为“教育部拟取消教师资格终身制实行定期认证”的文章，完整的标题名记不清楚了，只记得“教育部”和“资格”，则可以以邻近词的搜索方式进行查询。

例六、搜索文章内容中与教育部表示相关的记录，其代码如下：

SELECT * FROM 文章

         WHERE CONTAINS(内容,' "教育部" NEAR "表示"')

14.7.2  使用FREETEXT搜索

FREETEXT搜索方式与CONTAINS搜索方式相比，其搜索结果表现都十分不精确，因为FREETEXT的搜索方式是将一个句子中的每个单字拆分开进行搜索的。例如：如果使用CONTAINS搜索方式搜索条件为“教育部”的记录，那么搜索出来的将是记录里包含“教育部”三个字符串的记录。如果使用FREETEXT搜索方式搜索条件为“教育部”的记录，那么搜索出来的将是记录里包含“教”或“育”或“部”的记录。如果搜索的是英文字符串“SQL Server 2005”，则拆分为“SQL”、“Server”和“2005”来进行搜索，只要满足其中一个条件都算搜索成功。FREETEXT的语法代码为：

FREETEXT ( { column_name | (column_list) | * }

          , 'freetext_string' [ , LANGUAGE language_term ] )

其中：

l  column_name：为字段名。

l  column_list：为字段列表。

l  *：代码所有字段。

l  'freetext_string'：搜索的字符串。

l  LANGUAGE language_term：用于单词断字、词干分析、同义词库查询以及干扰词删除的特定的语言。

FREETEXT的搜索方式没有CONTAINS复杂，下面举例说明：

例七、用CONTAINS和FREETEXT两种方式来搜索“教育部”字符串，其代码如下：

SELECT * FROM 文章

         WHERE FREETEXT(内容,'教育部')

SELECT * FROM 文章

         WHERE CONTAINS (内容,'教育部')

其运行结果如图14.22所示，使用CONTAINS查询结果要比使用FREETEXT的查询结果记录数要多得多。

 

图14.22 CONTAINS和FREETEXT的查询结果

14.7.3  使用CONTAINSTABLE搜索

CONTAINSTABLE函数与CONTAINS谓词类似，其可以返回符合条件的多条记录，但是返回的记录是作为数据表出现在SELECT语句的FROM子句中。这个数据表里只包含两个字段：一个字段名为“KEY”，该字段显示的是全文索引的唯一索引键的内容，也就是图14.6所示界面中所创建的索引列；另一个字段名为“RANK”，该字段是排名值字段，其排名值是由系统依查询符合的程度自动生成的。CONTAINSTABLE的语法代码为：

CONTAINSTABLE ( table , { column_name | (column_list ) | * } , ' < contains_search_condition > '

     [ , LANGUAGE language_term]

  [ ,top_n_by_rank ]

   )

< contains_search_condition > ::=

    { < simple_term >

    | < prefix_term >

    | < generation_term >

    | < proximity_term >

    |  < weighted_term >

    }

    | { ( < contains_search_condition > )

    { { AND | & } | { AND NOT | &! } | { OR | | } }

     < contains_search_condition > [ ...n ]

}

< simple_term > ::=

          word | " phrase "

< prefix term > ::=

     { "word * " | "phrase *" }

< generation_term > ::=

     FORMSOF ( { INFLECTIONAL | THESAURUS } , < simple_term > [ ,...n ] )

< proximity_term > ::=

     { < simple_term > | < prefix_term > }

     { { NEAR | ~ } { < simple_term > | < prefix_term > } } [ ...n ]

< weighted_term > ::=

     ISABOUT

        ( { {

  < simple_term >

  | < prefix_term >

  | < generation_term >

  | < proximity_term >

  }

   [ WEIGHT ( weight_value ) ]

   } [ ,...n ]

        )

从以上代码可以看得出，CONTAINSTABLE与CONTAINS的搜索条件一样分为五类，其形式也几乎一样，只是增加了table和top_n_by_rank两个参数：

l  table：全文索引所在的数据表名。

l  top_n_by_rank：返回的记录数，相当于SELECT语句中的top n。

下面举几个例子说明CONTAINSTABLE与CONTAINS在用法上不同的地方。

例八、查看文章表中内容含有“教育部”的记录的编号，其代码如下：

SELECT * FROM

         CONTAINSTABLE(文章,内容,'教育部') as table1

在本例中可以看到，CONTAINSTABLE返回的结果是作为数据表的形式出现在FROM子句中。其运行结果如图14.23所示，查询的结果也就是CONTAINSTABLE返回的数据表的结果，只有两个字段。

 

图14.23 例八的运行结果

例九、查看文章表中内容含有“教育部”和“表示”的前十条记录，并按查询符合程度排序，其代码如下：

SELECT * FROM 文章 JOIN

         CONTAINSTABLE(文章,内容,' "教育部" NEAR "表示" ',10) as table1

         ON 文章.编号= table1.[KEY]

         ORDER BY table1.RANK DESC

其运行结果如图14.24所示，在本例中，将CONTAINSTABLE函数返回的数据表与文章表JOIN起来进行联合查询，就好像一个真实的数据表一样。由于KEY是关键字，所以在本例当中用方括号将KEY括起来。

 

图14.24 例九的运行结果

例十、搜索文章表中内容中含有“download”、“上海”、“山西”的记录，并为不同的条件加上加权值，然后按权值排序。其代码如下：

SELECT * FROM 文章 JOIN

         CONTAINSTABLE(文章,内容,

                   'ISABOUT ("download" weight(0.9),

                   "上海"  weight(0.6),

                   "山西"  weight(0.1))') AS TABLE1

         ON 文章.编号 = TABLE1.[KEY]

         ORDER BY TABLE1.RANK DESC

其运行结果如图14.25所示，RANK字段是依符合程度生成的数据再加上权值后的结果。

 

图14.25 例十的运行结果

14.7.4  使用FREETEXTTABLE搜索

与CONTAINSTABLE一样，FREETEXTTABLE函数也是返回拥有KEY和RANK两个字段的表，该表可以和数据库中的数据表一样使用。FREETEXTTABLE的语法代码如下所示：

FREETEXTTABLE (table , { column_name | (column_list) | * }

          , 'freetext_string'

     [ ,LANGUAGE language_term ]

     [ ,top_n_by_rank ] )

由以上代码可以看出FREETEXTTABLE函数与FREETEXT谓词的语法代码相似，只是多了table和top_n_by_rank两个参数。

例十一、查看文章表中“内容”字段含有“教育部”的前十条记录，其代码如下：

SELECT 文章.内容,TABLE1.* FROM 文章JOIN

         FREETEXTTABLE(文章,内容,'教育部',8) AS TABLE1

         ON 文章.编号= TABLE1.[KEY]

运行结果如图14.26所示：

 

图14.26 例十一的运行结果

14.7.5  搜索image字段

在SQL Server 2005的image类型的字段里，不仅仅是可以存储图形文件，还可以存储如可执行文件、音乐文件、文本文件等众多文件类型。SQL Server 2005支持对存储在image类型的字段里的一些文件的内容进行全文搜索，但在创建全文索引时必须指明存储在image字段里的文件的类型。如图14.7所示。

SQL Server 2005支持对存储在image中的纯文本文件、网页文件、Word文件、Excel文件和PowerPoint文件的内容进行查询，其扩展名字段必须分别为txt、htm、doc、xls和ppt。全文索引创建完毕后，对image字段里的文件内容进行查询的方法与其他字段的查询方法是一样的：

例十二、查询存储在文章表的文件字段里的内容包含“数据库”的文件，其代码如下：

SELECT 编号,标题,文件,扩展名 FROM 文章

         WHERE CONTAINS(文件,'数据库')

14.8  2005新增：与全文索引相关的T-SQL语句

在SQL Server 2005以前的版本中，创建与管理全文目录、全文索引主要是使用存储过程来完成。从SQL Server 2005开始新增加了一些与全文索引相关的T-SQL语句，可以用来创建与管理全文目录和全文索引。

14.8.1  创建全文目录

创建全文目录的T-SQL语句为：

CREATE FULLTEXT CATALOG catalog_name

     [ON FILEGROUP filegroup ]

     [IN PATH 'rootpath']

     [WITH <catalog_option>]

     [AS DEFAULT]

     [AUTHORIZATION owner_name ]

<catalog_option>::=

     ACCENT_SENSITIVITY = {ON|OFF}

其中参数说明如下：

l  catalog_name：全文目录名称。

l  ON FILEGROUP filegroup：包含全文目录的文件组名。

l  IN PATH 'rootpath'：全文目录的路径。

l  AS DEFAULT：指定该全文目录为默认目录。

l  AUTHORIZATION owner_name：将全文目录的所有者设为数据库用户名或角色的名称。

l  ACCENT_SENSITIVITY：设置该全文目录的全文索引是否区分重音。

例十三、在Northwind数据库中创建一个名为“TSQL全文目录”全文目录，其代码如下：

CREATE FULLTEXT CATALOG TSQL全文目录

         ON FILEGROUP [PRIMARY]

         IN PATH 'E:"book"SQL Server 2005大全"数据库"第十四章"运行后数据库'

         AS DEFAULT

14.8.2  更改全文目录属性

创建完全文目录之后，如果发现其设置有不如意之处，可以用T-SQL语句对其进行修改。更改全文目录属性的T-SQL语句代码如下：

ALTER FULLTEXT CATALOG catalog_name

{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]

| REORGANIZE

| AS DEFAULT

}

其中参数说明如下：

l  catalog_name：全文目录名称

l  REBUILD：重新生成全文目录。

l  WITH ACCENT_SENSITIVITY：设置该全文目录的全文索引是否区分重音。

l  REORGANIZE：执行主合并索引以便将在索引过程中创建的各个较小的索引合并成一个大索引。

l  AS DEFAULT：指定该全文目录为默认目录。

例十四、重新生成“TSQL全文目录”，其代码如下：

ALTER FULLTEXT CATALOG TSQL全文目录

         REBUILD

14.8.3  创建全文索引

有了全文目录后，可以在全文目录里创建全文索引。一个全文目录里可以包含多个全文索引，但一个全文索引只能属于一个全文目录。每个数据表只能有一个全文索引。创建全文索引的T-SQL语句代码如下：

CREATE FULLTEXT INDEX ON table_name

     [(column_name [TYPE COLUMN type_column_name]

          [LANGUAGE language_term] [,...n])]

     KEY INDEX index_name

          [ON fulltext_catalog_name]

     [WITH

          {CHANGE_TRACKING {MANUAL | AUTO | OFF [, NO POPULATION]}}

     ]

其中参数说明如下：

l  table_name：数据表名。

l  column_name：全文索引中包括的一列或多列的名称。只能对类型为char、varchar、nchar、nvarchar、text、ntext、image、xml和varbinary的列进行全文索引。

l  TYPE COLUMN type_column_name：用于存储column_name的文档类型的数据表中的列名。

l  LANGUAGE language_term：存储在column_name中的数据所用的语言。

l  KEY INDEX index_name：数据表中唯一键索引的字段名。

l  ON fulltext_catalog_name：全文目录名。

l  MANUAL：指定是使用SQL Server代理还是手动传播跟踪日志。

l  AUTO：当关联的数据表中修改了数据时，SQL Server自动更新全文索引。

l  OFF[,NO POPULATION]：不保留对索引数据的更改列表。

例十五、为“文章”表的“标题”、“内容”和“文件”三个字段创建全文索引，其代码如下：

CREATE FULLTEXT INDEX

         ON 文章(标题,内容,文件 TYPE COLUMN 扩展名)

         KEY INDEX PK_文章

         ON TSQL全文目录

注意：由于在前面的章节里已经为“文章”表创建了全文索引，因此在运行本例之前要先把原来创建的全文索引删除。

14.8.4  更改全文索引属性

创建完全文索引之后，如果发现其设置有不如意之处，可以用T-SQL语句对其进行修改。更改全文索引属性的T-SQL语句代码如下：

ALTER FULLTEXT INDEX ON table_name

   { ENABLE

   | DISABLE

   | SET CHANGE_TRACKING { MANUAL | AUTO | OFF }

   | ADD ( column_name

     [ TYPE COLUMN type_column_name ]

     [ LANGUAGE language_term ] [,...n] )

     [ WITH NO POPULATION ]

   | DROP ( column_name [,...n] )

     [WITH NO POPULATION ]

   | START { FULL | INCREMENTAL | UPDATE } POPULATION

   | STOP POPULATION

   }

其中一些参数说明如下：

l  table_name：数据表名。

l  ENABLE | DISABLE：启用或禁用全文索引。

l  MANUAL：指定是使用SQL Server代理还是手动传播跟踪日志。

l  AUTO：当关联的数据表中修改了数据时，SQL Server自动更新全文索引。

l  OFF：不保留对索引数据的更改列表。

l  ADD：指定在全文索引中添加列。

l  WITH NO POPULATION：指定添加过删除全文索引列之后不填充全文索引。

l  DROP：指定在全文索引中删除列。

l  START……POPULATION：开始填充全文索引。

l  FULL：指定填充所有全文索引。

l  INCREMENTAL：指定对上次填充以来修改过的行进行填充。

l  UPDATE ：指定对自上次更新更改跟踪索引以来的所有插入、更新或删除进行处理。

l  STOP POPULATION：停止正在进行的填充。

例十六、禁用“文章”表的全文索引，其代码如下：

ALTER FULLTEXT INDEX ON 文章

         DISABLE

例十七、启用“文章”表的全文索引，其代码如下：

ALTER FULLTEXT INDEX ON 文章

         ENABLE

例十八、将“文章”表里的“文件”字段从全文索引里删除，其代码如下：

ALTER FULLTEXT INDEX ON 文章

         DROP (文件)

例十九、填充“文章”表的全文索引，其代码如下：

ALTER FULLTEXT INDEX ON 文章

         START FULL POPULATION

14.8.5  删除全文索引

删除全文索引的T-SQL的语法代码比较简单，如下所示：

DROP FULLTEXT INDEX ON table_name

例二十、删除“文章”表里的全文索引，其代码如下：

DROP FULLTEXT INDEX ON 文章

14.8.6  删除全文目录

删除全文目录的T-SQL的语法代码比较简单，如下所示：

DROP FULLTEXT CATALOG catalog_name

例二十一、删除“文章”表里的全文索引，其代码如下：

DROP FULLTEXT CATALOG TSQL全文目录

注意：要删除全文目录必须为空，即不能包含有全文索引。

14.9  小结

使用全文搜索可以快速、灵活地为存储在数据库中的文本数据的基于关键字的查询创建索引。与仅适用于字符模式的LIKE谓词不同，全文查询将根据特定语言的规则对词和短语进行操作，从而针对此数据执行语言搜索。

全文索引是由SQL Server FullText Search服务来维护的，必须选启动该服务才能使用全文索引。填充全文索引有三种方式：完全填充、增量填充和更改跟踪。

在全文索引中概念与术语比较多，如全文索引、全文目录、断字符、词干分析器、标记、筛选器、填充、干扰词等。了解怎么创建全文目录，怎么创建全文索引，怎么进行全文索引的填充，怎么使用调度让全文索引自动填充。

使用CONTAINS、FREETEXT两个谓词和CONTAINSTABLE、FREETEXTTABLE两个行集值函数可以用来进行全文搜索，其中CONTAINS和FREETEXT用在WHERE子句中，CONTAINSTABLE和FREETEXTTABLE用在FROM子句中。CONTAINS搜索有简单词、派生词、前缀词、加权词和邻近词五种搜索方式。FREETEXT只有一种搜索方式，但是其将一个句子中的每个单字拆分开进行搜索。

SQL Server 2005可以对存储在image类型字段里的文件进行全文搜索。其搜索的前提是必须要有一个字段指明image类型字段里存储的文件是什么类型。当为image类型字段设置好全文索引后，可以像其他字段一样地进行全文搜索。