Возможности полнотекстового поиска в MySQL

Введение

В этой лабораторной работе вы изучите возможности полнотекстового поиска MySQL. Вы начнете с создания базы данных и таблицы с именем articles, содержащей столбцы id, title и content, а затем заполните ее примерами данных.

Далее вы добавите полнотекстовый индекс с именем article_index к таблице articles, в частности, к столбцам title и content, используя оператор ALTER TABLE. Наконец, вы проверите создание индекса с помощью команды SHOW INDEXES, убедившись, что Index_type имеет значение FULLTEXT. Эта настройка подготовит вас к эффективному поиску текста в вашей базе данных MySQL.

В ходе этой лабораторной работы вы будете работать внутри оболочки MySQL (MySQL shell). Вам нужно будет войти в оболочку MySQL только в начале и выйти в конце. Все SQL-команды в следующих шагах должны выполняться в рамках одной и той же сессии MySQL.

Создание базы данных и таблицы

На этом этапе вы создадите базу данных MySQL и таблицу для хранения статей. Эта таблица будет использоваться для демонстрации возможностей полнотекстового поиска.

Сначала откройте терминал и подключитесь к серверу MySQL от имени пользователя root. Это можно сделать с помощью следующей команды:

sudo mysql -u root

Эта команда подключается к серверу MySQL, используя пользователя root. Поскольку вы используете sudo, пароль запрашиваться не будет.

После подключения к оболочке MySQL вы увидите приглашение mysql>. Оставайтесь в оболочке MySQL для всех последующих шагов до конца лабораторной работы.

Теперь давайте создадим базу данных с именем search_db. Эта база данных будет содержать нашу таблицу articles. Выполните следующую SQL-команду:

CREATE DATABASE IF NOT EXISTS search_db;

Предложение IF NOT EXISTS гарантирует, что база данных будет создана только в том случае, если она еще не существует.

Затем переключитесь на только что созданную базу данных с помощью команды USE:

USE search_db;

Вы должны увидеть сообщение об изменении базы данных.

Теперь создайте таблицу articles. Эта таблица будет иметь три столбца: id, title и content.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT
);

Разберем эту команду:

• CREATE TABLE articles: Это оператор создает новую таблицу с именем articles.
• id INT AUTO_INCREMENT PRIMARY KEY: Это определяет целочисленный столбец с именем id, который автоматически увеличивается для каждой новой строки и служит первичным ключом таблицы.
• title VARCHAR(255) NOT NULL: Это определяет строковый столбец с именем title с максимальной длиной 255 символов. NOT NULL означает, что этот столбец не может быть пустым.
• content TEXT: Это определяет текстовый столбец с именем content, который может хранить более длинные строки.

После выполнения этой команды таблица articles будет создана в базе данных search_db.

Вы можете проверить создание таблицы, выведя список таблиц в текущей базе данных:

SHOW TABLES;

В выводе вы должны увидеть articles.

+-----------------------+
| Tables_in_search_db   |
+-----------------------+
| articles              |
+-----------------------+
1 row in set (0.00 sec)

Теперь, когда вы создали базу данных и таблицу, вы готовы заполнить их некоторыми примерами данных на следующем шаге.

Вставка образца данных и добавление полнотекстового индекса

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

Убедитесь, что вы все еще находитесь в оболочке MySQL и используете базу данных search_db. Если нет, выберите базу данных:

USE search_db;

Теперь давайте вставим некоторые примеры данных в таблицу articles. Мы добавим три строки с разными заголовками и содержимым:

INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');

Этот оператор INSERT добавляет несколько строк в таблицу articles. Каждый набор скобок после VALUES представляет новую строку, причем значения соответствуют столбцам title и content.

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

SELECT * FROM articles;

В выводе вы должны увидеть три вставленные строки:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Теперь, когда в таблице есть данные, давайте добавим полнотекстовый индекс. Полнотекстовый индекс позволяет MySQL выполнять быстрый и релевантный поиск по текстовым данным. Мы добавим полнотекстовый индекс к столбцам title и content.

Используйте оператор ALTER TABLE для добавления индекса:

ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);

Разберем эту команду:

• ALTER TABLE articles: Это указывает на то, что мы изменяем таблицу articles.
• ADD FULLTEXT INDEX article_index: Это добавляет новый индекс типа FULLTEXT и называет его article_index.
• (title, content): Это указывает столбцы, которые будут включены в полнотекстовый индекс.

После выполнения этой команды MySQL создаст полнотекстовый индекс для указанных столбцов.

Чтобы убедиться, что полнотекстовый индекс успешно создан, вы можете использовать команду SHOW INDEXES:

SHOW INDEXES FROM articles;

В выводе будут показаны все индексы в таблице articles. Вы должны увидеть запись для article_index со значением FULLTEXT в столбце Index_type.

+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles |          0 | PRIMARY        |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| articles |          1 | article_index  |            1 | title       | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
| articles |          1 | article_index  |            2 | content     | NULL      |           3 |     NULL | NULL   | YES  | FULLTEXT   |         |               | YES     | NULL       |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)

Теперь вы успешно вставили данные и добавили полнотекстовый индекс в свою таблицу. На следующем шаге вы узнаете, как использовать этот индекс для выполнения полнотекстового поиска.

Выполнение базового полнотекстового поиска

На этом этапе вы выполните базовый полнотекстовый поиск по таблице articles с использованием предложения MATCH AGAINST. Это основной способ использования созданного вами полнотекстового индекса.

Убедитесь, что вы все еще находитесь в оболочке MySQL и используете базу данных search_db. Если нет, выберите базу данных:

USE search_db;

Предложение MATCH AGAINST используется в предложении WHERE оператора SELECT. Базовый синтаксис выглядит следующим образом:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');

Здесь column_list — это список столбцов, разделенных запятыми, которые включены в полнотекстовый индекс (в нашем случае title и content), а 'search_term' — это слово или фраза, которую вы хотите найти.

Давайте найдем статьи, содержащие слово "MySQL":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');

Этот запрос выбирает столбцы id, title и content из таблицы articles, где столбцы title или content соответствуют термину "MySQL".

Вы должны увидеть следующий вывод:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)

Будут возвращены все три статьи, поскольку все они содержат слово "MySQL".

Полнотекстовый поиск также предоставляет показатель релевантности, указывающий, насколько хорошо каждая строка соответствует поисковому термину. Вы можете включить этот показатель в свой оператор SELECT:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;

Этот запрос добавляет столбец с именем relevance, который показывает оценку для каждой строки.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

Вы можете отсортировать результаты по релевантности, чтобы сначала увидеть наилучшие совпадения:

SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;

Этот запрос сортирует результаты на основе показателя relevance в порядке убывания.

+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title                    | content                                                             | relevance          |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         | 1.34832763671875   |
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)

На этом шаге вы научились выполнять базовый полнотекстовый поиск с помощью MATCH AGAINST и получать и сортировать результаты по релевантности. На следующем шаге вы изучите более продвинутые параметры поиска с использованием булева режима.

Использование булева режима для расширенного поиска

На этом этапе вы будете использовать булев режим предложения MATCH AGAINST для выполнения более точного и гибкого полнотекстового поиска. Булев режим позволяет использовать операторы для управления тем, какие слова должны или не должны присутствовать в результатах.

Убедитесь, что вы все еще находитесь в оболочке MySQL и используете базу данных search_db. Если нет, выберите базу данных:

USE search_db;

Для использования булева режима добавьте предложение IN BOOLEAN MODE к оператору MATCH AGAINST. Синтаксис выглядит следующим образом:

SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);

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

• +: Требует, чтобы слово присутствовало в результате.
• -: Исключает строки, содержащие слово.
• "": Поиск точной фразы.
• *: Оператор подстановки (в конце слова).

Найдем статьи, которые должны содержать слово "MySQL" и не должны содержать слово "optimizing".

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);

Этот запрос использует оператор + для требования "MySQL" и оператор - для исключения "optimizing".

Вы должны увидеть следующий вывод:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Статья об "Optimizing MySQL Queries" исключена, поскольку она содержит "optimizing".

Теперь давайте найдем точную фразу "full-text search":

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);

Использование двойных кавычек ищет слова как непрерывную фразу.

Вывод будет следующим:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL.         |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

Возвращается только первая статья, поскольку она содержит точную фразу "full-text search".

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

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);

Термин index* будет соответствовать словам, таким как "index" и "indexing".

Вывод будет следующим:

+----+------------------------+---------------------------------------------------------------------+
| id | title                  | content                                                             |
+----+------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL      | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

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

Комбинирование техник поиска

На этом этапе вы объедините базовый поиск MATCH AGAINST с операторами булева режима для выполнения более сложных и точных полнотекстовых запросов.

Убедитесь, что вы все еще находитесь в оболочке MySQL и используете базу данных search_db. Если нет, выберите базу данных:

USE search_db;

Попробуем найти статьи, которые содержат слово "MySQL" и либо слово "indexing", либо слово "optimizing". Мы можем использовать скобки для группировки необязательных терминов в булевом режиме.

SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);

Этот запрос требует наличия "MySQL" (+MySQL) и хотя бы одного из терминов в скобках (+(indexing optimizing)).

Вывод будет следующим:

+----+--------------------------+---------------------------------------------------------------------+
| id | title                    | content                                                             |
+----+--------------------------+---------------------------------------------------------------------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

Первая статья исключена, поскольку она содержит "MySQL", но не "indexing" и не "optimizing".

Вы также можете комбинировать булев режим с оценкой релевантности. Найдем статьи, содержащие "MySQL", и отдадим приоритет тем, в которых также упоминается "indexing", используя оператор >.

SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;

Этот запрос требует наличия "MySQL" и повышает оценку релевантности для статей, содержащих "indexing". Затем результаты сортируются по рассчитанной релевантности.

Вывод будет следующим:

+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title                    | content                                                             | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
|  2 | Indexing in MySQL        | Learn about different types of indexes in MySQL, including full-text indexes. |  1.6931  |
|  1 | MySQL Full-Text Search   | This article explains how to use full-text search in MySQL.         |  0.3068  |
|  3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |  0.3068  |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)

Обратите внимание, что статья "Indexing in MySQL" имеет наивысший рейтинг благодаря оператору >indexing.

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

На этом лабораторная работа по возможностям полнотекстового поиска MySQL завершена. Теперь вы можете выйти из оболочки MySQL, набрав:

exit

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

Резюме

В этой лабораторной работе вы ознакомились с возможностями полнотекстового поиска в MySQL. Вы начали с создания базы данных с именем search_db и таблицы articles для хранения текстовых данных. Затем вы заполнили таблицу примерами статей.

Ключевым шагом было добавление полнотекстового индекса с именем article_index к столбцам title и content таблицы articles с помощью оператора ALTER TABLE. Этот индекс необходим для эффективного полнотекстового поиска. Вы проверили создание индекса с помощью SHOW INDEXES.

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