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

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 < 50
    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

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

补充：数据库,Mssql