SQL SERVER 2005/2008全文索引实战讲解

在正式介绍SQL全文索引相关概念，先请各位跟着如下步骤实战一下：

   
   

    
    

     
     

      
      [c-sharp] 
      
      view plain
      
      copy
      
      

     
     
    
    
    
    
--新建一个测试数据库，并指定排序规则为Chinese_PRC_CI_AS  
CREATE DATABASE TESTFT COLLATE Chinese_PRC_CI_AS      
GO  
USE TESTFT  
GO  
--新建全文目录TestCL,并将其置为默认  
CREATE FULLTEXT CATALOG TestCL AS DEFAULT      
GO  
  
--新建测试表  
CREATE TABLE TB          
(  
    ID INT IDENTITY,  
    Title VARCHAR(100),  
    Body VARCHAR(1000),  
    CONSTRAINT PK_TB PRIMARY KEY CLUSTERED (ID)  
)  
GO  
  
--在TB表的title和body列上创建全文索引  
CREATE FULLTEXT INDEX ON TB (Title,Body) KEY INDEX PK_TB  
GO  
  
--插入两行演示数据  
INSERT TB(Title,Body) VALUES('艺术家评选','苍井空和小泽玛利亚是深受人民群众喜爱的著名艺术家')  
INSERT TB(Title,Body) VALUES('给苍老师的一封信','宁夏固原有一位网友给敬爱的苍老师写了一封信')  
GO  
   
   
 

OK，到目前为止，准备工作基本完成，下面开始查询

[c-sharp]  view plain copy

1. --例1：  
2. SELECT * FROM TB WHERE CONTAINS(*,'小')  
3. /* 
4. ID          Title                          Body 
5. ----------- ------------------------------ ------------------------------------------------------ 
6. (0 行受影响) 
7. */  
8. SELECT * FROM TB WHERE CONTAINS(*,'小泽')  
9. /* 
10. ID          Title                          Body 
11. ----------- ------------------------------ ------------------------------------------------------ 
12. 1           艺术家评选                     苍井空和小泽玛利亚是深受人民群众喜爱的著名艺术家 
13. (1 行受影响) 
14. */  
15. --例2：  
16. SELECT * FROM TB WHERE CONTAINS(*,'苍')  
17. /* 
18. ID          Title                          Body 
19. ----------- ------------------------------ ------------------------------------------------------ 
20. 1           艺术家评选                     苍井空和小泽玛利亚是深受人民群众喜爱的著名艺术家 
21. (1 行受影响) 
22. */  
23. SELECT * FROM TB WHERE CONTAINS(*,'苍老师')  
24. /* 
25. ID          Title                          Body 
26. ----------- ------------------------------ ------------------------------------------------------ 
27. 2           给苍老师的一封信               宁夏固原有一位网友给敬爱的苍老师写了一封信 
28. (1 行受影响) 
29. */  

 

看着上面的查询结果，是不是很奇怪，这和我们的预期完全不一样嘛，怎么会这样呢？

对于例1，这里涉及到全文索引的一个重要概念，干扰词(Noise Words)。
为了精简全文索引，Microsoft SQL Server 提供了一种机制，用来去掉那些经常出现但对搜索没有帮助的词。这些词称为“干扰词”或“终止词”。

如英文中的"a"、"and"和"the"等，中文中的"的"、"了"、"在"等，凭经验就知道这些词对于搜索意义不大。因此将他们排除在全文索引之外。

对于SQL 2005，干扰词存放在$SQL_Server_Install_Path/Microsoft SQL Server/MSSQL.1/MSSQL/FTDATA/ 目录中，每种语言对应一个文件。
对于SQL 2008，干扰词存放在系统表sys.fulltext_stopwords中，可通过下面的语句查看：

SQL code

   
   

    
    
SELECT stopword FROM sys.fulltext_stopwords WHERE language_id = 2052 --简体中文
--下面是部分结果：
/*
后
因
在
好
它
小    --请注意这里
已
并
很
我
*/
   
   

现在我们应该知道，例1中我们使用CONTAINS(*,'小')语句之所以查不到任何结果，是因为"小"字被系统当作干扰词，系统中没有存储关于"小"字的任何信息。

那么，这些干扰词我们可以修改么？答案是肯定的。对于SQL 2005，我们可以直接修改相应语言对应的干扰词文件；对于SQL 2008，我们可以用T-SQL语句来增加、删除干扰词。

----------------------------------------------------------------------------------------------------------

 

对于例2，和干扰词无关，是由于全文引擎断词不当造成的。这也是全文索引最为人诟病的一点。
在SQL 2008之前版本中，我们无法看到全文引擎到底是怎样断词的，但在SQL 2008中，我们可以通过查询系统表值函数sys.dm_fts_index_keywords
和sys.dm_fts_index_keywords_by_document来查看断词结果。

如对于第二条记录，我们可以这样看断词后的结果:

SQL code

   
   

    
    SELECT display_term 
FROM sys.dm_fts_index_keywords_by_document(DB_ID('TestFT'),OBJECT_ID('TB')) 
WHERE document_id=2 and column_id = 3 --全文键值为2 第3列
/*
display_term
---------------
宁夏
固
原有
位
网友
给
敬爱
苍老    --注意这里
师
写
封
信
END OF FILE
*/
   
   

根据上述结果，我们可以看到，全文引擎将"苍老"当成了一个词，所以我们用CONTAINS(*,'苍')查询不到这条记录。同理，我们用CONTAINS(*,'老师')也查不到这条记录。

对于每种断字符语言，断词结果是无法改变的。如果实在想要改变，只能通过微软公布的接口，自行编程修改相应的组件。

除了使用上面的两个表值函数外，还可以使用sys.dm_fts_parser来分析断词结果，sys.dm_fts_parser函数用起来更加灵活。
比如，针对同样的一句话，我们可以比较繁体中文和简体中文的断词结果:

SQL code

   
   

    
    
SELECT special_term,display_term FROM sys.dm_fts_parser('宁夏固原有一位网友给敬爱的苍老师写了一封信',1028,0,0) --繁体中文
/*
special_term     display_term
---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Exact Match      宁
Exact Match      夏
Exact Match      固
Exact Match      原有
Noise Word       一
Exact Match      位
Exact Match      网
Exact Match      友
Exact Match      给
Exact Match      敬
Exact Match      爱
Noise Word       的
Exact Match      苍        --注意这里
Exact Match      老
Exact Match      师
Exact Match      写
Noise Word       了
Noise Word       一
Exact Match      封
Exact Match      信

(20 行受影响)
*/

SELECT special_term,display_term FROM sys.dm_fts_parser('宁夏固原有一位网友给敬爱的苍老师写了一封信',2052,0,0) --简体中文
/*
special_term     display_term
---------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Exact Match      宁夏
Exact Match      固
Exact Match      原有
Noise Word       一
Exact Match      位
Exact Match      网友
Exact Match      给
Exact Match      敬爱
Noise Word       的
Exact Match      苍老    --注意这里
Exact Match      师
Exact Match      写
Noise Word       了
Noise Word       一
Exact Match      封
Exact Match      信

(16 行受影响)
*/
   
   

可以看到，对于"苍老师"，简体和繁体的断词结果是不同的，对于繁体中文，用CONTAINS(*,'苍')是能够查到这条记录的.
顺便说一句，有意使用别的语言也是对付断词不准的一种方法.