sql全文检索(full-text)用法与注意事项

本文章来总结了SQL Server全文检索(full-text)用法与注意事项，有需要学习与了解sqlserver全文搜索的朋友可参考本文章。

SQL Server全文搜索 一项强大而又让人无可奈何的技术！说他强大，是因为他的检索速度极快，比like快几十上百倍。说他让人无可奈何，是因为很多场合对准确性要求很高，使用者又不能自己很好的组织查询语句，所以查出来的结果让人觉得没有确定性。

原本觉得这技术很不错，但真正研究起来，发现问题还是很多，研究到最后觉得这技术对柳永法(yongfa365)'Blog来说，只会用到对精确度不高的场合，像：CMS系统，大量文章，根据tag进行快速检索，这时他的快速检索优势能发挥出来，其它地方，真不敢用。像OA，各种业务系统，用他搜索不出要的东西，或者尝试很多次搜索，那用户还不郁闷死。暂且把研究结果放上来，以备后查。

前段时间做了个系统，记录客户的地址，下次再输入类似的地址，系统会进行分词，然后搜索看这个客户是不是已经在我们系统里了。感觉全文搜索能实现这样的功能就好了：就是，根据哪些词索引由用户自己定义（如：朝阳区|海淀区|东城区|左家庄|中关村 等，整个北京的地名，也没多少关键词），只对这些词进行索引，这样一来查询速度会更快，全文目录所占的地方也会更小，不知为何SQL Server没提供这样的功能，也许这只是 个例 吧。

全文查询只需要几秒或更少的时间，具体取决于返回的行数。
可以对包含 char、varchar 和 nvarchar 数据的列创建全文索引。也可以对包含格式化二进制数据（如存储在 varbinary(max) 或 image 列中的 Microsoft Word 文档）的列创建全文索引。不能使用 LIKE 谓词来查询格式化的二进制数据。

创建全文索引 创建全文索引的一个前提是，表中必须有这样的一个索引“unique, single-column, non-nullable index”。大多数情况下，主键都会满足这样的要求。 SQL Server这样做的实际要求是： 1． 必须有非空索引(主键都会满足这样的要求)。 2． 索引必须作用在单独一个列上。复合主键是不满足这个要求，一个work around是可以新建立一列，如textid，int型，自增，并建立索引。 提示：如果你的主键过长，笔者也建议新建一个int型自增列，例如主键是GUID的话，那么创建全文索引开销会是非常巨大的。

 
最简步骤：（以AdventureWorks数据库中的databaselog表中的event字段为例）

代码如下

复制代码

1、  启用全文索引： use AdventureWorks exec sp_fulltext_database 'enable' 2、  全文索引是存储在指定的文件系统中的，而不是SQLServer中。 exec sp_fulltext_catalog 'Cat_Desc', 'create', 'f:ft' 创建全文索引的目录 3、  对表创建全文索引 exec sp_fulltext_table 'databaselog', 'create', 'Cat_Desc', 'PK_DatabaseLog_DatabaseLogID' 在已有的表上根据已有的索引创建全文索引   4、  对表中的列添加全文索引 exec sp_fulltext_column 'databaselog', 'event', 'add'   5、  表启动完全填充 exec sp_fulltext_table 'databaselog', 'start_full'   6、  执行全文检索 select * from freetexttable(databaselog, event,'ALTER_TABLE');

注意事项：

•为了让全文搜索更好的运行，请选用nvarchar，放弃使用varchar 原因见：SQL Server全文搜索关于varchar与nvarchar的问题
•全文搜索不适合对精准度要求很高的场合，如，查找"show.aspx?id=",是查不出来的，推荐使用SQL Server CLR, 教程：SQL Server CLR 极速入门，启用、设计、部署、运行
•全文搜索最好是配合分词组件工作，分词后再使用全文搜索查找，分词组件见：盘古分词
操作：

1.确保服务已启用：在"SQL Server 配置管理器"里设置"SQL Server FullText Search"启动状态为"自动"
2.建全文索引最直观的方法是：右击相关表-->全文索引-->定义全文索引-->然后基本上是下一步就完成了。
最常用语法：

代码如下	复制代码
SELECT * FROM [test] WHERE CONTAINS(UserName,'柳永法'); SELECT * FROM [test] WHERE CONTAINS(Address,'山西 or 天津'); SELECT * FROM [test] WHERE CONTAINS(Address,'山西 and 天津');

SQL Server全文搜索关于varchar与nvarchar的问题

SQL Server全文搜索有一个问题，就是记录不全，上周末经过分析发现，

1.字段类型是varchar 且 如果搜索的的词正好是文章的结尾，就搜索不到，解决方法是在他后边再加上一个.让他不是在文章最后就行
2.字段类型换成nvarchar后，问题解决

代码如下

复制代码

CREATE DATABASE test USE test DROP TABLE test --建测试表 CREATE TABLE test     (       id INT IDENTITY(1, 1) PRIMARY KEY ,       txtTitle1 VARCHAR(50) ,--这里用的是varchar       txtTitle2 NVARCHAR(50) ,--这里用的是nvarchar     )     --插入50条数据 DECLARE @i INT SET @i = 0 WHILE @i     BEGIN         INSERT  INTO test                 ( txtTitle1, txtTitle2 )         VALUES  ( '柳永法', '柳永法')         SET @i = @i + 1     END

--对test表建全文索引，列选择txtTitle1, txtTitle2。方法如下：
--右击要建全文索引的表-->全文索引-->定义全文索引-->点几下"下一步"直到“选择表列”
-->选中要建立全文索引的列-->下一步-->自动-->创建新目录（写上名称，选位置，其它自便）
-->点几下"下一步"直到完成,这时系统会开始建全文目录，建好没有可以在当前数据库所在：
--展开当前数据库-->存储-->全文目录-->右击，你刚才起的名，如果“重新生成”是灰色，
--说明系统当前正在生成，如果可以点，说明生成完了

 

代码如下

复制代码

--针对varchar的列txtTitle1 只要查询里是以 '法' 结尾，都查不出记录来 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle1,'柳永法'); --0 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle1,'柳'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle1,'永'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle1,'法'); --0 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle1,'柳永'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle1,'永法'); --0 SELECT REPLICATE('-',20) --针对nvarchar的列txtTitle2 都可以查出全部记录 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle2,'柳永法'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle2,'柳'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle2,'永'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle2,'法'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle2,'柳永'); --50 SELECT COUNT(id) FROM dbo.test WHERE CONTAINS(txtTitle2,'永法'); --50 --在txttitle列末尾随便加一个字符 UPDATE test SET txttitle1=txttitle1+'.'

   
--重建索引后，再执行上面的查询，结果都 是50

--通过以上示例得出结论：只要在设计字段时把varchar改成nvarchar,就可以解决我们的问题，且nvarchar有个好处就是：如果数据库服务器部署在非中文的系统上时，不会出现乱码问题。

详细的全文搜索参考http://msdn.microsoft.com/zh-cn/library/ms142571(v=SQL.100).aspx

全文检索很容易建立，一旦建立，快速的响应将给使用者和用户带来惊喜！