Detailed explanation of the full-text search function of SQL Server

SQL Server's Full-Text Search is a text retrieval function based on word segmentation and relies on full-text indexing. The full-text index is different from the traditional balanced tree (B-Tree) index and column storage index. It is composed of a data table, called an invert index (Invert Index), and stores the mapping relationship between word segments and the unique keys of the rows. Inverted indexes are automatically created and maintained by SQL Server when creating a full-text index or updating a full-text index. The full-text index mainly contains three analyzers: word breaker, stemmer and synonym analyzer. The data stored in the full-text index is information such as word segmentation and its position. Word segmentation is based on the grammatical rules of a specific language, looking for word boundaries according to specific symbols, and decomposing the text into "words". Each word is called a word segmentation (term); The full-text index sometimes extracts the stem of the word segmentation and stores multiple derived forms of the stem as a single stem. This process is called stemming; it converts related words into synonyms based on the custom synonym list provided by the user. This process It's called extracting synonyms.

Generating a full-text index is to segment the text data in the user table into words (Word breaker), extract stems (Stemmer), convert synonyms (Thesaurus), and filter out stop words ( Stopword), and finally store the processed data in the full-text index. The process of storing data into full-text data is called the Populate or Crawl process. The update method of the full-text index can be manual filling, automatic filling, or incremental filling.

1. Create a full-text catalog and unique index

Before creating a full-text index, you must create a full-text catalog (Full-Text Catalog). The full-text catalog is used to organize the full-text index and is the container of the full-text index. Every full-text index must belong to a full-text catalog. The full-text catalog is a logical structure, which is the same as the database schema, regardless of the storage location of the full-text index.

create fulltext catalog catalog_test
as default;

In order to create a full-text index, a unique, single-column, non-nullable index must exist on the basic table. The full-text engine uses this index to Each row of data on the table is mapped to a unique index key, and the inverted index stores the mapping relationship between the index key and the word segmentation.

create unique index uidx_dbLogID 
on [dbo].[DatabaseLog]
([DatabaseLogID]);

2. Create a full-text index

Each table can only create one full-text index. When creating a full-text index, you must consider the file group stored in the full-text index and the stop word list associated with the full-text index. , the update method of the full-text index, and the language associated with the text. The full-text index column must be a text field, for example:

create fulltext index 
on [dbo].[DatabaseLog]
(
[tsql] language 1033
)
key index ui_dbLogID
on (catalog_test,filegroup [primary]) 
with(change_tracking=off ,no population ,stoplist=system);

1, language (language)

option language is optional, Used to specify the column-level language. The value of this option can be the name or LCID of the language. If the language option is not specified, the default language of the SQL Server instance is used. View the languages ​​supported by the system and their corresponding LCIDs and names from the system view sys.fulltext_languages ​​(Transact-SQL).

2, Full-text catalog (fulltext_catalog)

Option fulltext_catalog_name is used to specify the group of full-text index,

3, File group (filegroup)

Option filegroup filegroup_name is used to specify the file group where the full-text index is stored. If no file group is specified, the full-text index and the underlying table are stored in the same file group. Since updating the full-text index is an IO-intensive operation, in order to update the full-text index faster, it is best to store the full-text index on a physical hard disk or file group different from the base table to achieve maximum IO concurrency.

4. The method of filling the full-text index is the same as the ordinary index. When the basic table data is updated, the full-text index must be automatically updated. This is the system's default behavior. Manual update can also be configured. Full-text index, or automatically update the full-text index at specific time intervals.

Option CHANGE_TRACKING is used to specify whether data updates (Update, Delete, or Insert) related to full-text index columns need to be synchronized to the full-text index,

•CHANGE_TRACKING = MANUAL: manual update

•CHANGE_TRACKING = AUTO: Automatically update, default setting, when the underlying table data changes, the full-text index is automatically updated,
•CHANGE_TRACKING =OFF, NO POPULATION: Do not update, specify the option NO POPULATION, indicating that after creating the full-text index, SQL Server will not Update (populate) the full-text index; if the NO POPULATION option is not specified, SQL Server updates the full-text index after creating the full-text index.

5, stop words (STOPLIST)

Stop words (StopWord) are also called noise words. Each full-text index is associated with a stop word list. By default, the full-text index Associated is the system stopword (system stoplist). The full-text engine removes stop words from the word segmentation so that the full-text index does not contain stop words.

STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

Three, fill in the full-text index

填充全文索引也叫做爬虫（crawl）进程，或填充（Population）进程。由于创建或填充全文索引会消耗大量的系统（IO、内存）资源，因此尽量选择在系统空闲时对全文索引进行填充。在创建全文索引时，通过指定选项 CHANGE_TRACKING= MANUAL，或 CHANGE_TRACKING= OFF, NO POPULATION，新建的全文索引不会立即填充，用户可以选择在系统空闲时，使用 alter fulltext index 语句执行填充操作。只有填充全文索引之后，全文索引才包含基础表的分词数据。

alter fulltext index 
on table_name
start { full | incremental | update } population;

更新全文索引有三种方式：

•FULL POPULATION：全部填充，从基础表中获取每一行，重新编入全文索引；
•INCREMENTAL POPULATION：增量填充，前提是基础表中包含timestamp字段，从上一次填充之后，只把更新之后的数据编入全文索引；
•UPDATE POPULATION：更新填充，从上一次填充之后执行更新（insert、update、或delete）操作的数据行重新编入索引；

在创建全文索引时，如果指定CHANGE_TRACKING=AUTO   或   CHANGE_TRACKING=  OFF ， 那么新建的全文索引会立即开始填充进程。

四，使用 contains 谓词查询全文索引

如果想要在查询中使用全文索引，通常使用CONTAINS谓词来调用全文索引，实现比LIKE关键字更复杂的文本匹配查询，而LIKE关键字是模糊匹配，不会调用全文索引。

例如，利用contains谓词执行单个分词的完全匹配查询：

select [tsql] 
from [dbo].[DatabaseLog] 
where contains([tsql], 'searchword', language 1033);

全文查询跟Like相比，速度更快，支持的搜索功能更复杂，使用contains谓词，不仅能够执行分词的完全匹配或分词的前缀匹配查询，还能够执行基于词根的查询，基于自定义同义词的查询，基于距离和顺序的相邻分词查询。但是，和Like 相比，contains谓词不能进行后缀匹配查询。

contains谓词返回的结果是布尔值，如果全文索引列中包含指定的关键字或查找模式（pattern），返回TRUE；否则，返回FALSE。

contains谓词支持word查询和短语查询，word是指单个分词，短语（phrase）是由多个word和间隔的空格组成的，对于短语，必须使用双引号，将多个word组成一个短语。

1，逻辑组合查询

使用and ，and not， 或 or 逻辑运算符 匹配多个word 或 多个phrase

CONTAINS(Name, '"Mountain" OR "Road" ')
CONTAINS(Name, ' Mountain OR Road ')

2，前缀查询

使用contains谓词进行前缀匹配，和like 'prefix%'功能相同，只不过contains谓词使用“*”作为通配符，“*”匹配0，1或多个字符，前缀匹配的写法是：'"prefix*"'，全文索引只能执行前缀匹配。

CONTAINS(Name, ' "Chain*" ')
CONTAINS(Name, '"chain*" OR "full*"')

3，查询同义词（thesaurus）或词干（stemmer）

Stemmer（词干），例如，根据语法规程，英语的动词 根据数（单数，复数），人称，时态的不同而存在不同的变化形式，这些单词都是同源的。

CONTAINS(Description, ' FORMSOF (INFLECTIONAL, ride) ')

THESAURUS （同义词），需要导入XML进行配置，SQL Server 提供一个默认的Thesaurus file，是Empty的。如果在Thesaurus file 配置“Author”，“Writer”，“journalist” 是同义词，在使用fulltext index查询时，只要满足任意一个同义词，都匹配成功。

CONTAINS(Description, ' FORMSOF (THESAURUS, author) ')

4，距离查询

使用 near 函数，查询匹配相邻分词的数据行，near函数的定义如下，用于需要在查询模式中指定距离查询的查询模式：

NEAR ( ( { <simple_term> | <prefix_term> } [ ,…n ] ) [, <maximum_distance> ] [, <match_order> ] )

例如：使用Near 函数指定相邻分词的距离和匹配顺序，near((term1,term2，term3),5)表示任意两个term之间的距离不能超过5, near((term1,term2，term3),5,true)，表示任意两个term的距离不能超过5，并且按照 term1，term2，term3的顺序存在于字符串中。

--regardless of the intervening distance and regardless of order
CONTAINS(column_name, 'NEAR(term1,"term3 term4")')
--searches for "AA" and "BB", in either order, within a maximum distance of five
CONTAINS(column_name, 'NEAR((AA,BB),5)')
--in the specified order with regardless of the distance
CONTAINS(column_name, 'NEAR ((Monday, Tuesday, Wednesday), MAX, TRUE)')

对于 near((term1,term2,term3),5,true)，term1 和 term5之间最多存在5个term，不包括内部的搜索分词，“term2”，例如：

CONTAINS(column_name, 'NEAR((AA,BB,CC),5)')

这个查询会匹配下面的文本，注意，内部的搜索分词CC没有计算距离：

BB one two CC three four five AA

例如，在原文本中，分词bike和control的最大距离不能超过10，分词bike必须出现在分词control的前面：

CONTAINS(Comments , 'NEAR((bike,control), 10, TRUE)')

SQL Server提供的全文搜索功能，比LIKE关键字丰富，具备初级的全文搜索功能，速度快，维护简单，缺点是，全文搜索功能非常有限，在实际的开发中，可以配合开源的全文搜索引擎，例如，Solr，Elasticsearch等来开发功能更强大的全文搜索功能。

相关推荐：

PlateSpin备份时SQL Server的信息介绍

Python操作SQL Server数据库的方法

SQL server 数据库的数据完整性

The above is the detailed content of Detailed explanation of the full-text search function of SQL Server. For more information, please follow other related articles on the PHP Chinese website!