Повнотекстовий пошук (Full-Text Search) в MS SQL Server

Продовжуємо вивчати можливості SQL Server від компанії Microsoft і на черзі у нас компонент Full-Text Search, в російській варіанті – це «повнотекстовий пошук», І зараз ми дізнаємося, для чого він потрібен, і як же реалізувати цей самий повнотекстовий пошук в SQL сервері, використовуючи цей компонент.

І почнемо ми, звичайно ж, з розгляду основ повнотекстового пошуку, тобто що це таке і для чого він взагалі потрібен.

Що таке повнотекстовий пошук?

повнотекстовий пошук – це пошук слів чи фраз в текстових даних. Зазвичай такий вид пошуку використовується для пошуку тексту в великому обсязі даних, наприклад, таблиця з мільйоном і більш рядків, так як він значно швидше звичайного пошуку, який можна здійснити, використовуючи конструкцію LIKE.

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

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

Можливості повнотекстового пошуку в MS SQL Server

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

Підготовка до реалізації повнотекстового пошуку в MS SQL Server

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

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

Примітка! Реалізовувати повнотекстовий пошук я буду на прикладі версії SQL Server 2008 R2. Також мається на увазі, що компонент Full-Text Search у Вас вже встановлено, якщо немає, то встановіть його шляхом додавання відповідного компонента через «Центр установки 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, ми з Вами розберемо обидва способи.

Створення повнотекстового каталогу на T-SQL

CREATE FULLTEXT CATALOG TestCatalog
WITH ACCENT_SENSITIVITY = ON
AS DEFAULT
AUTHORIZATION dbo
GO

де,

  • CREATE FULLTEXT CATALOG – команда створення повнотекстового каталогу;
  • TestCatalog – ім’я нашого повнотекстового каталогу;
  • WITH ACCENT_SENSITIVITY OFF – опція вказує, чи буде повнотекстовий каталог враховувати діакритичні знаки для повнотекстового індексування. За замовчуванням ON;
  • AS DEFAULT – опція, для того щоб вказати, що каталог є каталогом за замовчуванням. У разі створення повнотекстового індексу без явної вказівки каталогу використовується каталог за замовчуванням;
  • AUTHORIZATION dbo – встановлює власника повнотекстового каталогу, їм може бути користувач або роль бази даних. В даному випадку ми вказали роль dbo.

Створення повнотекстового каталогу в графічному інтерфейсі Management Studio

Точно такий же повнотекстовий каталог можна створити і в графічному інтерфейсі 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), автоматично відобразяться і в нашому повнотекстовому індексі, тобто автоматичне заповнення.

Створення повнотекстового індексу в графічному інтерфейсі Management Studio

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

Зміна та видалення повнотекстового індексу

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

Або можна написати наступний код

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.

Ссылка на основную публикацию