在正式介绍SQL全文索引相关概念,先请各位跟着如下步骤实战一下:
-
- --新建一个测试数据库,并指定排序规则为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,到目前为止,准备工作基本完成,下面开始查询
- --例1:
- SELECT * FROM TB WHERE CONTAINS(*,'小')
- /*
- ID Title Body
- ----------- ------------------------------ ------------------------------------------------------
- (0 行受影响)
- */
- SELECT * FROM TB WHERE CONTAINS(*,'小泽')
- /*
- ID Title Body
- ----------- ------------------------------ ------------------------------------------------------
- 1 艺术家评选 苍井空和小泽玛利亚是深受人民群众喜爱的著名艺术家
- (1 行受影响)
- */
- --例2:
- SELECT * FROM TB WHERE CONTAINS(*,'苍')
- /*
- ID Title Body
- ----------- ------------------------------ ------------------------------------------------------
- 1 艺术家评选 苍井空和小泽玛利亚是深受人民群众喜爱的著名艺术家
- (1 行受影响)
- */
- SELECT * FROM TB WHERE CONTAINS(*,'苍老师')
- /*
- ID Title Body
- ----------- ------------------------------ ------------------------------------------------------
- 2 给苍老师的一封信 宁夏固原有一位网友给敬爱的苍老师写了一封信
- (1 行受影响)
- */
看着上面的查询结果,是不是很奇怪,这和我们的预期完全不一样嘛,怎么会这样呢?
对于例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(*,'苍')是能够查到这条记录的.
顺便说一句,有意使用别的语言也是对付断词不准的一种方法.