Запросы с полнотекстовым поиском

Для определения полнотекстовых запросов SQL Server используют полнотекстовые предикаты (CONTAINS и FREETEXT) и функции (CONTAINSTABLE и FREETEXTTABLE). Они поддерживают широкий Transact-SQL синтаксис, поддерживающий различные формы терминов запросов. Чтобы написать полнотекстовые запросы, необходимо узнать, когда и как использовать эти предикаты и функции.

Обзор предикатов Full-Text (CONTAINS и FREETEXT)

Предикаты CONTAINS и FREETEXT возвращают значение TRUE или FALSE. Их можно использовать только для указания критериев выбора для определения соответствия заданной строки полнотекстового запроса. Строки, которые совпадают, возвращаются в результирующий набор. CONTAINS и FREETEXT указываются в предложении SELECT, в части WHERE или HAVING. Их можно объединить с любым из других Transact-SQL предикатов, таких как LIKE и BETWEEN.

При использовании CONTAINS или FREETEXT можно указать один столбец, список столбцов или все столбцы в таблице для поиска. При необходимости можно указать язык, ресурсы которого будут использоваться данным полнотекстовым запросом для разбиения слов на составляющие части и стемминга, поиска по тезаурусу и удаления стоп-слов.

CONTAINS и FREETEXT полезны для различных типов совпадений, как показано ниже.

• Используйте CONTAINS (или CONTAINSTABLE) для точных или неточных (менее точных) совпадений с отдельными словами и фразами, для поиска близости слов на определенном расстоянии друг от друга или для взвешенных совпадений. При использовании CONTAINS необходимо указать по крайней мере одно условие поиска, указывающее текст, который выполняется поиск, и условия, определяющие совпадения.

  Вы можете использовать логическую операцию между условиями поиска. Дополнительные сведения см. в разделе Об использовании логических операторов AND, OR и NOT (в CONTAINS и CONTAINSTABLE) далее в этом разделе.

• Используйте FREETEXT (или FREETEXTTABLE) для сопоставления значения, но не точного выражения, указанных слов, фраз или предложений ( строка свободного текста). Совпадения генерируются, если любой термин или его форма найдены в полнотекстовом индексе указанного столбца.

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

Примеры

А. Использование CONTAINS с <simple_term>

Следующий пример находит все продукты с ценой $80.99 , содержащей слово "Mountain".

USE AdventureWorks2012  
GO  
  
SELECT Name, ListPrice  
FROM Production.Product  
WHERE ListPrice = 80.99  
   AND CONTAINS(Name, 'Mountain')  
GO  

В. Использование инструкции FREETEXT для поиска слов, содержащих определенные значения символов

Следующий пример просматривает все документы, содержащие слова, которые связаны со словами «vital», «safety», «components».

USE AdventureWorks2012  
GO  
  
SELECT Title  
FROM Production.Document  
WHERE FREETEXT (Document, 'vital safety components')  
GO  

Обзор функций Full-Text (CONTAINSTABLE и FREETEXTTABLE)

Функции CONTAINSTABLE и FREETEXTTABLE используются как обычное имя таблицы в предложении FROM инструкции SELECT. Они возвращают таблицу с нуля, одной или несколькими строками, соответствующими полнотекстовому запросу. Возвращаемая таблица содержит только строки базовой таблицы, соответствующие критериям выбора, указанным в условии полнотекстового поиска функции.

Запросы, использующие одну из этих функций, возвращают значение ранжирования релевантности (RANK) и полнотекстовый ключ (KEY) для каждой строки следующим образом:

• Столбец KEY

  Столбец KEY возвращает уникальные значения возвращаемых строк. С помощью столбца KEY можно задавать критерии выбора.

• Столбец RANK

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

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

CONTAINSTABLE полезна для тех же типов совпадений, что и CONTAINS, и FREETEXTTABLE полезно для тех же типов совпадений, что и FREETEXT. Дополнительные сведения см. в разделе "Общие сведения о Full-Text предикаты (CONTAINS и FREETEXT)" ранее в этом разделе. При выполнении запросов, использующих функции CONTAINSTABLE и FREETEXTTABLE, необходимо явно присоединить возвращаемые строки к строкам в базовой таблице SQL Server.

Как правило, результат CONTAINSTABLE или FREETEXTTABLE необходимо объединить с базовой таблицей. В таких случаях необходимо знать уникальное имя ключевого столбца. Этот столбец, имеющийся в каждой таблице с поддержкой полнотекстового поиска, используется для принудительного применения уникальных строк в таблице (уникальный**ключевой столбец). Дополнительные сведения см. в разделе Управление полнотекстовыми индексами.

Примеры

А. Использование CONTAINSTABLE

В следующем примере возвращается идентификатор описания и описание всех продуктов, для которых столбец Description содержит слово "алюминий" рядом со словом "light" или "lightweight". Возвращаются только строки со значением ранга 2 или выше.

USE AdventureWorks2012  
GO  
  
SELECT FT_TBL.ProductDescriptionID,  
   FT_TBL.Description,   
   KEY_TBL.RANK  
FROM Production.ProductDescription AS FT_TBL INNER JOIN  
   CONTAINSTABLE (Production.ProductDescription,  
      Description,   
      '(light NEAR aluminum) OR  
      (lightweight NEAR aluminum)'  
   ) AS KEY_TBL  
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK > 2  
ORDER BY KEY_TBL.RANK DESC;  
GO  

В. Использование FREETEXTTABLE

В следующем примере запрос FREETEXTTABLE дополнен таким образом, чтобы сначала возвращать строки с наивысшим рейтингом, добавив эти рейтинги в список выбора. Чтобы указать запрос, необходимо знать, что ProductDescriptionID является уникальным ключевым столбцом таблицы ProductDescription .

USE AdventureWorks2012  
GO  
  
SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

Ниже приведено расширение того же запроса, которое возвращает только строки со значением ранжирования 10 или больше:

USE AdventureWorks2012  
GO  
  
SELECT KEY_TBL.RANK, FT_TBL.Description  
FROM Production.ProductDescription AS FT_TBL   
     INNER JOIN  
     FREETEXTTABLE(Production.ProductDescription, Description,  
                    'perfect all-around bike') AS KEY_TBL  
     ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  
WHERE KEY_TBL.RANK >= 10  
ORDER BY KEY_TBL.RANK DESC  
GO  

Использование логических операторов — AND, OR и NOT — в CONTAINS и CONTAINSTABLE

Функция CONTAINSTABLE и предикат CONTAINS используют одинаковые условия поиска. Обе поддерживают объединение нескольких терминов поиска с помощью логических операторов AND, OR и NOT для выполнения логических операций. Вы можете использовать AND, например, для поиска строк, содержащих как латте, так и "бублик по-нью-йоркски". Вы можете использовать AND NOT, например, чтобы найти строки, содержащие "бублик", но не содержащие "сливочный сыр".

Сведения об объединении CONTAINS с другими предикатами, которые используют логические операторы AND, OR и NOT, см. в разделе "Условие поиска" (Transact-SQL).

Пример

В следующем примере используется таблица ProductDescription базы данных AdventureWorks2012 . Запрос использует предикат CONTAINS для поиска описаний, в которых идентификатор описания не равен 5 и описание содержит как слово «Алюминий», так и слово «шпиндель». Условие поиска использует логический оператор AND.

USE AdventureWorks2012  
GO  
  
SELECT Description  
FROM Production.ProductDescription  
WHERE ProductDescriptionID <> 5 AND  
   CONTAINS(Description, 'aluminum AND spindle')  
GO  

Дополнительные соображения по Full-Text запросам

При написании полнотекстовых запросов также следует учитывать следующее:

• Опция Язык

  Многие выражения запроса в значительной степени зависят от поведения разделителя слов. Чтобы гарантировать использование правильного средства разбиения по словам (и модуля стемминга) и файла тезауруса, рекомендуется указывать опцию LANGUAGE. Дополнительные сведения см. в разделе Выбор языка при создании полнотекстового индекса.

• Стоп-слова

  При определении полнотекстового запроса следует иметь в виду, что средство полнотекстового поиска не учитывает стоп-слова (также известные как пропускаемые слова), указанные в критерии поиска. Стоп-слова — это часто встречающиеся слова, которые не повышают эффективность поиска конкретного текста. Примерами могут служить слова «и», «или», «о» и «в». Стоп-слова перечислены в стоплисте. Каждый полнотекстовый индекс связан с конкретным списком стоп-слов, который определяет, какие стоп-слова не указываются в запросе или в индексе во время индексирования. Дополнительные сведения см. в разделе Настройка стоп-слов и списков стоп-слов для полнотекстового поиска и управление ими.

• Тезаурус

  По умолчанию тезаурус используется в запросах FREETEXT и FREETEXTTABLE. Предикат CONTAINS и функция CONTAINSTABLE поддерживают опциональный аргумент THESAURUS.

• Конфиденциальность регистра

  В запросах полнотекстового поиска не учитывается регистр букв. Однако в японском языке существует несколько фонетических орфографий, в которых концепция орфографической нормализации аналогична нечувствительности к регистру (например, кана = нечувствительность). Этот тип орфографической нормализации не поддерживается.

Запросы varbinary(max) и xml Columns

varbinary(max) varbinary xml Если столбец имеет полнотекстовый индекс, его можно запросить с помощью полнотекстовых предикатов (CONTAINS и FREETEXT) и функций (CONTAINSTABLE и FREETEXTTABLE), как и любой другой полнотекстовый индексированный столбец.

varbinary(max) или varbinary data

Один varbinary(max) или varbinary столбец может хранить множество типов документов. SQL Server поддерживает любой тип документа, для которого установлен фильтр и доступен в операционной системе. Тип каждого документа определяется по расширению имени файла этого документа. Например, при работе с DOC-файлом при полнотекстовом поиске будет использоваться фильтр, который поддерживает документы Microsoft Word. Чтобы получить список доступных типов документов, выполните запрос к представлению каталога sys.fulltext_document_types .

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

EXEC sp_fulltext_service @action='load_os_resources', @value=1  

Чтобы создать полнотекстовый индекс в varbinary(max) столбце, обработчику Full-Text необходим доступ к расширениям файлов документов в столбце varbinary(max) . Эти сведения должны храниться в столбце таблицы, называемом столбцом типа, который должен быть связан со varbinary(max) столбцом в полнотекстовом индексе. Во время индексирования документа средство полнотекстового поиска по расширению файла в столбце типа определяет, какой фильтр следует использовать.

XML-данные

Столбец xml типа данных хранит только XML-документы и фрагменты, а для документов используется только XML-фильтр. Поэтому столбец типов не требуется. В xml столбцах полнотекстовый индекс индексирует содержимое XML-элементов, но игнорирует разметку XML. К значениям атрибута, если они не являются числовыми значениями, применяется полнотекстовый индекс. Теги элементов используются в качестве границ токенов. Поддерживаются корректно оформленные документы и фрагменты XML или HTML, содержащие несколько языков.

Дополнительные сведения о запросе в столбце xml см. в разделе "Использование поиска Full-Text с XML-столбцами".

Поддерживаемые формы терминов запросов

В этом разделе приведены сведения о поддержке, предоставляемой для каждой формы запроса полнотекстовых предикатов и функций с значением набора строк.

Поиск определенного слова или фразы (простой термин)

Вы можете использовать CONTAINS, CONTAINSTABLE, FREETEXT или FREETEXTTABLE для поиска таблицы для определенной фразы. Например, если вы хотите искать ProductReview таблицу в базе данных AdventureWorks2012 , чтобы найти все комментарии о продукте с фразой "кривая обучения", можно использовать предикат CONTAINS следующим образом:

USE AdventureWorks2012  
GO  
  
SELECT Comments  
FROM Production.ProductReview  
WHERE CONTAINS(Comments, '"learning curve"')  
GO  

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

Выполнение префиксного поиска (поиска по префиксу)

Для поиска слов и фраз с указанным префиксом можно использовать функции CONTAINS или CONTAINSTABLE . Будут возвращены все записи в столбце, содержащие текст, который начинается с заданного префикса. Например, чтобы найти все строки, содержащие префикс top-, как в top``ple, top``pingи top. Запрос выглядит следующим образом:

USE AdventureWorks2012  
GO  
  
SELECT Description, ProductDescriptionID  
FROM Production.ProductDescription  
WHERE CONTAINS (Description, '"top*"' )  
GO  

При выполнении этого запроса будут возвращены все фрагменты текста, соответствующие тексту, указанному перед звездочкой (*). Если текст и звездочка не ограничены двойными кавычками (например, CONTAINS (DESCRIPTION, 'top*')), полнотекстовый поиск не считает звездочку символом-шаблоном.

Если префиксный термин является фразой, каждый токен, составляющий фразу, считается отдельным префиксным термином. При выполнении такого запроса будут возвращены все строки со словами, начинающимися на префиксные термы. Например, префикс "лёгкий хлеб*" будет находить строки, содержащие текст "лёгкий панированный," "слегка панированный," или "лёгкий хлеб," но не будет возвращать "слегка поджаренный хлеб".

Поиск инфлекционных форм конкретного слова (термин генерации)

С помощью функций CONTAINS, CONTAINSTABLE, FREETEXTили FREETEXTTABLE можно найти все грамматические формы глаголов и существительных (поиск словоформ) или синонимы указанного слова (поиск по тезаурусу).

В следующем примере выполняется поиск любой формы слова "фут" ("фут", "футов" и т. д.) в столбце Comments таблицы ProductReview в базе данных AdventureWorks.

USE AdventureWorks2012  
GO  
  
SELECT Comments, ReviewerName  
FROM Production.ProductReview  
WHERE CONTAINS (Comments, 'FORMSOF(INFLECTIONAL, "foot")')  
GO  

Поиск слов или фраз с использованием весовых значений (взвешанный термин)

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

В следующем примере показан запрос, который ищет все адреса клиентов, используя весовые значения, в которых любой текст, начинающийся со строки "Bay", имеет "Street" или "View". Результаты дают более высокий ранг тем строкам, которые содержат больше указанных слов.

USE AdventureWorks2012  
GO  
  
SELECT AddressLine1, KEY_TBL.RANK   
FROM Person.Address AS Address INNER JOIN  
CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("Bay*",   
         Street WEIGHT(0.9),   
         View WEIGHT(0.1)  
         ) ' ) AS KEY_TBL  
ON Address.AddressID = KEY_TBL.[KEY]  
ORDER BY KEY_TBL.RANK DESC  
GO  

Взвешенное выражение можно использовать в сочетании с любым простым выражением, префиксным выражением, генерационным выражением или выражением близости.

Просмотр результата токенизации при комбинации алгоритма разбиения слов, тезауруса и стоп-списка.

После применения заданного средства разбиения слов, тезауруса и стоп-листа к строке запроса можно просмотреть результат токенизации с помощью динамического представления управления sys.dm_fts_parser. Дополнительные сведения см. в разделе sys.dm_fts_parser (Transact-SQL).

См. также

СОДЕРЖИТ (Transact-SQL)
CONTAINSTABLE (Transact-SQL)
FREETEXT (Transact-SQL)
FREETEXTTABLE (Transact-SQL)
Создание запросов полнотекстового поиска (визуальные инструменты для баз данных)
Повышение производительности полнотекстовых запросов