当前位置:Gxlcms > mysql > sql全文检索(full-text)用法与注意事项

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

时间:2021-07-01 10:21:17 帮助过:66人阅读

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

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

人气教程排行