Оффлайн
- Регистрация
- 25.01.17
- Сообщения
- 763
- Реакции
- 224
- Репутация
- 292
Продолжаем изучать возможности SQL Server от компании Microsoft и на очереди у нас компонент Full-Text Search, в русском варианте это «Полнотекстовый поиск» и сейчас мы узнаем, для чего он нужен, и как же реализовать этот самый полнотекстовый поиск в SQL сервере, используя этот компонент.
И начнем мы, конечно же, с рассмотрения основ полнотекстового поиска, т.е. что это такое и для чего он вообще нужен.
Что такое полнотекстовый поиск?
Полнотекстовый поиск – это поиск слов или фраз в текстовых данных. Обычно такой вид поиска используется для поиска текста в большом объёме данных, например, таблица с миллионом и более строк, так как он значительно быстрей обычного поиска, который можно осуществить, используя конструкцию LIKE.
Полнотекстовый поиск подразумевает создание специального индекса (он отличается от обычных индексов) текстовых данных, который представляет собой некий словарь слов, которые встречаются в этих данных.
С помощью полнотекстового поиска можно реализовать своего рода поисковую систему документов (т.е. строк), по словам или фразам в базе данных своего предприятия. Так как помимо своей быстрой работы он обладает еще и возможностью ранжировать найденные документы, т.е. выставлять ранг каждой найденной строке другими словами можно найти самые релевантные записи, т.е. самые подходящие под Ваш запрос.
Возможности полнотекстового поиска в MS SQL Server
Перед тем как приступать к созданию полнотекстового поиска, необходимо знать несколько важных моментов:
В примерах ниже в качестве инструмента создания и управления полнотекстовыми каталогами и индексами я буду использовать SQL Server Management Studio.
Исходные данные для создания полнотекстового поиска
Допустим, что у нас есть база данных TestBase, а в ней есть таблица TestTable в которой всего два поля первое (id) это первичный ключ, а второе (textdata) это текстовые данные по которым мы и будем осуществлять полнотекстовый поиск.
CREATE TABLE TestTable(
id int IDENTITY(1,1) NOT NULL,
textdata varchar(500) NULL,
CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (id ASC)
)
Для примера она будет содержать следующие данные
Создание полнотекстового каталога в SQL Server
Для создания полнотекстового каталога как впрочем, и индекса можно использовать или графический интерфейс SSMS или инструкций
Создание полнотекстового каталога на T-SQL
CREATE FULLTEXT CATALOG TestCatalog
WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION dbo
GO
Где,
Точно такой же полнотекстовый каталог можно создать и в графическом интерфейсе Management Studio. Для этого открываем базу данных, переходим в папкуХранилище ->Полнотекстовые каталоги, щелкаем правой кнопкой мыши по данному пункту и выбираем «Создать полнотекстовый каталог»
Откроется окно создания каталога, где мы указываем название каталога и его опции
Изменение и удаление полнотекстового каталога в SQL Server
Для изменения опций каталога можно использовать инструкцию ALTER FULLTEXT CATALOG, например, давайте сделаем так, чтобы наш каталог перестал учитывать диакритические знаки, для этого пишем SQL инструкцию, которая перестроит наш каталог с новой опцией
ALTER FULLTEXT CATALOG TestCatalog
REBUILD WITH ACCENT_SENSITIVITY=OFF
GO
Для того чтобы удалить каталог можно использовать инструкцию T-SQL, например
DROP FULLTEXT CATALOG TestCatalog
Все это можно было сделать и в графическом интерфейсе Management Studio (для изменения параметров каталога «Свойства», для удаления «Удалить»)
Создание полнотекстового индекса в SQL Server
После создания полнотекстового каталога можно начинать создавать в нем полнотекстовые индекса. В нашем случае мы хотим создать полнотекстовый индекс, в котором участвует поле textdata таблицы TestTable.
Создание полнотекстового индекса на T-SQL
Для того чтобы создать полнотекстовый индекс можно написать следующую SQL инструкцию
CREATE FULLTEXT INDEX ON TestTable(textdata)
KEY INDEX PK_TestTable ON (TestCatalog)
WITH (CHANGE_TRACKING AUTO)
GO
Где,
Полнотекстовый индекс можно создать, используя и графические инструменты, для этого открываем свойства полнотекстового каталога и переходим в пункт «Таблицы или представления», выбираем нужную таблицу, поле, уникальный индекс и способ отслеживания изменений. В нашем случае у нас всего одна доступная таблица и одно поле.
Изменение и удаление полнотекстового индекса
В случае необходимости можно изменить параметры полнотекстового индекса. Давайте в качестве примера, изменим способ отслеживания изменений с автоматического на ручной. Для изменения в графическом интерфейсе можно использовать окно «Свойства полнотекстового каталога -> Таблицы или представления», которое мы использовали при создании полнотекстового индекса.
Или можно написать следующий код
ALTER FULLTEXT INDEX ON TestTable
SET CHANGE_TRACKING = MANUAL
Для того чтобы удалить полнотекстовый индекс достаточно просто удалить таблицу из списка объектов связанных с полнотекстовым каталогом в том же окне «Свойства полнотекстового каталога -> Таблицы или представления»
Или написать код T-SQL
DROP FULLTEXT INDEX ON TestTable
Создание полнотекстового каталога и индекса с помощью мастера
Как я уже упоминал ранее полнотекстовый каталог и индекс можно создать, используя мастер, т.е. по шагам, для этого щелкаем правой кнопкой мыши по таблице, которую мы хотим включить в полнотекстовый поиск, и выбираем «Полнотекстовый индекс ->Определить полнотекстовый индекс»
Примечание!Перед этим я удалил и каталог, и индекс которые мы создавали в предыдущих примерах.
В итоге запустится мастер полнотекстового индексирования SQL Server
Далее выбираем уникальный индекс
Затем столбец, который будет включен в полнотекстовый индекс
Потом необходимо выбрать способ отслеживания изменений
Указываем название каталога и его опции, для того чтобы его создать, так как предполагается, что у нас каталога нет, если бы он был, то мы могли бы его выбрать
Здесь мы можем настроить расписание заполнения полнотекстового каталога
Для создания каталога и индекса осталось нажать «Готово»
В следующем окне мы увидим результат выполнения операций по созданию полнотекстового каталога и индекса. В моем случае все прошло успешно.
Таким образом, мы выполнили создание полнотекстового каталога и индекса одновременно с помощью мастера.
Примеры полнотекстовых запросов
Сразу скажу, что более подробно полнотекстовые запросы мы будем рассматривать в следующих материалах, а пока, в качестве примера и подтверждения того, что наш полнотекстовый поиск работает, давайте напишем пару простых полнотекстовых запросов.
Если помните, наша таблица TestTable содержит определения технологий, языков программирования, в общем, определений связанных со сферой IT. Допустим, что мы хотим получить все записи, где есть упоминание о компании Microsoft, для этого мы пишем полнотекстовый запрос с ключевым словом CONTAINS, например:
select * from TestTable
where CONTAINS (textdata, '"Microsoft"')
Мы получили результат, но допустим, нам также необходимо отсортировать его по релевантности другими словами какие строки больше соответствуют нашему запросу. Для этого мы будем использовать функцию CONTAINSTABLE, которая проставляет ранг для каждой найденной записи.
SELECT Table1.id AS ID,
RowRank.Rank as [RANK], Table1.textdata as [TEXTDATA]
FROM TestTable Table1
INNER JOIN CONTAINSTABLE(TestTable, textdata, '"Microsoft"') as RowRank
on Table1.id=RowRank.[KEY]
ORDER BY RowRank.RANK DESC
Как видим, ранг проставлен и по нему отсортированы строки. Сам алгоритм ранжирования, как и более подробную информацию о полнотекстовом поиске можно найти в электронной документации по SQL Server.
На этом предлагаю заканчивать, надеюсь, все было понятно, удачи!
И начнем мы, конечно же, с рассмотрения основ полнотекстового поиска, т.е. что это такое и для чего он вообще нужен.
Что такое полнотекстовый поиск?
Полнотекстовый поиск – это поиск слов или фраз в текстовых данных. Обычно такой вид поиска используется для поиска текста в большом объёме данных, например, таблица с миллионом и более строк, так как он значительно быстрей обычного поиска, который можно осуществить, используя конструкцию LIKE.
Полнотекстовый поиск подразумевает создание специального индекса (он отличается от обычных индексов) текстовых данных, который представляет собой некий словарь слов, которые встречаются в этих данных.
С помощью полнотекстового поиска можно реализовать своего рода поисковую систему документов (т.е. строк), по словам или фразам в базе данных своего предприятия. Так как помимо своей быстрой работы он обладает еще и возможностью ранжировать найденные документы, т.е. выставлять ранг каждой найденной строке другими словами можно найти самые релевантные записи, т.е. самые подходящие под Ваш запрос.
Возможности полнотекстового поиска в MS SQL Server
- В полнотекстовом поиске SQL сервера можно осуществлять поиск не только по отдельным словам или фразам, но и по префиксным выражениям, например, задать текст начала слова или фразы;
- Также можно искать слова по словоформам, например, различные формы глаголов или существительные в единственном и во множественном числе, т.е. по производным выражениям;
- Можно построить запрос так чтобы найти слова или фразы, находящиеся рядом с другими словами или фразами, т.е. выражения с учетом расположения;
- Есть возможность искать синонимические формы конкретного слова (тезаурус) т.е. например, если в тезаурусе определено что «Автомобиль» и «Машина» это синонимы, то при поиске слова «Автомобиль» в результирующий набор войдут и строки содержащие слово «Машина»;
- В запросе можно указывать слова или фразы с взвешенными значениями, например, если в запросе указано несколько слов или фраз, то им можно присвоить важность от 0,0 до 1,0 (1,0 означает что это самое важное слово или фраза);
- Для того чтобы не учитывать в поиске некоторые слова можно использовать «список стоп-слов» т.е. по словам, включенным в этот список, поиск выполняться не будет.
Перед тем как приступать к созданию полнотекстового поиска, необходимо знать несколько важных моментов:
- Для реализации полнотекстового поиска компонент Full-Text Search (Полнотекстовый поиск) должен быть установлен;
- У таблицы может быть только один полнотекстовый индекс;
- Чтобы создать полнотекстовый индекс, таблица должна содержать один уникальный индекс, который включает один столбец и не допускает значений NULL. Рекомендовано использовать уникальный кластеризованный индекс (или просто первичный ключ) первый столбец которого должен иметь целочисленный тип данных;
- Полнотекстовый индекс можно создавать на столбцах с типом данных: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary или varbinary(max);
- Для того чтобы создать полнотекстовый индекс сначала необходимо создать полнотекстовый каталог. Начиная с SQL Server 2008 полнотекстовый каталог это логическое понятие, обозначающее группу полнотекстовых индексов, т.е. является виртуальным объектом и не входит в файловую группу (есть способ создания полнотекстового индекса, используя «Мастер», при котором каталог можно создать одновременно с индексом, этот способ мы будем рассматривать чуть ниже).
В примерах ниже в качестве инструмента создания и управления полнотекстовыми каталогами и индексами я буду использовать SQL Server Management Studio.
Исходные данные для создания полнотекстового поиска
Допустим, что у нас есть база данных TestBase, а в ней есть таблица TestTable в которой всего два поля первое (id) это первичный ключ, а второе (textdata) это текстовые данные по которым мы и будем осуществлять полнотекстовый поиск.
CREATE TABLE TestTable(
id int IDENTITY(1,1) NOT NULL,
textdata varchar(500) NULL,
CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED (id ASC)
)
Для примера она будет содержать следующие данные
Создание полнотекстового каталога в SQL Server
Для создания полнотекстового каталога как впрочем, и индекса можно использовать или графический интерфейс SSMS или инструкций
You must be registered for see links
, мы с Вами разберем оба способа.Создание полнотекстового каталога на 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. Для этого открываем базу данных, переходим в папкуХранилище ->Полнотекстовые каталоги, щелкаем правой кнопкой мыши по данному пункту и выбираем «Создать полнотекстовый каталог»
Откроется окно создания каталога, где мы указываем название каталога и его опции
Изменение и удаление полнотекстового каталога в SQL Server
Для изменения опций каталога можно использовать инструкцию ALTER FULLTEXT CATALOG, например, давайте сделаем так, чтобы наш каталог перестал учитывать диакритические знаки, для этого пишем SQL инструкцию, которая перестроит наш каталог с новой опцией
ALTER FULLTEXT CATALOG TestCatalog
REBUILD WITH ACCENT_SENSITIVITY=OFF
GO
Для того чтобы удалить каталог можно использовать инструкцию T-SQL, например
DROP FULLTEXT CATALOG TestCatalog
Все это можно было сделать и в графическом интерфейсе Management Studio (для изменения параметров каталога «Свойства», для удаления «Удалить»)
Создание полнотекстового индекса в SQL Server
После создания полнотекстового каталога можно начинать создавать в нем полнотекстовые индекса. В нашем случае мы хотим создать полнотекстовый индекс, в котором участвует поле textdata таблицы TestTable.
Создание полнотекстового индекса на T-SQL
Для того чтобы создать полнотекстовый индекс можно написать следующую SQL инструкцию
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), автоматически отобразятся и в нашем полнотекстовом индексе, т.е. автоматическое заполнение.
Полнотекстовый индекс можно создать, используя и графические инструменты, для этого открываем свойства полнотекстового каталога и переходим в пункт «Таблицы или представления», выбираем нужную таблицу, поле, уникальный индекс и способ отслеживания изменений. В нашем случае у нас всего одна доступная таблица и одно поле.
Изменение и удаление полнотекстового индекса
В случае необходимости можно изменить параметры полнотекстового индекса. Давайте в качестве примера, изменим способ отслеживания изменений с автоматического на ручной. Для изменения в графическом интерфейсе можно использовать окно «Свойства полнотекстового каталога -> Таблицы или представления», которое мы использовали при создании полнотекстового индекса.
Или можно написать следующий код
ALTER FULLTEXT INDEX ON TestTable
SET CHANGE_TRACKING = MANUAL
Для того чтобы удалить полнотекстовый индекс достаточно просто удалить таблицу из списка объектов связанных с полнотекстовым каталогом в том же окне «Свойства полнотекстового каталога -> Таблицы или представления»
Или написать код T-SQL
DROP FULLTEXT INDEX ON TestTable
Создание полнотекстового каталога и индекса с помощью мастера
Как я уже упоминал ранее полнотекстовый каталог и индекс можно создать, используя мастер, т.е. по шагам, для этого щелкаем правой кнопкой мыши по таблице, которую мы хотим включить в полнотекстовый поиск, и выбираем «Полнотекстовый индекс ->Определить полнотекстовый индекс»
Примечание!Перед этим я удалил и каталог, и индекс которые мы создавали в предыдущих примерах.
В итоге запустится мастер полнотекстового индексирования SQL Server
Далее выбираем уникальный индекс
Затем столбец, который будет включен в полнотекстовый индекс
Потом необходимо выбрать способ отслеживания изменений
Указываем название каталога и его опции, для того чтобы его создать, так как предполагается, что у нас каталога нет, если бы он был, то мы могли бы его выбрать
Здесь мы можем настроить расписание заполнения полнотекстового каталога
Для создания каталога и индекса осталось нажать «Готово»
В следующем окне мы увидим результат выполнения операций по созданию полнотекстового каталога и индекса. В моем случае все прошло успешно.
Таким образом, мы выполнили создание полнотекстового каталога и индекса одновременно с помощью мастера.
Примеры полнотекстовых запросов
Сразу скажу, что более подробно полнотекстовые запросы мы будем рассматривать в следующих материалах, а пока, в качестве примера и подтверждения того, что наш полнотекстовый поиск работает, давайте напишем пару простых полнотекстовых запросов.
Если помните, наша таблица TestTable содержит определения технологий, языков программирования, в общем, определений связанных со сферой IT. Допустим, что мы хотим получить все записи, где есть упоминание о компании Microsoft, для этого мы пишем полнотекстовый запрос с ключевым словом CONTAINS, например:
select * from TestTable
where CONTAINS (textdata, '"Microsoft"')
Мы получили результат, но допустим, нам также необходимо отсортировать его по релевантности другими словами какие строки больше соответствуют нашему запросу. Для этого мы будем использовать функцию CONTAINSTABLE, которая проставляет ранг для каждой найденной записи.
SELECT Table1.id AS ID,
RowRank.Rank as [RANK], Table1.textdata as [TEXTDATA]
FROM TestTable Table1
INNER JOIN CONTAINSTABLE(TestTable, textdata, '"Microsoft"') as RowRank
on Table1.id=RowRank.[KEY]
ORDER BY RowRank.RANK DESC
Как видим, ранг проставлен и по нему отсортированы строки. Сам алгоритм ранжирования, как и более подробную информацию о полнотекстовом поиске можно найти в электронной документации по SQL Server.
На этом предлагаю заканчивать, надеюсь, все было понятно, удачи!