Давайте приступим к рассмотрению полнотекстовых запросов в СУБД Microsoft SQL Server, с помощью которых осуществляется полнотекстовый поиск.
CONTAINS
CONTAINS – это ключевое слово, которое используется в конструкции WHERE для поиска слов или фраз в столбце, который участвует в полнотекстовом поиске.
В следующем примере мы просто ищем строки, которые содержат слово Microsoft
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, 'Microsoft')
Логические операторы
При составлении критерия поиска можно использовать логические операторы AND, OR, AND NOT, т.е. например, можно построить запрос так чтобы искались строки со словами и Microsoft и SQL
--Строки, содержащие и слово Microsoft и слово SQL (AND)
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, '"Microsoft" AND "SQL"')
--Строки, содержащие слово Microsoft или слово SQL (OR)
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, '"Microsoft" OR "SQL"')
--Строки, содержащие слово Microsoft и при этом не содержат слово SQL (AND NOT)
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, '"Microsoft" AND NOT "SQL"')
Поиск по префиксным выражениям
Префиксные выражения означают, что мы можем искать слова, не указывая их полностью, например, указав только начало, это делается с помощью знака *
Допустим, нам необходимо найти строки, где есть упоминания о программах или программировании, для этого мы напишем следующее:
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, '"програм*"')
Поиск слова по словоформам
Полнотекстовый поиск SQL сервера позволяет искать различные формы глаголов или существительные в единственном и во множественном числе, например, давайте найдем записи, в которых есть слово «запрос» и его производные выражения
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, 'FORMSOF(INFLECTIONAL, "запрос")')
Как видим слова «запрос» у нас нет, но есть слово «запросов» поэтому эта строка и вывелась. Для поиска по словоформам мы использовали функцию FORMSOF().
Поиск слов или фраз с учетом расположения
Если Вам нужно найти слова или фразы, которые располагаются недалеко друг от друга, то можно использовать ключевое слово NEAR. Допустим, мы хотим получить все строки, в которых есть упоминание о любом языке программирования, но при условии, что компания Microsoft должна иметь к нему какое-то отношение, другими словами фраза «язык программирования» должна располагаться неподалеку от слова «Microsoft».
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE CONTAINS (textdata, '"язык программирования" NEAR "Microsoft"')
Функция CONTAINSTABLE
Это табличная функция, которая возвращает результирующий набор данных с проставленным рангом. RANK это значение от 0 до 1000 показывающие степень соответствия каждой строки условию поиска. Другими словами мы можем написать запрос и получить отсортированный результат по релевантности. При использовании функции CONTAINSTABLE можно использовать такие же условия поиска, как и в CONTAINS. Эта функция помимо RANK возвращает еще столбец KEY это уникальный ключ базовой таблицы, по которому можно произвести объединение.
Давайте напишем полнотекстовый запрос, который вернет записи, в которых есть слово «SQL» или «Microsoft», при этом отсортируем результат по релевантности, т.е. сначала будут идти те строки, которые максимально подходят под наш критерий.
SELECT Table1.id AS ID,
RowRank.Rank AS [RANK],
Table1.textdata AS [TEXTDATA]
FROM TestTable Table1
INNER JOIN CONTAINSTABLE(TestTable, textdata, '"SQL" OR "Microsoft"') AS RowRank
ON Table1.id=RowRank.[KEY]
ORDER BY RowRank.RANK DESC
В случае необходимости в функцию CONTAINSTABLE четвертым параметром можно передать язык, ресурсы которого будут использоваться для разбиения слов, но он не обязателен. Также пятым или четвертым параметром (если не указан параметр LANGUAGE) можно передать число которое ограничит результирующий набор. Например, для получения только первых 3 строк мы бы написали вот такой запрос:
SELECT Table1.id AS ID,
RowRank.Rank as [RANK],
Table1.textdata as [TEXTDATA]
FROM TestTable Table1
INNER JOIN CONTAINSTABLE(TestTable,textdata,
'"SQL" OR "Microsoft"',3) AS RowRank
ON Table1.id=RowRank.[KEY]
ORDER BY RowRank.RANK DESC
Поиск с использованием взвешенных значений
В условии поиска можно указать важность того или иного слова или фразы от 0.0 до 1.0, т.е. осуществлять поиск со взвешенными значениями. Значение 0.0 является самым низким, а значение 1.0 самым высоким (в качестве десятичного разделителя всегда используется точка). Чтобы в запросе использовать взвешенные значения необходимо использовать такие функции как ISABOUT и WEIGHT. Их также можно использовать и в запросах CONTAINS.
А теперь допустим, что нас результаты ранжирования, из предыдущего примера, не устроили, мы хотим видеть сначала только самые релевантные строки, содержащие слово SQL, а только потом строки со словом Microsoft. Для этого мы назначим более высокий приоритет слову SQL, допустим 0.9, а Microsoft 0.1.
SELECT Table1.id AS ID,
RowRank.Rank AS [RANK],
Table1.textdata AS [TEXTDATA]
FROM TestTable Table1
INNER JOIN CONTAINSTABLE(TestTable, textdata,
'ISABOUT("SQL" WEIGHT(.9),
"Microsoft" WEIGHT(.1))') AS RowRank
ON Table1.id=RowRank.[KEY]
ORDER BY RowRank.RANK DESC
Как видим, результат у нас изменился и теперь строки, которые содержат слово SQL, имеют более высокий ранг.
FREETEXT
FREETEXT – это ключевое слово, с помощью которого осуществляется поиск по произвольной текстовой строке. Другими словами мы можем написать любую фразу или предложение, а FREETEXT сам выделит все слова и определит все словоформы этих слов и только потом выполнит запрос. Полнотекстовые запросы с использованием FREETEXT являются менее точными по сравнению с CONTAINS. Как Вы понимаете использование ключевых слов WEIGHT, FORMSOF, NEAR и прочего синтаксиса запрещено.
Для примера давайте представим, что мы не знаем, что конкретно мы ищем, например, мы хотим узнать есть ли в нашей базе языки, которые используются для разработки программ, и для этого напишем следующий запрос:
SELECT id AS ID, textdata AS TextData
FROM TestTable
WHERE FREETEXT (textdata, 'Языки для разработки программ')
В итоге мы получаем список, который примерно соответствует нашему запросу, т.е. в этих строках есть слова похожие на слова из строки нашего условия.
Функция FREETEXTTABLE
Для определения какая строка самая релевантная, т.е. подходящая под строку запроса можно использовать табличную функцию FREETEXTTABLE, которая также как и CONTAINSTABLE проставляет ранжирующее значение от 0 до 1000 и возвращает два столбца KEY и RANK. Количество возвращаемых строк можно также ограничить, передав дополнительный параметр. В условие запроса использование ключевых слов WEIGHT, FORMSOF, NEAR и других, как и во FREETEXT нельзя.
В примере выше мы получили результирующий набор, но мы не знаем какие строки действительно релевантные нашему запросу, поэтому имеет смысл использовать функцию FREETEXTTABLE, для того чтобы увидеть ранжирующие значение каждой строки и, допустим, отсортировать по нему.
SELECT Table1.id AS ID,
RowRank.Rank AS [RANK],
Table1.textdata AS [TEXTDATA]
FROM TestTable Table1
INNER JOIN FREETEXTTABLE(TestTable,
textdata,
'Языки для разработки программ') AS RowRank
ON Table1.id=RowRank.[KEY]
ORDER BY RowRank.RANK DESC
Результат этого запроса уже более понятен, исключение составляет строка с C++, в которой кроме слова «язык» ничего похожего на слова из нашей фразы нет.
Запросы на получение полнотекстовых свойств индексирования
Дополнительно хотелось бы еще отметить пару функций, с помощью которых нельзя выполнить поиск, но можно узнать параметры которые влияют на результаты поиска.
Функция FULLTEXTCATALOGPROPERTY
С помощью этой функции можно узнать свойства полнотекстового каталога. Она имеет два параметра: первый это название полнотекстового каталога и второй это имя свойства, значение которого мы хотим узнать.
Например, следующий запрос показывает статус полнотекстового каталога, т.е. какая операция в данный момент выполняется:
SELECT CASE
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 0
THEN 'Бездействие'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 1
THEN 'Идет полное заполнение'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 2
THEN 'Пауза'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 3
THEN 'Ограниченный режим'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 4
THEN 'Восстановление'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 5
THEN 'Выключение'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 6
THEN 'Идет добавочное заполнение'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 7
THEN 'Построение индекса'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 8
THEN 'Диск заполнен. Приостановлено'
WHEN FULLTEXTCATALOGPROPERTY('TestCatalog','PopulateStatus') = 9
THEN 'Отслеживание изменений'
ELSE ''
END AS [Статус]
Также доступны и другие свойства, например:
- AccentSensitivity — учет диакритических знаков, 0 без учета, 1 с учетом;
- IndexSize — логический размер полнотекстового каталога в мегабайтах (МБ);
- ItemCount — количество элементов в полнотекстовом каталоге;
- MergeStatus — выполняется ли слияние в единый файл, 0 слияние не выполняется, 1 слияние выполняется;
- PopulateCompletionAge — разница в секундах между последним заполнением полнотекстового индекса и 01/01/1990 00:00:00;
- UniqueKeyCount — количество уникальных ключей в полнотекстовом каталоге;
- ImportStatus — выполняется ли в настоящее время импорт полнотекстового каталога, 0 не выполняется, 1 выполняется.
Функция OBJECTPROPERTYEX
Это функция используется для получения данных об объектах области схемы в текущей базе данных. Другими словами с помощью нее можно узнать свойства объектов, не только относящихся к полнотекстовому индексированию. В качестве параметров она принимает: первый параметр идентификатор объекта и второй это название свойства, значение которого мы хотим узнать.
В случае с полнотекстовым поиском она нам может помочь, например, тогда когда мы хотим узнать есть ли у таблицы полнотекстовый индекс или включено ли полнотекстовое отслеживание изменений.
SELECT OBJECTPROPERTYEX(
object_id('TestTable'),
'TableHasActiveFulltextIndex'
) AS [Активный полнотекстовый индекс(TRUE/FALSE)]
SELECT OBJECTPROPERTYEX(
object_id('TestTable'),
'TableFulltextChangeTrackingOn'
) AS [Полнотекстовое отслеживание изменений(TRUE/FALSE)]
SELECT OBJECTPROPERTYEX(
object_id('TestTable'),
'TableFulltextCatalogId'
) AS [Идентификатор полнотекстового каталога]
