SQLSERVER 2008 R2中的全文检索

最近在做全文搜索的内容，google了一下全文检索，发现了一些问题，现在总结如下。 全文索引和查询概念(摘自SQL联机帮助) SQL Server 2008 为应用程序和用户提供了对 SQL Server 表中基于字符的数据发出全文查询的功能。在可以对给定表运行全文查询之前，数据

　　最近在做全文搜索的内容，google了一下全文检索，发现了一些问题，现在总结如下。

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

　　SQL Server 2008 为应用程序和用户提供了对 SQL Server 表中基于字符的数据发出全文查询的功能。在可以对给定表运行全文查询之前，数据库管理员必须对表创建全文索引。全文索引包括表中一个或多个基于字符的列。这些列可以具有下列任何一种数据类型：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 或 varbinary(max)。每个全文索引都对基表中的一个或多个列创建索引，并且每个列都可以具有一种特定语言。从 SQL Server 2008 开始，全文搜索支持 50 多种不同语言，比如英语、西班牙语、中文、日语、阿拉伯语、孟加拉语和印地语。有关支持的全文语言的完整列表，请参阅 sys.fulltext_languages (Transact-SQL)。

　　对于每种支持的语言，SQL Server 都提供了特定于语言的语言组件，包括断字符和词干分析器以及一个空同义词库文件。对于每种全文语言，SQL Server 也提供了一个文件（“同义词库文件” ），您可以在该文件中有选择地定义特定于语言的同义词以扩展搜索查询的范围。另外，从 SQL Server 2008 开始提供系统非索引字表。若要支持特定语言或商业应用场景，您可以通过添加和删除非索引字（也称为干扰词）更改系统非索引字表，并且您可以根据需要创建附加的非索引字表。

　　SQL Server 为编写全文查询提供了一组全文谓词（CONTAINS 和 FREETEXT）和行集值函数（CONTAINSTABLE 和 FREETEXTTABLE）。使用这些全文谓词和行集值函数，应用程序和用户可以执行各种类型的全文搜索，例如搜索单个单词或短语（并可以选择对结果集排名），搜索与其他单词或短语接近的单词或短语，或者搜索特定单词的同义词形式。

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

　　全文查询根据特定语言（例如，英语或日语）的规则对词和短语进行操作，从而对全文索引中的文本数据执行语言搜索。全文查询可以包括简单的词和短语，或者词或短语的多种形式。

　　全文搜索适用于多种商业应用场景：例如，电子商务（在网站上搜索项目）、律师事务所（在法律数据库中搜索案例记录）或人力资源部门（从所存储的个人简历中找到符合职位描述的简历）。不管是什么样的商业应用场景，全文搜索的基本管理任务和开发任务是相同的。然而，在给定的商业应用场景中，可以对全文索引和查询进行优化以使其满足业务目标。例如，对于电子商务来说，最大限度地提高性能可能比对结果进行排序、检索的准确性（实际上有多少个现有匹配项是由全文查询返回的）或支持多种语言更重要。对于律师事务所来说，首先需要考虑的可能是返回所有可能存在的匹配项（“返回全部” 信息）。

　　例如，假定有一个对 DevTools 表的全文索引。全文索引可能指出在 Abstract 列的第 423 个和第 982 个单词处找到了单词 Microsoft，所在的行与 ProductID 6 关联。该索引结构支持对所有包含被索引单词的项进行有效检索，以及高级检索操作，如短语检索和邻近检索。
　　为防止全文索引因包含很多对检索没有帮助的词而变得臃肿，a、and、is 或 the 这类额外的词都忽略不计。例如，指定"the products ordered during these summer months"与指定"products ordered during summer months"是一样的。有这两个字符串的行都会被返回。
　　目录 \Mssql\Ftdata\Sqlserver\Config 下提供了多种语言的干扰词列表。在安装带有全文检索支持的 Microsoft® SQL Server™ 时会创建这个目录，并同时安装干扰词文件。干扰词文件可以编辑。例如，高技术公司的系统管理员可以把单词 computer 添加到他们的干扰词表中去。（如果编辑干扰词文件，则必须在更改生效之前重新填充全文目录。）下表显示了干扰词文件及其相应的语言。

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

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

<p><span>Book_No   Writer     Title <br>-------- ----------- --------------------------<br>A025     Asimov      Foundation's Edge <br>A027     Asimov      Foundation and Empire <br>C011     Clarke      Childhood's End <br>V109     Verne       Mysterious Island </span></p>

　　假定想使用一个全文检索查询来查找包含单词 Foundation 的书名。在本例中，将从全文索引获得值 A025 和 A027。然后 SQL Server 用这些键值和其它栏的信息响应该查询。　　　　
　　下表显示了存储全文索引数据所使用的语言。这些语言基于 SQL Server 安装期间选择的 Unicode 排序规则区域设置标识符。

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

　　此列表中没有的其它所有 Unicode 排序规则区域设置标识符值都映射到使用空格分隔单词的中性语言单词的断字符和词干分隔符。
　　说明  Unicode 排序规则区域设置标识符设置用于所有可进行全文索引的数据类型（如 char、nchar 等）。如果为 char、varchar 或 text 类型列的排序次序设置的语言类型，不是 Unicode 排序规则区域设置标识符语言，那么在对 char、varchar 和 text 类型的列进行全文索引和查询时，仍然使用 Unicode 排序规则区域设置标识符值。

　　创建全文索引

　　1、启动 Microsoft Search 服务
　　开始菜单-->SQL程序组-->服务管理器-->下拉筐-->Microsoft Search 服务-->启动它。
　　2、 ..\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\Config\目录里建一个非空noise.chs文件
　　非空noise.chs文件，也有人说是空的noise.chs文件，但我每次都往里写几个没用的字母。
　　3、建立环境
　　打开查询分析器-->执行下列脚本：

<p><span>--</span><span>检查数据库pubs是否支持全文索引，如果不支持</span><span><br>--</span><span>则使用sp_fulltext_database 打开该功能</span><span><br></span><span>if</span><span>(</span><span>select</span><span>databaseproperty</span><span>(</span><span>'</span><span>DeviceInfoPortal</span><span>'</span><span>,</span><span>'</span><span>isfulltextenabled</span><span>'</span><span>))</span><span>=</span><span>0</span><span> <br>  </span><span>execute</span><span> sp_fulltext_database </span><span>'</span><span>enable</span><span>'</span><span><br><br></span><span>--</span><span>建立全文目录FT_PUBS</span><span><br></span><span>execute</span><span> sp_fulltext_catalog </span><span>'</span><span>FT_DeviceInfoPortal</span><span>'</span><span>,</span><span>'</span><span>create</span><span>'</span><span><br><br></span><span>--</span><span>创建唯一索引</span><span><br></span><span>create</span><span>unique</span><span>clustered</span><span>index</span><span> Idx_files_id </span><span>on</span><span> dbo.files(fileid)<br><br></span><span>--</span><span>为title表建立全文索引数据元</span><span><br></span><span>execute</span><span> sp_fulltext_table </span><span>'</span><span>files</span><span>'</span><span>,</span><span>'</span><span>create</span><span>'</span><span>,</span><span>'</span><span>FT_DeviceInfoPortal</span><span>'</span><span>,</span><span>'</span><span>Idx_files_id</span><span>'</span><span><br><br></span><span>--</span><span>设置全文索引列名</span><span><br>--</span><span>execute sp_fulltext_column 'files','content','add'</span><span><br></span><span>execute</span><span> sp_fulltext_column </span><span>@tabname</span><span>=</span><span>'</span><span>files</span><span>'</span><span>,</span><span>@colname</span><span>=</span><span>'</span><span>content</span><span>'</span><span>,</span><span>@action</span><span>=</span><span>'</span><span>add</span><span>'</span><span>,</span><span>@type_colname</span><span>=</span><span>'</span><span>type</span><span>'</span><span>;<br><br></span><span>--</span><span>execute sp_fulltext_column 'files','content','drop'</span><span><br></span><span><br></span><span>--</span><span>建立全文索引</span><span><br>--</span><span>activate，是激活表的全文检索能力，也就是在全文目录中注册该表</span><span><br></span><span>execute</span><span> sp_fulltext_table </span><span>'</span><span>files</span><span>'</span><span>,</span><span>'</span><span>activate</span><span>'</span><span><br><br></span><span>--</span><span>填充全文索引目录</span><span><br></span><span>execute</span><span> sp_fulltext_catalog </span><span>'</span><span>FT_DeviceInfoPortal</span><span>'</span><span>,</span><span>'</span><span>start_full</span><span>'</span><span><br></span><span>go</span><span><br><br></span><span>--</span><span>在您可以使用操作系统筛选器、断字符和词干分析器之前，您必须将它们加载到服务器实例中</span><span><br></span><span>execute</span><span> sp_fulltext_service </span><span>@action</span><span>=</span><span>'</span><span>load_os_resources</span><span>'</span><span>, </span><span>@value</span><span>=</span><span>1</span><span>;<br><br></span><span>--</span><span>检查全文目录填充情况</span><span><br></span><span>While</span><span>fulltextcatalogproperty</span><span>(</span><span>'</span><span>FT_DeviceInfoPortal</span><span>'</span><span>,</span><span>'</span><span>populateStatus</span><span>'</span><span>)</span><span></span><span>0</span><span><br></span><span>begin</span><span><br><br></span><span>--</span><span>如果全文目录正处于填充状态，则等待30秒后再检测一次</span><span><br></span><span>waitfor</span><span> delay </span><span>'</span><span>0:0:30</span><span>'</span><span><br></span><span>end</span><span> </span></p>

<p><span>--</span><span>全文目录填充完成后，即可使用全文目录检索</span><span><br></span><span><br></span><span>select</span><span>*</span><span><br></span><span>from</span><span> files<br></span><span>where</span><span>CONTAINS</span><span>(</span><span>*</span><span>,</span><span>'</span><span>运行记录</span><span>'</span><span>)<br></span><span>or</span><span>CONTAINS</span><span>(</span><span>*</span><span>,</span><span>'</span><span>Understanding</span><span>'</span><span>)<br></span><span>or</span><span>CONTAINS</span><span>(</span><span>*</span><span>,</span><span>'</span><span>全文搜索</span><span>'</span><span>)<br><br></span><span>or</span><span>CONTAINS</span><span>(</span><span>*</span><span>,</span><span>'</span><span>出厂日期</span><span>'</span><span>)</span></p>

　　结果如下：

　　全文索引中的几个问题：
　　1. 搜索时出现错误:
　　服务器: 消息 7619，级别 16，状态 1，行 2
　　查询子句只包含被忽略的词
　　这种情况修改 \Mssql\Ftdata\Sqlserver\Config 下对应语言的干扰词列表文件

　　2. 修改了干扰词文件,查询中文时仍然出现上述问题

　　a. 首先检查你的SQL有没有安装最新的补丁，检查的方法是在查询分析器中运行:

　　select @@version
　　如果出来的版本号是8.00.760以下，则表明你未安装sp3的补丁，要装上。
　　注意下载后，执行的时候是解压，要在解压后的目录中执行setup.bat才是真正的安装。

　　b. 配置全文索引时,单词断字符选择"中文(中国)"
　　c. Noise.chs文件中至少有一个单词，例如:?

　　d. 如果在全文检索时，你能正常修改干扰词文件，说明你的全文检索没有使用上这个文件。

　　如果你配置的全文检索应该要用到这个文件，那就在

　　“企业管理器--展开你的数据库--右键全文目录--重建全部全文目录”

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

　　4. SQL Server 2000以上才支持对image列的全文检索。

　　ps： sql sever全文检索对word内容进行检索可行。如果文件类型是 pdf, ceb, 永中的uof,eio，不能全文检索，谁有好方法能检索到这些文件类型，请留下宝贵意见。

　　大文件保存建议用：SQL Server 2008 FILESTREAM特性管理文件