Поделиться через

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

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Пишите полнотекстовые запросы с помощью предикатов CONTAINS и FREETEXT и функций CONTAINSTABLE и FREETEXTTABLE, возвращающих наборы строк, с инструкцией SELECT. В этой статье приведены примеры предикатов и функций, из которых вы сможете выбрать самые подходящие.

  • Для сопоставления слов и фраз используйте функции CONTAINS и CONTAINSTABLE.
  • Для поиска совпадений по смыслу, а не буквального совпадения, используйте функции FREETEXT и FREETEXTTABLE.

Примеры каждого предиката и функции

Ниже приводятся примеры базы данных AdventureWorks. Окончательный выпуск AdventureWorks см. в разделе Базы данных и сценарии AdventureWorks для SQL Server 2016 CTP3. Чтобы запустить примеры запросов, нужно также настроить полнотекстовый поиск. Дополнительные сведения см. в разделе Начало работы с полнотекстовым поиском.

Пример - СОДЕРЖИТ

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

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

Пример FREETEXT

В следующем примере выполняется поиск всех документов, содержащих слова, связанные с vital safety components:

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

Пример "CONTAINSTABLE"

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

USE AdventureWorks2022  
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 AdventureWorks2022  
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 AdventureWorks2022  
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

Сопоставление слов или смысла

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

CONTAINS/CONTAINSTABLE

  • Сопоставление отдельных слов и фраз с точными или приближенными (менее точными) соответствиями.
  • Вы также можете выполнить следующие действия:
    • Определите близость слов друг к другу в пределах определенного расстояния.
    • Возвращать взвешенные совпадения.
    • объединять условия поиска с логическими операторами. Дополнительные сведения см. в разделе Использование логических операторов (AND, OR и NOT) далее в этой статье.

FREETEXT/FREETEXTTABLE

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

Сравнение предикатов и функций

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

Предикаты CONTAINS и FREETEXT

Использование. Полнотекстовые предикаты CONTAINS и FREETEXT используются в предложении WHERE или HAVING инструкции SELECT.

Результаты . Предикаты CONTAINS и FREETEXT возвращают значение TRUE или FALSE, которое указывает, соответствует ли данная строка полнотекстовому запросу. Строки, которые совпадают, возвращаются в результирующий набор.

Дополнительные параметры. Вы можете объединить предикаты с любыми другими предикатами Transact-SQL, такими как LIKE и BETWEEN.

Вы можете указать, следует ли искать один столбец, список столбцов или все столбцы в таблице.

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

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

Дополнительные сведения. Дополнительные сведения о синтаксисе и аргументах этих предикатов см. в статьях о CONTAINS и FREETEXT.

Функции строкового набора значений CONTAINSTABLE и FREETEXTTABLE

Использование. Полнотекстовые функции CONTAINSTABLE и FREETEXTTABLE можно использовать в предложении FROM инструкции SELECT, как обычное имя таблицы.

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

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

Результаты . Эти функции возвращают пустую таблицу, либо таблицу с одной или несколькими строками, соответствующими полнотекстовому запросу. Возвращаемая таблица содержит только строки базовой таблицы, которые соответствуют критерию выбора, задаваемому в условии полнотекстового поиска функции.

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

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

Дополнительные сведения. Дополнительные сведения о синтаксисе и аргументах этих функций см. в статьях о CONTAINSTABLE и FREETEXTTABLE.

Конкретные типы поиска

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

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

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

Условие поиска (в этом случае "learning curve") может быть сложным и включать одно выражение или несколько.

Дополнительные сведения о простых поисковых терминах

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

Например, «круассан» — это слово, а «кофе с молоком» — фраза. Такие слова и фразы называются простыми выражениями.

ФункцииCONTAINS и CONTAINSTABLE выполняют поиск точного соответствия для фразы. ФункцииFREETEXT и FREETEXTTABLE разбивают фразу на отдельные слова.

Поиск слова с префиксом (термин с префиксом)

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

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

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

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

Дополнительные сведения о поиске префиксов

Префиксный термин — это строка, прикреплённая к началу слова для образования производного слова или изменённой формы.

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

  • Внутри фразы каждое слово считается префиксным термином. Например, термин "auto tran*" соответствует "автоматической передаче" и "автомобильному преобразователю", но он не соответствует "автоматической передачи двигателя".

Поиск префиксов поддерживается CONTAINS и CONTAINSTABLE.

Поиск инфлекционных форм определенного слова (порождающий термин)

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

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

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

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

Дополнительные сведения о поиске по терминам поколения

Словоформы — это формы глаголов в различных временах и лицах или формы существительных в единственном или множественном числе.

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

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

Поиск синонимов конкретного слова

В тезаурусе определяются пользовательские синонимы терминов. Дополнительные сведения о файлах тезауруса см. в статье Настройка файлов тезауруса для полнотекстового поиска и управление ими.

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

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

Поиск слова вблизи другого слова

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

Примером может служить поиск строк, в которых слово "лед" находится рядом со словом "хоккей" или фраза "хоккей на льду" — рядом с фразой "катание на коньках".

CONTAINS и CONTAINSTABLE

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

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

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

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

USE AdventureWorks2022  
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

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

Дополнительные сведения о взвешенных условиях поиска

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

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

Взвешенный поиск поддерживается CONTAINSTABLE.

Использование ОПЕРАТОРОВ AND, OR и NOT (логические операторы)

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

Предикаты FREETEXT и FREETEXTTABLE, напротив, обрабатывают булевы термины как слова для поиска.

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

Пример

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

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

Регистр, стоп-слова, язык и тезаурус

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

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

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

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

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

Проверка результатов маркеризации

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

См. также

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