欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-05-19 09:25:48
...

本文章来总结了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

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