【SQL Server学习笔记】全文检索

全文检索提供了基于字符串、二进制数据的智能单词短语的搜索，使得非结构化数据的搜索更为灵活，而且全文检索的执行效率比普通的like查询好很多。在SQL Server2008中全文检索功能已经集成到了数据库中，全文目录不在单独存储在文件系统中，现在也集成在数据库中。

与普通的B树聚集索引、非聚集索引不同，全文索引是由文本数据的索引标记组成的压缩的索引结构。标记是SQL Server在索引过程中标识的单词或字符串。使用特殊的全文检索功能可以在字符模式上扩展单词或短语的搜索，并基于其他单词的变形词、同义词、通配符、近义词进行搜索。

一个全文目录属于一个数据库，一个数据库可以有多个全文目录。

--1.1附加数据库
exec sp_attach_db 
	@dbname = 'AdventureWorks',
	@filename1 = 'c:\AdventureWorks_Data.mdf'

--1.2由于这个数据库是2005版本的,而开发环境是2008,所以必须修改兼容级别
alter database adventureworks
set compatibility_level = 100

use AdventureWorks
go


--2.1创建全文目录
create fulltext catalog cat_production_document
go

create fulltext catalog cat_production_document_ex2 
with accent_sensitivity = on 
go

--3.1创建全文索引
create fulltext index on production.Document  --在这个表上建全文索引
(
   DocumentSummary,
   Document TYPE COLUMN FileExtension
)
key index PK_Document_DocumentID    --键索引,一般是表的主键,唯一键索引名
on cat_production_document          --全文目录
with (CHANGE_TRACKING AUTO,         --全文索引会随着表数据的修改而自动更新
      StopList=SYSTEM);             --是用系统默认的干扰字表



--4.1重新组织全文目录，将那些细小的全文索引物理处理到一个大的完整的全文索引中，以提升性能
alter fulltext catalog cat_production_document
reorganize

--4.2设置全文目录为默认的数据库目录
alter fulltext catalog cat_production_document
as default

--4.3重新建立全文目录中的全部索引
alter fulltext catalog cat_production_document
rebuild with ACCENT_SENSITIVITY = OFF



--5.1.1添加索引字段，
--WITH NO POPULATION表示在表中删除、添加列后，不填充索引
alter fulltext index on production.document
add (title)

--5.1.2删除索引字段
ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENT
DROP (TITLE)


--5.2.1刷新表的每一行的索引
alter fulltext index on production.document
START FULL POPULATION

--5.2.2刷新那些从上次填充后修改的行的索引
alter fulltext index on production.document
START incremental POPULATION

--5.2.3刷新自上次索引更新以来所有的添加、删除、更新的行的索引
alter fulltext index on production.document
START update POPULATION

--5.3.1对于超大的表，全文索引填充会消耗大量系统资源，
--对于更改跟踪设置为manual或者off，通过这个选项可以停止全文索引的填充
alter fulltext index on production.document
STOP POPULATION

--5.3.2通过这个选项可以暂停全文索引的填充
alter fulltext index on production.document
PAUSE POPULATION

--5.3.3通过这个选项可以继续全文索引的填充
alter fulltext index on production.document
RESUME POPULATION


--5.4.1更改跟踪设置,SQL Server不保留对索引数据的更改，
--WITH NO POPULATION表示全文索引建立之后不会填充，直到alter之后才会被填充
ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENT
set CHANGE_TRACKING OFF

--5.4.2通过手动方式填充
ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENT
set CHANGE_TRACKING MANUAL

--5.4.3全文索引会随着表数据的修改而自动更新
ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENT
set CHANGE_TRACKING AUTO


--5.5.1关闭全文索引功能
ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENT
disable

--5.5.2开启全文索引功能
ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENT
enable



--6.1创建干扰词列表
CREATE FULLTEXT STOPLIST WCX;

--6.2增加干扰词
ALTER FULLTEXT STOPLIST WCX
ADD 'SQL' LANGUAGE 'English';

ALTER FULLTEXT STOPLIST WCX
ADD 'SERVER' LANGUAGE 'English'; 



--7.1.1全文目录的元数据  
select name,
       is_default,
       is_accent_sensitivity_on
from sys.fulltext_catalogs

--7.1.2通过函数获取元数据
select FULLTEXTCATALOGPROPERTY('cat_production_document', --全文目录名
                               'AccentSensitivity')       --是否区分重音

select FULLTEXTCATALOGPROPERTY('cat_production_document', --全文目录名
      --当前填充状态,0为空闲,1是正在填充,2是暂停,3是正在生成索引,8为磁盘满
                               'PopulateStatus') 
                               
select FULLTEXTCATALOGPROPERTY('cat_production_document',
                               'IndexSize')      --全文目录大小，单位MB  


--7.2全文索引的元数据
select object_id,                  --全文索引对应的表id
       fulltext_catalog_id,        --全文目录id
       is_enabled,                 --是否可用
       change_tracking_state_desc, --改变跟踪的状态 
       stoplist_id,                --干扰词列表
       data_space_id               --数据所在的空间id
from sys.fulltext_indexes


--7.3全文索引中每个索引列的列名
select fic.object_id,
       fic.column_id,
       c.name            
from sys.fulltext_index_columns fic
inner join sys.columns c
        on fic.object_id = c.object_id
           and fic.column_id = c.column_id
 
--7.4.1可用于全文索引操作的文档类型
select *
from sys.fulltext_document_types 
 
--7.4.2通过存储过程显示:可用于全文索引操作的文档类型
exec sp_help_fulltext_system_components @component_type ='filter'
 

--7.5查看干扰词列表元数据
select *
from sys.fulltext_stoplists

--7.6查看干扰词元数据
select *
from sys.fulltext_stopwords



--8.实例
create table dbo.t
	(vt int identity primary key,
	 v varchar(255) not null,
	 vv varchar(max) not null
	)

--8.1全文目录	
create fulltext catalog wct
as default

--8.2全文索引
create fulltext index on dbo.t
(vv)
key index PK__t__32141381546180BB
with stoplist = wcx

/*===================================================
返回将给定断字符、同义词库和非索引字表组合,
应用于查询字符串输入后生成的最终词语切分结果。
此词语切分结果,等效于全文引擎针对指定查询字符串的输出干扰词.
=====================================================*/
--8.3是否可以被全文引擎识别
select *
from sys.dm_fts_parser
      ('"SQL Server 2008 Transact-SQL Recipes"',  --要搜索的字符串
       1033,             --地区标识符
       5,                --干扰词列表id 
       0)                --是否区分重音


--8.4删除干扰词
--消息10736级别15,状态 2,第2行,全文非索引字表语句必须以分号(;)结尾。
ALTER FULLTEXT STOPLIST WCX
DROP 'SERVER' language 'English';

--8.5要删除干扰词列表,必须把引用干扰词列表的全文索引的干扰词列表设置为系统列表
ALTER FULLTEXT INDEX ON DBO.T
SET STOPLIST SYSTEM;

DROP FULLTEXT STOPLIST WCX;


--8.6删除全文索引
DROP fulltext index on dbo.t

--8.7删除全文目录
DROP FULLTEXT CATALOG wct

基本搜索

--查询基于变形的,字面的,同义的匹配方式搜索全文列
--会返回要搜索文本中包含的单词以及单词的同义词,变形词(复数)的记录
SELECT DOCUMENT,
       DOCUMENTSUMMARY
from production.Document
WHERE FREETEXT (DOCUMENT,        --带全文索引的列名
                'change pedal'); --要搜索的文本


--以精确或模糊的单词和短语匹配，来搜索非结构化文本数据
--当搜索一个单词时,双引号不是必要的,只需要单引号
select *
from production.Document
where contains(DocumentSummary,'"replacing" OR "pedals"')

高级搜索与排名

--1.1使用通配符,*是一个或多个字符的通配符
select Document,
       DocumentSummary
from Production.Document
where contains(DocumentSummary,'"import*" or "re*"')


--1.2相邻查询,near关键字
select Document,
       DocumentSummary
from Production.Document
where contains(DocumentSummary,'oil NEAR grease')

--词oil与,grease和frequency两个词相邻
select Document,
       DocumentSummary
from Production.Document
where contains(DocumentSummary,'oil NEAR grease AND frequency')

/*===================================================
搜索变形形式的语法:

    FORMSOF( [inflectional | thesaurus],simple_term,...

inflectional:指定要对指定的简单字词使用与语言相关的词干分析器
   thesaurus:指定使用对应于列全文语言或指定的查询语言的同义词库
=====================================================*/
--1.3基于变形形式,搜索匹配搜索项的行
select DOCUMENT,
	   DOCUMENTSUMMARY
FROM Production.Document
WHERE contains(DocumentSummary,'FORMSOF(inflectional,replace)')


--1.4基于同义词(分类词汇汇编),搜索匹配搜索项的行
select DOCUMENT,
	   DOCUMENTSUMMARY
FROM Production.Document
WHERE contains(DocumentSummary,'FORMSOF(thesaurus,replace,vital)')


/*==================================================
freetexttable与freetext相比,可以像表一样在from子句中引用,
而且可以用key来关联数据.
freetexttable返回的结果集包含了key和rank两列:

    1.key是定义在全文索引中的唯一键,主键,key只用于关联.
    2.rank是一个行在结果集中正确性的评估值.
=====================================================*/
--1.5根据含义返回排名搜索结果
select f.rank,                   
       Document,
       DocumentSummary

from Production.Document d
inner join freetexttable(Production.Document,  --全文索引的表名
                         documentsummary,      --索引列
                         'bicycle',            --要搜索的字符串
                         LANGUAGE 'English',   --指定语言
                         3)f                   --按照排名的top n匹配
        on d.DocumentID = f.[KEY] --必须写成[key] 
ORDER BY RANK DESC


/*===============================================
isabout为搜索项分配一个权值,
这个值用来影响搜索项在结果排名中的权值:

	    ISABOUT(search_term [weight (值)])
	    
权值范围：0.0~1.0
=================================================*/
--1.6根据权值返回排名搜索结果
select f.rank,
       d.Document,
       d.DocumentSummary
from Production.Document d
inner join containstable(Production.Document,
						 DocumentSummary,
						 'ISABOUT( bicycle weight(.9),
									  seat weight(.1))'
						)f    
        on d.DocumentID = f.[key]
order by RANK desc


--contains中也可以指定权值
select d.Document,
       d.DocumentSummary
from Production.Document d
where contains(DocumentSummary,
			  'ISABOUT( bicycle weight(.9),
						seat weight(.1))')