SQLServer 全文检索(full-text)语法

sql server 全文检索有两种搜索方式，一种是contains，另一种是freetext。前者是包含，类似于

like '%关键词%'，后者则是将一段文字分词以后对每个词进行搜索。

具体语法：
contains：

SELECT 字段1,字段2
FROM 表名
WHERE contains(字段,'"词一" or "词二"')

根据查找结果的相似度排序
SELECT 字段1,字段2
FROM 表名
inner join containstable(表名, 字段,'"词一" or "词二"',10) as k
on 表名.id = k.[key]
order by k.RANK DESC


freetext:

SELECT 字段1,字段2
FROM 表名
WHERE freetext(字段,'词一词二')

根据查找结果的相似度排序
SELECT 字段1,字段2
FROM 表名
inner join freetexttable(表名, 字段,'词一词二',10) as k
on 表名.id = k.[key]
order by k.RANK DESC

上文中freetexttable或containstable的10表示取10条数据

最近搜索了一下全文检索,发现了一些问题,现在总结如下:

全文索引和查询概念(摘自SQL联机帮助)

全文索引、查询和同步化最主要的设计要求是，在注册进行全文检索的所有表上都有一个唯一的全文键列（或者单列主键）。全文索引对使用的重要字及其所在位置进行跟踪。

例如，假定有一个对DevTools表的全文索引。全文索引可能指出在Abstract列的第423个和第982个单词处找到了单词Microsoft，所在的行与ProductID6关联。该索引结构支持对所有包含被索引单词的项进行有效检索，以及高级检索操作，如短语检索和邻近检索。

为防止全文索引因包含很多对检索没有帮助的词而变得臃肿，a、and、is或the这类额外的词都忽略不计。例如，指定"theproductsorderedduringthesesummermonths"与指定"productsorderedduringsummermonths"是一样的。有这两个字符串的行都会被返回。

目录\Mssql\Ftdata\Sqlserver\Config下提供了多种语言的干扰词列表。在安装带有全文检索支持的Microsoft®SQLServer™时会创建这个目录，并同时安装干扰词文件。干扰词文件可以编辑。例如，高技术公司的系统管理员可以把单词computer添加到他们的干扰词表中去。（如果编辑干扰词文件，则必须在更改生效之前重新填充全文目录。）下表显示了干扰词文件及其相应的语言。

干扰词文件语言
-----------------------
Noise.chs简体中文
Noise.cht繁体中文
Noise.dat语言中性
Noise.deu德语
Noise.eng英语（英国）
Noise.enu英语（美国）
Noise.esn西班牙语
Noise.fra法语
Noise.ita意大利语
Noise.jpn日语
Noise.kor韩文
Noise.nld荷兰语
Noise.sve瑞典语


在处理全文查询时，检索引擎将满足检条件的行的键值返回给MicrosoftSQLServer。比如有一个SciFi表，其中Book_No列是主键列。

Book_NoWriterTitle
---------------------------------------------
A025AsimovFoundation'sEdge
A027AsimovFoundationandEmpire
C011ClarkeChildhood'sEnd
V109VerneMysteriousIsland


假定想使用一个全文检索查询来查找包含单词Foundation的书名。在本例中，将从全文索引获得值A025和A027。然后SQLServer用这些键值和其它栏的信息响应该查询。

下表显示了存储全文索引数据所使用的语言。这些语言基于SQLServer安装期间选择的Unicode排序规则区域设置标识符。

Unicode排序规则区域设置标识符全文数据存储所用的语言
------------------------------------------------------
中文注音符号（台湾）繁体中文
汉语拼音简体中文
中文笔画简体中文
中文笔画（台湾）繁体中文
荷兰语荷兰语
英语（英国）英语（英国）
法语法语
通用Unicode英语（美国）
德语德语
德文电话簿德语
意大利语意大利语
日语日语
日语Unicode日语
韩文韩文
韩文Unicode韩文
西班牙语（现代）西班牙语
瑞典/芬兰语瑞典语



此列表中没有的其它所有Unicode排序规则区域设置标识符值都映射到使用空格分隔单词的中性语言单词的断字符和词干分隔符。

说明Unicode排序规则区域设置标识符设置用于所有可进行全文索引的数据类型（如char、nchar等）。如果为char、varchar或text类型列的排序次序设置的语言类型，不是Unicode排序规则区域设置标识符语言，那么在对char、varchar和text类型的列进行全文索引和查询时，仍然使用Unicode排序规则区域设置标识符值。



创建全文索引(以索引image列为例,其他类型字段大致一样)

标题全文索引image列，全攻略！
作者pengdali[原作]
关键字全文索引image


今天“百年不遇”的停电了，看了一天书。晚上搞了一下全文索引，决定把心得贴出来，我尽量写的详细，大家共同学习，欢迎指正！

1、启动MicrosoftSearch服务
开始菜单-->SQL程序组-->服务管理器-->下拉筐-->MicrosoftSearch服务-->启动它

2、
..\MicrosoftSQLServer\MSSQL\FTDATA\SQLServer\Config\目录里建一个非空noise.chs文件
非空noise.chs文件，也有人说是空的noise.chs文件，但我每次都往里写几个没用的字母。

3、建立环境
打开查询分析器-->执行下列脚本：
--------------------------------------------
createdatabasetest---创建test数据库
usetest---选择test数据库
createtabledali(IDintnotnullprimarykey,MyImageimage,FileTypevarchar(255),FileNmaevarchar(255))---创建dali表
--dali表中Id,MyImage,FileType三列是必须的，因为要对image列索引的话，必须要有一个主键列，一个image列,一个存放文件类型的列
--我们知道在windows系统中文件类型是靠扩展名来区分的所以FileType列也就是用来放文件的扩展名
--------------------------------------------

sp_fulltext_database'enable'--为全文索引启用数据库
sp_fulltext_catalog'My_FullDir','create'---创建一个叫My_FullDif的全文目录

declare@Keysysname;select@Key=c.namefromsyscolumnsa,sysconstraintsb,sysobjectscwherea.id=object_id('dali')anda.name='ID'anda.id=b.idandb.constid=c.idandc.namelike'PK%'
execsp_fulltext_table'dali','create','My_FullDir',@Key----这两句是为全文索引,对表进行标记

sp_fulltext_column'dali','MyImage','add',0x0804,'FileType'---这句是指定MyImage列为全文索引列,FileType是类型列
------------------------------------------------
4、在c盘下放一个扩展名为doc的word文件,一个扩展名为xls的excel文件,一个扩展名为htm的网页文件,个扩展名为bmp的图片
共4个，大家可根据实际情况放入！

5、插入数据
建立下面这个存储过程
--------------------------------------------------
CREATEPROCEDUREsp_textcopy
@srvnamevarchar(30),
@loginvarchar(30),
@passwordvarchar(30),
@dbnamevarchar(30),
@tbnamevarchar(30),
@colnamevarchar(30),
@filenamevarchar(30),
@whereclausevarchar(40),
@directionchar(1)
AS
/*这是使用textcopy工具将文件插入到数据库中，如果有前台工具可以用前台开发工具将文件插入，这里为了演示*/
DECLARE@exec_strvarchar(255)
SELECT@exec_str='textcopy/S'+@srvname+'/U'+@login+'/P'+@password+'/D'+@dbname+'/T'+@tbname+'/C'+@colname+'/W"'+@whereclause+'"/F"'+@filename+'"/'+@direction
EXECmaster..xp_cmdshell@exec_str
----------------------------------------------------

insertdalivalues(1,0x,'doc','大力的doc')---其中第二列是0x它是一个16进制数对应image列,是必须的,不要写null,第三列是文件类型，既扩展名

sp_textcopy'你的服务器名','sa','你的密码','test','dali','MyImage','c:\大力的doc.doc','whereID=1','I'
-------依次参数是：实例名,用户名,密码，数据库名，表名，image列名，路径及文件名，条件(你必须保证它只选择一行)，I
---------------------------------------------------------------------------------------------------------------------
insertdalivalues(2,0x,'bmp','图片')
sp_textcopy'你的服务器名','sa','你的密码','test','dali','MyImage','c:\图片.bmp','whereID=2','I'--注意条件是ID=2

insertdalivalues(3,0x,'xls','Excel文件')
sp_textcopy'你的服务器名','sa','你的密码','test','dali','MyImage','c:\Excel文件.xls','whereID=3','I'--注意条件是ID=3

insertdalivalues(4,0x,'htm','网页')
sp_textcopy'你的服务器名','sa','你的密码','test','dali','MyImage','c:\网页.htm','whereID=4','I'--注意条件是ID=4

----------上面的语句,要保证类型一样，路径正确，条件唯一正确应该就可以了

6、填充全文索引

sp_fulltext_table'dali','start_full'---第一个参数是表名，第二个参数是启动表的全文索引的完全填充

7、可以开始你的实验了

select*fromdaliwherecontains(MyImage,'J老师')

select*fromdaliwherecontains(MyImage,'海老师')

------END----------
--调试环境：SQLServer2000企业版、Windows2000高级服务器

全文索引中的几个问题:

1.搜索时出现错误:
服务器:消息7619，级别16，状态1，行2
查询子句只包含被忽略的词

这种情况修改\Mssql\Ftdata\Sqlserver\Config下对应语言的干扰词列表文件

2.修改了干扰词文件,查询中文时仍然出现上述问题
a.首先检查你的SQL有没有安装最新的补丁,检查的方法是在查询分析器中运行:
select@@version
如果出来的版本号是8.00.760以下,则表明你未安装sp3的补丁,要装上.

SQL补丁下载:
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=9032f608-160a-4537-a2b6-4cb265b80766

注意下载后,执行的时候是解压,要在解压后的目录中执行setup.bat才是真正的安装

b.配置全文索引时,单词断字符选择"中文(中国)"

c.Noise.chs文件中至少有一个单词,例如:?

d.如果在全文检索时,你能正常修改干扰词文件,说明你的全文检索没有使用上这个文件
如果你配置的全文检索应该要用到这个文件,那就在
企业管理器--展开你的数据库--右键全文目录--重建全部全文目录

3.表中的数据改变后,检索不到
方法1.右键你的表--全文索引表--启用增量填充
方法2.右键你的表--全文索引表--更改跟踪,这样以后的修改会自动填充(有一定延迟)

4.sql2000才支持对image列的全文检索