本文介绍了SQL全文索引的概念、原理和技术存储过程。全文索引是搜索引擎的关键技术，涉及词库定义、分词方法和存储过程。在SQL Server中，全文索引通过一系列存储过程实现，包括启动服务、创建全文目录和索引等。此外，文章还讨论了全文索引的创建、查询语法，以及如何使用全文谓词如CONTAINS和FREETEXT进行复杂查询。最后，文中提及了SQL正则表达式的应用。
参考网络资料，自己总结一些
*************************************
全文索引
1.定义
全文索引技术是目前搜索引擎的关键技术。
中文名
全文索引
外文名
term
方 法
先定义一个词库
性 质
搜索引擎的关键技术
2.原理
原理是先定义一个词库，然后在文章中查找每个词条(term)出现的频率和位置，把这样的频率和位置信息按照词库的顺序归纳，这样就相当于对文件建立了一个以词库为目录的索引，这样查找某个词的时候就能很快的定位到该词出现的位置。
问题在处理英文文档的时候显然这样的方式是非常好的，因为英文自然的被空格分成若干词，只要我们有足够大的词汇库就能很好的处理。但是亚洲文字因为没有空格作为断词标志，所以就很难判断一个词，而且人们使用的词汇在不断的变化，而维护一个可扩展的词汇库的成本是很高的，所以问题出现了。
解决出现这样的问题使“分词”成为全文索引的关键技术。目前有两种基本的方法：
二元法 它把所有有可能的每两两汉字的组合看为一个词组，这样就没有维护词库的开销。
词库法 它使使用词库中的词作为切分的标准，这样也出现了词库跟不上词汇发展的问题，除非你维护词库。
实际上现在很多著名的搜索引擎都使用了多种分词的办法，比如“正向最大匹配”+“逆向最大匹配”，基于统计学的新词识别，自动维护词库等技术，但是显然这样的技术还没有做到完美。
3.技术存储过程
目前全文索引技术正走向人工智能化，也是发展的方向。
MicroSoft SqlServer 2000/2005 中的全文索引是由一系列存储过程来完成的，这些存储过程按先后顺序罗列如下：
1、启动数据库的全文索引服务存储过程：sp_fulltext_service
2、初始化全文索引存储过程：sp_fulltext_database
3、建立全文索引目录存储过程：sp_fulltext_catalog
4、在全文索引目录中添加删除表标记存储过程：sp_fulltext_table
5、在全文索引目录的表中添加或删除列标记存储过程：sp_fulltext_column
4.SQL全文索引
CREATE TABLE Flags (Country nvarchar(30) NOT NULL, FlagColors varchar(200));
/*创建唯一索引*/
CREATE UNIQUE CLUSTERED INDEX FlagKey ON Flags(Country);
INSERT Flags VALUES ('France', 'Blue and White and Red');
INSERT Flags VALUES ('Italy', 'Green and White and Red');
INSERT Flags VALUES ('Tanzania', 'Green and Yellow and Black and Yellow and Blue');
SELECT * FROM Flags;
GO
/*数据库创建全文目录*/
CREATE FULLTEXT CATALOG TestFTCat;
/*创建全文索引列*/
CREATE FULLTEXT INDEX ON Flags(FlagColors) KEY INDEX FlagKey ON TestFTCat;
GO
SELECT * FROM Flags;
/*通过全文索引列，找关键词*/
SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Blue');
SELECT * FROM FREETEXTTABLE (Flags, FlagColors, 'Yellow');
语法：
FREETEXTTABLE (table , { column_name | (column_list) | * }
, 'freetext_string'
[ , LANGUAGE language_term ]
[ , top_n_by_rank ] )
表
是表的名称标记为全文查询。表或viewcan是一个,两个,或由三部分组成的数据库对象名称。查询视图时,只有一个全文索引可以涉及基表。
表不能指定服务器名称,不能用于查询链接服务器。
column_name
是一个或多个全文索引列的名称的表在FROM子句中指定。类型的列可以char、varchar nchar,nvarchar,文本,ntext、形象、xml、varbinary或varbinary(max)。
column_list
表明几个列,由一个逗号分开,可以指定。column_list必须括在括号里。除非指定language_term,所有列的语言column_list必须相同。
*
指定所有列已经注册的全文搜索应该用于搜索给定freetext_string。除非指定language_term,表中所有全文索引列的语言必须相同。
freetext_string
是column_name文本搜索。任何文本,包括单词、短语或句子,可以进入。匹配生成如果任何术语或任何术语的形式是在全文索引中找到。
与包含搜索条件和关键字,当用于freetext_string’和‘这个词被认为是噪音词,或stopword,并将被丢弃。
使用的重量,形式,通配符,附近和其他语法是不允许的。freetext_string wordbroken,遏制和通过同义词典。
语言language_term
的语言资源将被用于打破,阻止,同义词典和stopword删除作为查询的一部分。这个参数是可选的,可以指定一个字符串,整数,或十六进制值对应区域设置标识符(LCID)的一种语言。如果指定language_term,它所代表的语言将被应用到搜索条件的所有元素。如果没有指定值,列全文语言使用。
如果不同语言的文档存储在一起作为二进制大对象(blob)在一个专栏中,给定文档的区域标识符(LCID)决定了语言是用来���引内容。当查询这样一列,指定LANGUAGElanguage_term可以增加良好匹配的概率。
当指定为一个字符串,language_term列值的对应的别名sys。syslanguages(transact - sql)兼容性视图。必须括在单引号的字符串,如“language_term”。当指定为一个整数,language_term是实际的LCID标识语言。当指定为十六进制值,language_term 0 x的十六进制值LCID紧随其后。十六进制值不得超过八位数,包括前导零。
如果该值为双字节字符集(DBCS)格式,Microsoft SQL Server将它转换为Unicode。
如果指定的语言是无效或没有资源对应的安装语言,SQL服务器返回一个错误。使用中性语言资源,指定0 x0 language_term。
top_n_by_rank
指定只有nhighest排名比赛,在降序排列,返回。只适用于当一个整数值,n,指定。如果top_n_by_rank结合其他参数,查询可能返回行数少于实际匹配所有的谓词的行数。top_n_by_rank允许您查询性能增加回忆只有最相关的。
Arguments
table
Is the name of the table that has been marked for full-text querying. table or viewcan be a one-, two-, or three-part database object name. When querying a view, only one full-text indexed base table can be involved.
table cannot specify a server name and cannot be used in queries against linked servers.
column_name
Is the name of one or more full-text indexed columns of the table specified in the FROM clause. The columns can be of type char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max).
column_list
Indicates that several columns, separated by a comma, can be specified. column_list must be enclosed in parentheses. Unless language_term is specified, the language of all columns of column_list must be the same.
*
Specifies that all columns that have been registered for full-text searching should be used to search for the given freetext_string. Unless language_term is specified, the language of all full-text indexed columns in the table must be the same.
freetext_string
Is text to search for in the column_name. Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.
Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word, or stopword, and will be discarded.
Use of WEIGHT, FORMSOF, wildcards, NEAR and other syntax is not allowed. freetext_string is wordbroken, stemmed, and passed through the thesaurus.
LANGUAGE language_term
Is the language whose resources will be used for word breaking, stemming, and thesaurus and stopword removal as part of the query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.
If documents of different languages are stored together as binary large objects (BLOBs) in a single column, the locale identifier (LCID) of a given document determines what language is used to index its content. When querying such a column, specifying LANGUAGElanguage_term can increase the probability of a good match.
When specified as a string, language_term corresponds to the alias column value in the sys.syslanguages (Transact-SQL) compatibility view. The string must be enclosed in single quotation marks, as in 'language_term'. When specified as an integer, language_term is the actual LCID that identifies the language. When specified as a hexadecimal value, language_term is 0x followed by the hexadecimal value of the LCID. The hexadecimal value must not exceed eight digits, including leading zeros.
If the value is in double-byte character set (DBCS) format, Microsoft SQL Server will convert it to Unicode.
If the language specified is not valid or there are no resources installed that correspond to that language, SQL Server returns an error. To use the neutral language resources, specify 0x0 as language_term.
top_n_by_rank
Specifies that only the nhighest ranked matches, in descending order, are returned. Applies only when an integer value, n, is specified. If top_n_by_rank is combined with other parameters, the query could return fewer rows than the number of rows that actually match all the predicates. top_n_by_rank allows you to increase query performance by recalling only the most relevant hits.
全文索引概念
· 全文索引是针对数据表，只能对表创建全文索引，不能对数据库创建全文索引。
· 每个数据库可以不包含全文目录或包含多个全文目录，一个全文目录可以包含多个全文索引，但一个全文索引只能用于构成一个全文目录。
· 一个数据表只能创建一个全文索引，一个全文索引可以包含多个字段。
· 创建全文索引的表必须要有一个唯一的非空索引，并且这个唯一的非空的索引只能是一个字段，不能是组合字段。
· 每个表只允许有一个全文索引。若要对某个表创建全文索引，该表必须具有一个唯一且非 Null 的列。您可以对以下类型的列创建全文索引：char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 和 varbinary(max)，从而可对这些列进行全文搜索。对数据类型为 varbinary、varbinary(max)、image 或 xml 的列创建全文索引需要您指定类型列。类型列是用来存储每行中文档的文件扩展名（.doc、.pdf、xls 等）的表列。
启动服务
在SQL Server配置管理工具中，找到'SQL Full-text Filter Daemon Launcher'服务用本地用户启动。
创建全文目录
打开需要创建全文目录的数据库-存储-全文目录-右键新建全文目录
用语句创建全文目录
CREATE FULLTEXT CATALOG [FD_HouseSearch]WITH ACCENT_SENSITIVITY = ONAS DEFAULTAUTHORIZATION [dbo]
创建全文索引
右键需要创建全文索引的表-全文索引-定义全文索引
1.全文索引必须要有一个唯一非空索引，这里选择主键。
2.选择需要全文搜索的列，并且选择断字符语言，因为该字段主要用来存储中文，所以这里也选择了简体中文。
断字符：断字符用来对全文搜索数据进行语言分析，查找单词的边界，也就是怎样将一段很长的内容拆分成日常的词语或字。例如“全文搜索”，可能会断字成“全文”、‘搜索’、‘全’、‘文’、‘搜’、‘索’等符合中国人正常的习惯的词或字。
3.选择跟踪方式，这里选择自动跟踪，就是表发生更改时自动填充索引。
4.选择全文目录、索引文件、非索引字表
非索引字表：在刚才的断字中讲了怎样断字，这里就是将断的字保存在一张表中，该处选择系统默认的非索引字表.
----查询断字表SELECT TOP 1000 * FROM sys.dm_fts_index_keywords(db_id(''), object_id(''))
5.填充计划
可以新建填充计划来填充全文索引，填充计划可以是完全填充、增量填充、更新填充。
用语句创建全文索引
--语句少了很多默认参数，其它就按系统默认即可
CREATE FULLTEXT INDEX ON
dbo.Housetest
(Description
) /表，字段*/
KEY INDEX
PK_Housetest /*唯一索引*/
ON FD_HouseSearch/*全文搜索目录*/
全文谓词
全文查询使用全文谓词（CONTAINS 和 FREETEXT）以及全文函数（CONTAINSTABLE 和 FREETEXTTABLE）。它们支持复杂的 Transact-SQL 语法，这种语法支持各种形式的查询词。若要编写全文查询，必须了解何时以及如何使用这些谓词和函数。
CONTAINS 谓词可以搜索：
词或短语。
词或短语的前缀。
与另一个词相邻的词。
由另一个词的词形变化而生成的词（例如，drive 一词是 drives、drove、driving 和 driven 词形变化的词干）。
使用同义词库确定的另一个词的同义词（例如，metal 一词可能有 aluminum 和 steel 等同义词）。
FREETEXT谓词的用法这里就不做解释了！
经常用法语句
/*查看全文搜索目录*/
select * from sys.fulltext_catalogs
/*查看拆分的词库*/
/**使用系统拆分规则拆分，可以自定义建立拆分**/
/*自动触发全文搜索词库*/
/*多列组成全文搜索列*/
contains和freetext不会限定返回结果的数量，而是将满足条件的全部返回
这会带来两个比较重要的问题，
１、我们会得到很多无用的结果，同时如果返回结果过多，也会极大影响查询效率
２、返回的结果是无序的，并没有按照预想的如“相似程度”进行排序，导致最好的查询结果往往不再最前面
为了克服这样的问题，就可以使用containstable和freetexttable语法，这两个查询方法可以限定返回结果的数量，同时能赋予一个rank函数（相似度函数）返回rank最大的n个结果，这就是著名的top_n_by_rank
SQL正则表达式
1. --如果存在则删除原有函数
2. IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
3. DROP FUNCTION dbo.RegexReplace
4. GO
5. --开始创建正则替换函数
6. CREATE FUNCTION dbo.RegexReplace
7. (
8. @string VARCHAR(MAX), --被替换的字符串
9. @pattern VARCHAR(255), --替换模板
10. @replacestr VARCHAR(255), --替换后的字符串
11. @IgnoreCase INT = 0 --0区分大小写 1不区分大小写
12. )
13. RETURNS VARCHAR(8000)
14. AS
15. BEGIN
16. DECLARE @objRegex INT, @retstr VARCHAR(8000)
17. --创建对象
18. EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT
19. --设置属性
20. EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern
21. EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase
22. EXEC sp_OASetProperty @objRegex, 'Global', 1
23. --执行
24. EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr
25. --释放
26. EXECUTE sp_OADestroy @objRegex
27. RETURN @retstr
28. END
29. GO
30. --保证正常运行的话，需要将Ole Automation Procedures选项置为1
31. EXEC sp_configure 'show advanced options', 1
32. RECONFIGURE WITH OVERRIDE
33. EXEC sp_configure 'Ole Automation Procedures', 1
34. RECONFIGURE WITH OVERRIDE
复制以上代码，到SQL中执行
然后测试，如下
[sql] view plain copy
1. --2.将姓名倒过来
2. SELECT dbo.RegexReplace('John Smith', '([a-z]+)\s([a-z]+)', '$2,$1',1)
3. /*
4. --------------------------------------
5. Smith,John
6. */
扫一扫
756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
