Explication détaillée de la fonction de recherche en texte intégral de SQL Server-tutoriel mysql-php.cn

La recherche de texte intégral de SQL Server est une fonction de récupération de texte basée sur la segmentation des mots et repose sur l'indexation de texte intégral. L'index de texte intégral est différent de l'index d'arbre équilibré (B-Tree) traditionnel et de l'index de stockage de colonnes. Il est composé d'une table de données, appelée index inversé (Invert Index), et stocke la relation de mappage entre les segments de mots et le. clés uniques des lignes. Les index inversés sont automatiquement créés et gérés par SQL Server lors de la création d'un index de texte intégral ou de la mise à jour d'un index de texte intégral. L'index de texte intégral contient principalement trois analyseurs : le analyseur de mots, le stemmer et l'analyseur de synonymes. Les données stockées dans l'index de texte intégral sont des informations telles que la segmentation des mots et leur position. La segmentation des mots est basée sur les règles grammaticales d'une langue spécifique, recherchant les limites des mots selon des symboles spécifiques et décomposant le texte en « mots ». Chaque mot est appelé segmentation de mot (terme) ; l'index de texte intégral extrait parfois le radical du mot et stocke plusieurs formes dérivées du radical sous la forme d'un radical unique. Ce processus est appelé « radicalisation » et convertit les mots apparentés en synonymes. sur la liste de synonymes personnalisée fournie par l'utilisateur. Ce processus s'appelle extraire des synonymes.

Générer un index de texte intégral consiste à segmenter les données textuelles de la table utilisateur en mots (Word breaker), à extraire les radicaux (Stemmer), à convertir les synonymes (Thesaurus) et à filtrer les mots vides dans la segmentation des mots ( Stopword), et enfin stocker les données traitées dans l'index de texte intégral. Le processus de stockage des données dans des données de texte intégral est appelé processus de remplissage ou d'exploration. La méthode de mise à jour de l'index de texte intégral peut être un remplissage manuel, un remplissage automatique ou un remplissage incrémentiel.

1. Créer un catalogue de texte intégral et un index unique

Avant de créer un index de texte intégral, vous devez créer un catalogue de texte intégral (Le catalogue de texte intégral). catalog est utilisé pour organiser l’index de texte intégral et est le conteneur de l’index de texte intégral. Chaque index de texte intégral doit appartenir à un catalogue de texte intégral. Le catalogue de texte intégral est une structure logique identique au schéma de base de données, quel que soit l'emplacement de stockage de l'index de texte intégral.

create fulltext catalog catalog_test
as default;

Copier après la connexion

Afin de créer un index de texte intégral, un index unique, à une seule colonne et non nullable doit exister sur la table de base, et le moteur de texte intégral utilise cet index Chaque ligne de données dans la table de base est mappé sur une clé d'index unique, et l'index inversé stocke la relation de mappage entre la clé d'index et la segmentation de mots.

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

Copier après la connexion

2. Créer un index de texte intégral

Chaque table ne peut créer qu'un seul index de texte intégral, vous devez prendre en compte le groupe de fichiers stocké dans. l'index de texte intégral et la désactivation de l'association d'index de texte intégral. La liste de mots, la méthode de mise à jour de l'index de texte intégral et la langue associée au texte. La colonne de l'index de texte intégral doit être un champ de texte, par exemple :

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);

Copier après la connexion

1, langue (langue)

l'option langue est facultative, utilisée pour spécifier la langue au niveau de la colonne. La valeur de cette option peut être le nom ou le LCID de. la langue. Si l’option de langue n’est pas spécifiée, la langue par défaut de l’instance SQL Server est utilisée. Affichez les langues prises en charge par le système ainsi que leurs LCID et noms correspondants à partir de la vue système sys.fulltext_linguals (Transact-SQL).

2, catalogue de texte intégral (fulltext_catalog)

l'option fulltext_catalog_name est utilisée pour spécifier le groupe d'index de texte intégral,

3, groupe de fichiers (filegroup)

option filegroup filegroup_name est utilisée pour spécifier le groupe de fichiers dans lequel l'index de texte intégral est stocké. Si aucun groupe de fichiers n'est spécifié, l'index de texte intégral et la table sous-jacente sont stockés dans le même groupe de fichiers. Étant donné que la mise à jour de l'index de texte intégral est une opération gourmande en E/S, afin de mettre à jour l'index de texte intégral plus rapidement, il est préférable de stocker l'index de texte intégral sur un disque dur physique ou un groupe de fichiers différent de la table de base pour obtenir simultanéité maximale des E/S.

4. La méthode de remplissage de l'index de texte intégral

est la même qu'un index ordinaire Lorsque les données de la table sous-jacente sont mises à jour, l'index de texte intégral doit être automatiquement mis à jour. est le comportement par défaut du système. La mise à jour manuelle peut également être configurée ou mettre à jour automatiquement l'index de texte intégral à des intervalles de temps spécifiques.

L'option CHANGE_TRACKING est utilisée pour spécifier si les mises à jour des données (Mise à jour, Supprimer ou Insérer) liées aux colonnes de l'index de texte intégral doivent être synchronisées avec l'index de texte intégral,
•CHANGE_TRACKING = MANUEL : Manuel update
•CHANGE_TRACKING = AUTO : Mise à jour automatique, paramètre par défaut, lorsque les données de la table sous-jacente changent, l'index de texte intégral est automatiquement mis à jour,
•CHANGE_TRACKING =OFF, NO POPULATION : Ne pas mettre à jour, spécifiez l'option NON POPULATION, indiquant qu'après la création de l'index de texte intégral, SQL Server ne mettra pas à jour (remplira) l'index de texte intégral si l'option NO POPULATION n'est pas spécifiée, SQL Server met à jour l'index de texte intégral après avoir créé l'index de texte intégral ; .

5, mot vide (STOPLIST)

Le mot vide (StopWord) est également appelé mot parasite Chaque index de texte intégral sera associé à une liste de mots vides. index Associated est le mot vide du système (liste vide du système). Le moteur de texte intégral supprime les mots vides de la segmentation des mots afin que l'index de texte intégral ne contienne pas de mots vides.

STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }

Copier après la connexion

Trois, remplissez l'index en texte intégral

填充全文索引也叫做爬虫（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;

Copier après la connexion

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

•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);

Copier après la connexion

全文查询跟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 ')

Copier après la connexion

2，前缀查询

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

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

Copier après la connexion

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

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

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

Copier après la connexion

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

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

Copier après la connexion

4，距离查询

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

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

Copier après la connexion

例如：使用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)')

Copier après la connexion

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

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

Copier après la connexion

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

BB one two CC three four five AA

Copier après la connexion

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

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

Copier après la connexion

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