Полнотекстовый поиск (Full-Text Search) в Microsoft SQL Server

Всем привет! Продолжаем изучать возможности SQL Server от компании Microsoft, и на очереди у нас компонент Full-Text Search, в русском варианте это «Полнотекстовый поиск». И сейчас мы узнаем, для чего он нужен, и как же реализовать этот самый полнотекстовый поиск в Microsoft SQL Server, используя этот компонент.

И начнем мы, конечно же, с рассмотрения основ полнотекстового поиска, т.е. что это такое и для чего он вообще нужен.

Полнотекстовый поиск (Full-Text Search)

Что такое полнотекстовый поиск?

Полнотекстовый поиск – это поиск слов или фраз в текстовых данных. Обычно такой вид поиска используется для поиска текста в большом объёме данных, например, таблица с миллионом и более строк, так как он значительно быстрей обычного поиска, который можно осуществить, используя конструкцию LIKE.

Полнотекстовый поиск подразумевает создание специального индекса (он отличается от обычных индексов) текстовых данных, который представляет собой некий словарь слов, которые встречаются в этих данных.

С помощью полнотекстового поиска можно реализовать своего рода поисковую систему документов (т.е. строк), по словам или фразам в базе данных своего предприятия. Так как помимо своей быстрой работы он обладает еще и возможностью ранжировать найденные документы, т.е. выставлять ранг каждой найденной строке, другими словами, можно найти самые релевантные записи, т.е. самые подходящие под Ваш запрос.

Возможности полнотекстового поиска в Microsoft SQL Server

  • В полнотекстовом поиске SQL сервера можно осуществлять поиск не только по отдельным словам или фразам, но и по префиксным выражениям, например, задать текст начала слова или фразы;
  • Также можно искать слова по словоформам, например, различные формы глаголов или существительные в единственном и во множественном числе, т.е. по производным выражениям;
  • Можно построить запрос так, чтобы найти слова или фразы, находящиеся рядом с другими словами или фразами, т.е. выражения с учетом расположения;
  • Есть возможность искать синонимические формы конкретного слова (тезаурус) т.е., например, если в тезаурусе определено, что «Автомобиль» и «Машина» – это синонимы, то при поиске слова «Автомобиль» в результирующий набор войдут и строки, содержащие слово «Машина»;
  • В запросе можно указывать слова или фразы с взвешенными значениями, например, если в запросе указано несколько слов или фраз, то им можно присвоить важность от 0,0 до 1,0 (1,0 означает, что это самое важное слово или фраза);
  • Для того чтобы не учитывать в поиске некоторые слова можно использовать «список стоп-слов» т.е. по словам, включенным в этот список, поиск выполняться не будет.

Подготовка к реализации полнотекстового поиска в Microsoft SQL Server

Перед тем как приступать к созданию полнотекстового поиска, необходимо знать несколько важных моментов:

  • Для реализации полнотекстового поиска компонент Full-Text Search (Полнотекстовый поиск) должен быть установлен;
  • У таблицы может быть только один полнотекстовый индекс;
  • Чтобы создать полнотекстовый индекс, таблица должна содержать один уникальный индекс, который включает один столбец и не допускает значений NULL. Рекомендовано использовать уникальный кластеризованный индекс (или просто первичный ключ), первый столбец которого должен иметь целочисленный тип данных;
  • Полнотекстовый индекс можно создавать на столбцах с типом данных: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary или varbinary(max);
  • Для того чтобы создать полнотекстовый индекс, сначала необходимо создать полнотекстовый каталог. Начиная с SQL Server 2008 полнотекстовый каталог – это логическое понятие, обозначающее группу полнотекстовых индексов, т.е. является виртуальным объектом и не входит в файловую группу (есть способ создания полнотекстового индекса, используя «Мастер», при котором каталог можно создать одновременно с индексом, этот способ мы будем рассматривать чуть ниже).

Установка компонента «Полнотекстовый поиск» в Microsoft SQL Server

Чтобы установить компонент Full-Text Search, необходимо в процессе установки Microsoft SQL Server поставить галочку напротив «FullText and Semantic Extractions for Search».

Скриншот 1

Заметка! Подробно весь процесс установки SQL Server мы рассматривали в статье «Установка Microsoft SQL Server 2019». Реализовывать полнотекстовый поиск мы будем как раз на примере Microsoft SQL Server 2019.

В случае если в процессе установки SQL Server компонент Вы не отметили, то это можно сделать и потом, т.е. доустановить необходимый компонент. Для этого запустите «Центр установки SQL Server», так же, как и в процессе установки SQL Server, выберите «Новая установка изолированного экземпляра SQL Server или добавление компонентов к существующей установке» и на этапе выбора компонентов поставьте соответствующую галочку.

Исходные данные для создания полнотекстового поиска

Итак, компонент полнотекстовый поиск у нас установлен, кроме этого у нас установлена среда SQL Server Management Studio, с помощью которой мы и будем выполнять все действия для создания и управления полнотекстовыми каталогами и индексами.

Допустим, что у нас есть база данных TestDB, а в ней есть таблица TestTable, в которой всего два столбца: первый (Id) – это первичный ключ, а второй (TextData) – это текстовые данные, по которым мы и будем осуществлять полнотекстовый поиск.

Для примера она будет содержать несколько текстовых определений технологий.

   
   --Создание таблицы
   CREATE TABLE TestTable(
     Id INT IDENTITY(1,1) NOT NULL,
     TextData VARCHAR(1000) NULL,
     CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (Id ASC)
   );
   GO
   --Добавление данных в таблицу
   INSERT INTO TestTable(TextData)
     VALUES ('Компонент Database Engine – основная служба для хранения, обработки и защиты данных'),
            ('Службы Integration Services – это платформа для построения решений по интеграции и преобразованию данных уровня предприятия.'),
            ('Службы Reporting Services – это серверная платформа отчетов, предоставляющая возможности для удобной работы с отчетами для разнообразных источников данных.'),
            ('Репликация представляет собой набор технологий копирования и распространения данных и объектов баз данных между базами данных, а также синхронизации баз данных для поддержания согласованности.'),
            ('Компонент SQL Server Service Broker обеспечивает собственную поддержку компонента SQL Server Database Engine для приложений обмена сообщениями и приложений с очередями сообщений.'),
            ('SQL – язык структурированных запросов, применяемый для создания, модификации и управления данными в произвольной реляционной базе данных.'),
            ('C++ – язык программирования общего назначения, который поддерживает как процедурное, так и объектно-ориентированное программирование.'),
            ('Transact-SQL – язык программирования, процедурное расширение языка SQL, разработанное компанией Microsoft.'),
            ('PL/SQL – язык программирования, процедурное расширение языка SQL, разработанное корпорацией Oracle.'),
            ('Microsoft Visual Basic – интегрированная среда разработки программного обеспечения, разработанная корпорацией Microsoft.'),
            ('C# – объектно-ориентированный язык программирования. Разработанный компанией Microsoft как язык разработки приложений для платформы Microsoft .NET Framework.');
   GO
   --Выборка данных
   SELECT * FROM TestTable;

Скриншот 2

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

Создание полнотекстового каталога в SQL Server

Для создания полнотекстового каталога, как, впрочем, и индекса, можно использовать или графический интерфейс SSMS, или инструкции T-SQL, мы с Вами разберем оба способа.

Создание полнотекстового каталога на T-SQL

   
   --Создание полнотекстового каталога
   CREATE FULLTEXT CATALOG TestCatalog
     WITH ACCENT_SENSITIVITY = ON
     AS DEFAULT
     AUTHORIZATION dbo
   GO

Где,

  • CREATE FULLTEXT CATALOG – команда создания полнотекстового каталога;
  • TestCatalog – имя нашего полнотекстового каталога;
  • WITH ACCENT_SENSITIVITY {ON|OFF} – опция указывает, будет ли полнотекстовый каталог учитывать диакритические знаки для полнотекстового индексирования. По умолчанию ON;
  • AS DEFAULT – опция, для того чтобы указать, что каталог является каталогом по умолчанию. В случае создания полнотекстового индекса без явного указания каталога используется каталог по умолчанию;
  • AUTHORIZATION dbo – устанавливает владельца полнотекстового каталога, им может быть пользователь или роль базы данных. В данном случае мы указали роль dbo.

Создание полнотекстового каталога в графическом интерфейсе Management Studio

Точно такой же полнотекстовый каталог можно создать и в графическом интерфейсе Management Studio. Для этого открываем базу данных, переходим в контейнер «Хранилище –> Полнотекстовые каталоги», щелкаем правой кнопкой мыши по данному пункту и выбираем «Создать полнотекстовый каталог».

Скриншот 3

Откроется окно создания каталога, где мы указываем название каталога и его опции. Нажимаем «ОК».

Скриншот 4

Удаление и изменение полнотекстового каталога в SQL Server

Для изменения параметров каталога можно использовать инструкцию ALTER FULLTEXT CATALOG, например, давайте сделаем так, чтобы наш каталог перестал учитывать диакритические знаки, для этого пишем SQL инструкцию, которая перестроит наш каталог с новым значением параметра.

   
   --Изменение полнотекстового каталога
   ALTER FULLTEXT CATALOG TestCatalog
     REBUILD WITH ACCENT_SENSITIVITY=OFF
   GO

Возвращаем назад.

   
   ALTER FULLTEXT CATALOG TestCatalog
     REBUILD WITH ACCENT_SENSITIVITY=ON
   GO

В случае необходимости удалить полнотекстовый каталог, мы можем использовать инструкцию DROP FULLTEXT CATALOG.

   
   DROP FULLTEXT CATALOG TestCatalog;

Все это можно было сделать и в графическом интерфейсе Management Studio (для изменения параметров каталога «Свойства», для удаления «Удалить»).

Скриншот 5

Создание полнотекстового индекса в Microsoft SQL Server

После того как полнотекстовый каталог мы создали, мы можем переходить к созданию полнотекстового индекса. В нашем случае для столбца TextData нашей тестовой таблицы.

Создание полнотекстового индекса на T-SQL

Для того чтобы создать полнотекстовый индекс, мы пишем инструкцию CREATE FULLTEXT INDEX.

   
   --Создание полнотекстового индекса
   CREATE FULLTEXT INDEX ON TestTable(TextData)
     KEY INDEX PK_TestTable ON (TestCatalog)
     WITH (CHANGE_TRACKING AUTO)
   GO

Где

  • CREATE FULLTEXT INDEX – команда создания полнотекстового индекса;
  • TestTable(TextData) – таблица и столбец, включенные в индекс;
  • KEY INDEX PK_TestTable – имя уникального индекса таблицы TestTable;
  • ON (TestCatalog) – указываем, что полнотекстовый индекс будет создан в полнотекстовом каталоге TestCatalog. Если не указать этот параметр, то индекс будет создан в полнотекстовом каталоге по умолчанию;
  • WITH (CHANGE_TRACKING AUTO) – это мы говорим, что все изменения, которые будут вноситься в базовую таблицу (TestTable), автоматически отобразятся и в нашем полнотекстовом индексе, т.е. автоматическое заполнение.

Заметка! Подробно про все типы индексов мы разговаривали в материале – Основы индексов в Microsoft SQL Server.

Создание полнотекстового индекса в графическом интерфейсе Management Studio

Полнотекстовый индекс можно создать, используя и графические инструменты, для этого открываем свойства полнотекстового каталога и переходим в пункт «Таблицы или представления», выбираем нужную таблицу, столбец, уникальный индекс и способ отслеживания изменений. В нашем случае у нас всего одна доступная таблица и один столбец. Нажимаем «ОК».

Скриншот 6

Изменение и удаление полнотекстового индекса

В случае необходимости можно изменить параметры полнотекстового индекса. Давайте в качестве примера, изменим способ отслеживания изменений с автоматического на ручной. Для изменения в графическом интерфейсе можно использовать окно «Свойства полнотекстового каталога -> Таблицы или представления», которое мы использовали при создании полнотекстового индекса.

Или можно написать следующий код.

   
   --Изменение полнотекстового индекса
   ALTER FULLTEXT INDEX ON TestTable
     SET CHANGE_TRACKING = MANUAL

Чтобы вернуть назад, используйте значение параметра AUTO.

   
   ALTER FULLTEXT INDEX ON TestTable
     SET CHANGE_TRACKING = AUTO

Для того чтобы удалить полнотекстовый индекс, достаточно просто удалить таблицу из списка объектов, связанных с полнотекстовым каталогом в том же окне «Свойства полнотекстового каталога -> Таблицы или представления»

Скриншот 7

Или написать код T-SQL

   
   --Удаление полнотекстового индекса
   DROP FULLTEXT INDEX ON TestTable;

Создание полнотекстового каталога и индекса с помощью мастера

Как я уже упоминал ранее, полнотекстовый каталог и индекс можно создать, используя мастер, т.е. по шагам, для этого щелкаем правой кнопкой мыши по таблице, которую мы хотим включить в полнотекстовый поиск, и выбираем «Полнотекстовый поиск -> Определить полнотекстовый индекс».

Примечание! Перед этим я удалил и индекс, и каталог, которые мы создавали в предыдущих примерах.

Скриншот 8

В итоге запустится мастер полнотекстового индексирования SQL Server, нажимаем «Далее».

Скриншот 9

Далее выбираем уникальный индекс и нажимаем «Далее».

Скриншот 10

Затем столбец, который будет включен в полнотекстовый индекс, нажимаем «Далее».

Скриншот 11

Потом необходимо выбрать способ отслеживания изменений. Нажимаем «Далее».

Скриншот 12

Указываем название каталога и его опции, для того чтобы его создать, так как предполагается, что у нас каталога нет, если бы он был, то мы могли бы его выбрать. Нажимаем «Далее».

Скриншот 13

Для создания каталога и индекса осталось нажать «Готово».

Скриншот 14

В следующем окне мы увидим результат выполнения операций по созданию полнотекстового каталога и индекса. В моем случае все прошло успешно. Нажимаем «Закрыть».

Скриншот 15

Таким образом, мы выполнили создание полнотекстового каталога и индекса одновременно с помощью мастера.

Примеры полнотекстовых запросов

Более подробно полнотекстовые запросы мы будем рассматривать в отдельном материале, а пока, в качестве примера и подтверждения того, что наш полнотекстовый поиск работает, давайте напишем пару простых полнотекстовых запросов.

Если помните, наша таблица TestTable содержит определения технологий, языков программирования, в общем, определений, связанных со сферой IT. Допустим, что мы хотим получить все записи, где есть упоминание о компании Microsoft, для этого мы пишем полнотекстовый запрос с ключевым словом CONTAINS, например

   
   SELECT Id, TextData
   FROM TestTable
   WHERE CONTAINS (TextData, 'Microsoft');

Скриншот 16

Мы получили результат, но допустим, нам также необходимо отсортировать его по релевантности, другими словами, какие строки больше соответствуют нашему запросу. Для этого мы будем использовать функцию CONTAINSTABLE, которая проставляет ранг для каждой найденной записи.

   
   SELECT Table1.Id AS [Id],
          RowRank.Rank AS [RANK],
          Table1.TextData AS [TextData]
   FROM TestTable AS Table1
   INNER JOIN CONTAINSTABLE(TestTable, TextData, 'Microsoft') AS RowRank
   ON Table1.Id=RowRank.[KEY]
   ORDER BY RowRank.RANK DESC;

Скриншот 17

Как видим, ранг проставлен и по нему отсортированы строки. Сам алгоритм ранжирования, как и более подробную информацию о полнотекстовом поиске, можно найти в электронной документации по SQL Server.

Заметка! Еще больше статей по Microsoft SQL Server и языку TSQL Вы можете найти в специальном сборнике статей – Сборник статей для изучения Microsoft SQL Server.

На этом предлагаю завершить, надеюсь, все было понятно, удачи!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Комментарии: 1
  1. Константин

    Добрый день! Зависит ли работа полнотекстового поиска от языковой версии windows и sql? Например, если винда и сиквел анлийские, будет ли работать этот поиск в БД с данными на русском языке?

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.