【SQL Server学习笔记】全文检索-白红宇

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

阅读量：5259 次

发布时间：2019-06-14

本文共 7700 字，大约阅读时间需要 25 分钟。

全文检索提供了基于字符串、二进制数据的智能单词短语的搜索，使得非结构化数据的搜索更为灵活，而且全文检索的执行效率比普通的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 adventureworksset compatibility_level = 100use AdventureWorksgo--2.1创建全文目录create fulltext catalog cat_production_documentgocreate 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_documentreorganize--4.2设置全文目录为默认的数据库目录alter fulltext catalog cat_production_documentas default--4.3重新建立全文目录中的全部索引alter fulltext catalog cat_production_documentrebuild with ACCENT_SENSITIVITY = OFF--5.1.1添加索引字段，--WITH NO POPULATION表示在表中删除、添加列后，不填充索引alter fulltext index on production.documentadd (title)--5.1.2删除索引字段ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENTDROP (TITLE)--5.2.1刷新表的每一行的索引alter fulltext index on production.documentSTART FULL POPULATION--5.2.2刷新那些从上次填充后修改的行的索引alter fulltext index on production.documentSTART incremental POPULATION--5.2.3刷新自上次索引更新以来所有的添加、删除、更新的行的索引alter fulltext index on production.documentSTART update POPULATION--5.3.1对于超大的表，全文索引填充会消耗大量系统资源，--对于更改跟踪设置为manual或者off，通过这个选项可以停止全文索引的填充alter fulltext index on production.documentSTOP POPULATION--5.3.2通过这个选项可以暂停全文索引的填充alter fulltext index on production.documentPAUSE POPULATION--5.3.3通过这个选项可以继续全文索引的填充alter fulltext index on production.documentRESUME POPULATION--5.4.1更改跟踪设置,SQL Server不保留对索引数据的更改，--WITH NO POPULATION表示全文索引建立之后不会填充，直到alter之后才会被填充ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENTset CHANGE_TRACKING OFF--5.4.2通过手动方式填充ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENTset CHANGE_TRACKING MANUAL--5.4.3全文索引会随着表数据的修改而自动更新ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENTset CHANGE_TRACKING AUTO--5.5.1关闭全文索引功能ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENTdisable--5.5.2开启全文索引功能ALTER FULLTEXT INDEX ON PRODUCTION.DOCUMENTenable--6.1创建干扰词列表CREATE FULLTEXT STOPLIST WCX;--6.2增加干扰词ALTER FULLTEXT STOPLIST WCXADD 'SQL' LANGUAGE 'English';ALTER FULLTEXT STOPLIST WCXADD 'SERVER' LANGUAGE 'English'; --7.1.1全文目录的元数据  select name,       is_default,       is_accent_sensitivity_onfrom 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               --数据所在的空间idfrom sys.fulltext_indexes--7.3全文索引中每个索引列的列名select fic.object_id,       fic.column_id,       c.name            from sys.fulltext_index_columns ficinner 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 wctas default--8.2全文索引create fulltext index on dbo.t(vv)key index PK__t__32141381546180BBwith 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 WCXDROP 'SERVER' language 'English';--8.5要删除干扰词列表,必须把引用干扰词列表的全文索引的干扰词列表设置为系统列表ALTER FULLTEXT INDEX ON DBO.TSET STOPLIST SYSTEM;DROP FULLTEXT STOPLIST WCX;--8.6删除全文索引DROP fulltext index on dbo.t--8.7删除全文目录DROP FULLTEXT CATALOG wct

基本搜索

--查询基于变形的,字面的,同义的匹配方式搜索全文列--会返回要搜索文本中包含的单词以及单词的同义词,变形词(复数)的记录SELECT DOCUMENT,       DOCUMENTSUMMARYfrom production.DocumentWHERE FREETEXT (DOCUMENT,        --带全文索引的列名                'change pedal'); --要搜索的文本--以精确或模糊的单词和短语匹配，来搜索非结构化文本数据--当搜索一个单词时,双引号不是必要的,只需要单引号select *from production.Documentwhere contains(DocumentSummary,'"replacing" OR "pedals"')

高级搜索与排名

--1.1使用通配符,*是一个或多个字符的通配符select Document,       DocumentSummaryfrom Production.Documentwhere contains(DocumentSummary,'"import*" or "re*"')--1.2相邻查询,near关键字select Document,       DocumentSummaryfrom Production.Documentwhere contains(DocumentSummary,'oil NEAR grease')--词oil与,grease和frequency两个词相邻select Document,       DocumentSummaryfrom Production.Documentwhere contains(DocumentSummary,'oil NEAR grease AND frequency')/*===================================================搜索变形形式的语法:    FORMSOF( [inflectional | thesaurus],simple_term,...inflectional:指定要对指定的简单字词使用与语言相关的词干分析器   thesaurus:指定使用对应于列全文语言或指定的查询语言的同义词库=====================================================*/--1.3基于变形形式,搜索匹配搜索项的行select DOCUMENT,	   DOCUMENTSUMMARYFROM Production.DocumentWHERE contains(DocumentSummary,'FORMSOF(inflectional,replace)')--1.4基于同义词(分类词汇汇编),搜索匹配搜索项的行select DOCUMENT,	   DOCUMENTSUMMARYFROM Production.DocumentWHERE contains(DocumentSummary,'FORMSOF(thesaurus,replace,vital)')/*==================================================freetexttable与freetext相比,可以像表一样在from子句中引用,而且可以用key来关联数据.freetexttable返回的结果集包含了key和rank两列:    1.key是定义在全文索引中的唯一键,主键,key只用于关联.    2.rank是一个行在结果集中正确性的评估值.=====================================================*/--1.5根据含义返回排名搜索结果select f.rank,                          Document,       DocumentSummaryfrom Production.Document dinner 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.DocumentSummaryfrom Production.Document dinner 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.DocumentSummaryfrom Production.Document dwhere contains(DocumentSummary,			  'ISABOUT( bicycle weight(.9),						seat weight(.1))')

转载于:https://www.cnblogs.com/momogua/archive/2012/07/27/8304626.html

你可能感兴趣的文章