SQL Server 2008 R2 全文搜索

本章介绍在SQL Server 2008 R2 下的全文索引，它能够对数据中的字符类型列（如varchar、text等类型）进行索引，并通过索引实现全文搜索查询。首先对比简单介绍一下常规索引和全文索引的区别，如下图：

OK，下面我们就利用SQL Server 提供的存储过程来建立一个全文索引，具体步骤为：

（1）启动数据库的全文处理功能（sp_fulltext_datebase）；
（2）建立全文目录（sp_fulltext_catalog）；
（3）在全文目录中注册需要全文索引的表（sp_fulltext_table）；
（4）指出表中需要全文检索的列名（sp_fulltext_column）
（5）为表创建全文索引（sp_fulltext_table）；
（6）填充全文索引（sp_fulltext_catalog）。

接下来用实例一步步演示：

 

SQL Server 全文搜索

      
      

       
       use
       
        DBFullText


       
       --
       
        建表
       
       

       
       create
       
        
       
       table
       
        Student
(
    id 
       
       int
       
        
       
       primary
       
        
       
       key
       
        
       
       identity
       
       (
       
       1
       
       ,
       
       1
       
       ) 
       
       not
       
        
       
       null
       
       ,
    name 
       
       nvarchar
       
       (
       
       30
       
       ) 
       
       null
       
       ,
    familyAddress 
       
       nvarchar
       
       (
       
       100
       
       ) 
       
       null
       
       ,
    schoolAddress 
       
       nvarchar
       
       (
       
       100
       
       ) 
       
       null
       
       
)


       
       --
       
       插入一些数据
       
       

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       SAVEA
       
       '
       
       ,
       
       '
       
       187 Suffolk Ln.
       
       '
       
       ,
       
       '
       
       1900 Oak St.
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       VICTE
       
       '
       
       ,
       
       '
       
       2, rue du Commerce
       
       '
       
       ,
       
       '
       
       23 Tsawassen Blvd.
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       BLONP
       
       '
       
       ,
       
       '
       
       24, place Kléber
       
       '
       
       ,
       
       '
       
       25, rue Lauriston
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       PARIS
       
       '
       
       ,
       
       '
       
       265, boulevard Charonne
       
       '
       
       ,
       
       '
       
       2732 Baker Blvd.
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       OLDWO
       
       '
       
       ,
       
       '
       
       2743 Bering St.
       
       '
       
       ,
       
       '
       
       2817 Milton Dr.
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       WANDK
       
       '
       
       ,
       
       '
       
       Adenauerallee 900
       
       '
       
       ,
       
       '
       
       Åkergatan 24
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       BERGS
       
       '
       
       ,
       
       '
       
       Berguvsvägen  8
       
       '
       
       ,
       
       '
       
       Carrera 22 con Ave. Carlos Soublette #8-35
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       SANTG
       
       '
       
       ,
       
       '
       
       Carrera 52 con Ave. Bolívar #65-98 Llano Largo
       
       '
       
       ,
       
       '
       
       Erling Skakkes gate 78
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       OCEAN
       
       '
       
       ,
       
       '
       
       Grenzacherweg 237
       
       '
       
       ,
       
       '
       
       Jardim das rosas n. 32
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       LEHMS
       
       '
       
       ,
       
       '
       
       Sierras de Granada 9993
       
       '
       
       ,
       
       '
       
       Via Ludovico il Moro 22
       
       '
       
       )

       
       insert
       
        
       
       into
       
        Student 
       
       values
       
       (
       
       '
       
       SIMOB
       
       '
       
       ,
       
       '
       
       South House 300 Queensbridge
       
       '
       
       ,
       
       '
       
       P.O. Box 555
       
       '
       
       )



       
       --
       
       检查 DBFullText 是否支持全文索引，如果不支持全文索引，则使用sp_fulltext_datebase打开该功能
       
       

       
       if
       
        (
       
       select
       
        
       
       databaseproperty
       
        (
       
       '
       
       DBFullText
       
       '
       
       ,
       
       '
       
       IsFulltextEnables
       
       '
       
       ))
       
       =
       
       0
       
       

       
       exec
       
        sp_fulltext_database 
       
       '
       
       enable
       
       '
       
       



       
       --
       
       创建全文目录（‘全文目录名‘，’创建/删除‘） 
       
       

       
       exec
       
        sp_fulltext_catalog 
       
       '
       
       FT_Student
       
       '
       
       ,
       
       '
       
       create
       
       '
       
       



       
       --
       
       创建全文索引（‘表名‘，’创建/删除‘，’名称‘，’约束名‘），这里的约束名就是建表的时候自动生成的主键约束
       
       

       
       exec
       
        sp_fulltext_table 
       
       '
       
       Student
       
       '
       
       ,
       
       '
       
       create
       
       '
       
       ,
       
       '
       
       FT_Student
       
       '
       
       ,
       
       '
       
       PK_Student
       
       '
       
       



       
       --
       
       设置全文索引列（‘表名‘，’列名‘，’添加/删除‘） 
       
       

       
       exec
       
        sp_fulltext_column 
       
       '
       
       Student
       
       '
       
       ,
       
       '
       
       familyAddress
       
       '
       
       ,
       
       '
       
       add
       
       '
       
       

       
       exec
       
        sp_fulltext_column 
       
       '
       
       Student
       
       '
       
       ,
       
       '
       
       schoolAddress
       
       '
       
       ,
       
       '
       
       add
       
       '
       
       



       
       --
       
       激活表的全文检索能力，也就是在全文目录中注册该表
       
       

       
       exec
       
        sp_fulltext_table 
       
       '
       
       Student
       
       '
       
       ,
       
       '
       
       activate
       
       '
       
       



       
       --
       
       填充全文索引目录
       
       

       
       exec
       
        sp_fulltext_catalog 
       
       '
       
       FT_Student
       
       '
       
       ,
       
       '
       
       start_full
       
       '
       
       



       
       --
       
       测试一下
       
       

       
       select
       
        
       
       *
       
        
       
       from
       
        Student 
       
       where
       
        
       
       contains
       
        (familyAddress,
       
       '
       
       South
       
       '
       
       )

       
       select
       
        
       
       *
       
        
       
       from
       
        Student 
       
       where
       
        
       
       contains
       
        (schoolAddress,
       
       '
       
       Dr.
       
       '
       
       )
      
      

 

OK，现在全文搜索的SQL Server代码部分已经做完。其实在SQL Server 2008 R2里面，完全不用上面那么多代码去操作存储过程创建全文索引，

它自带的有 ‘ Full Text Catalogs’，我们完全可以手动建立一个全文索引（实现过程当然是调用存储过程，只不过在这里省略了），首先找到目录

Storage -> Full Text Catalogs，然后创建一个新的Full Text Catalog，如下图

 

 

 然后打开它，选择要进行全文索引的列，如下图

 

 

保存之后，即可做如上述的全文搜索。

 

这里主要讲全文搜索里面用到的 Contains 函数，摘录别人的文章，感觉写的好，拿来分享一下。 假设有表 students，其中的 address 是全文本检索的列。   1 . 查询住址在北京的学生 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' beijing ' ) remark: beijing是一个单词，要用单引号括起来。 网管下载dl.bitscn.com 2 . 查询住址在河北省的学生 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' "HEIBEI province" ' ) remark: HEBEI province是一个词组，在单引号里还要用双引号括起来。 中国网管论坛bbs.bitsCN.com 3 . 查询住址在河北省或北京的学生 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' "HEIBEI province" OR beijing ' ) remark: 可以指定逻辑操作符(包括 AND ， AND NOT ， OR )。 网管下载dl.bitscn.com 4 . 查询有 ' 南京路 ' 字样的地址 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' nanjing NEAR road ' ) remark: 上面的查询将返回包含 ' nanjing road ' ， ' nanjing east road ' ， ' nanjing west road ' 等字样的地址。 A NEAR B，就表示条件： A 靠近 B。 网管网www.bitscn.com 5 . 查询以 ' 湖 ' 开头的地址 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' "hu*" ' ) remark: 上面的查询将返回包含 ' hubei ' ， ' hunan ' 等字样的地址。 记住是 * ，不是 % 。 网管论坛bbs_bitsCN_com 6 . 类似加权的查询 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' ISABOUT (city weight (.8), county wright (.4)) ' ) remark: ISABOUT 是这种查询的关键字，weight 指定了一个介于 0 ~ 1之间的数，类似系数(我的理解)。表示不同条件有不同的侧重。 网管下载dl.bitscn.com 7 . 单词的多态查询 SELECT student_id,student_name FROM students WHERE CONTAINS ( address, ' FORMSOF (INFLECTIONAL,street) ' ) remark: 查询将返回包含 ' street ' ， ' streets ' 等字样的地址。 对于动词将返回它的不同的时态，如：dry，将返回 dry，dried，drying 等等。