По умолчанию или с модификатором IN NATURAL LANGUAGE MODE функция MATCH() выполняет поиск по полному тексту на естественном языке для строки в коллекции текстов. Коллекция — это набор одного или нескольких столбцов, включенных в индекс FULLTEXT. Строка поиска задается в качестве аргумента для AGAINST(). Для каждой строки в таблице функция MATCH() возвращает значение релевантности; то есть, меру сходства между строкой поиска и текстом в этой строке в столбцах, указанных в списке MATCH().

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO articles (title,body) VALUES
    ->   ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ->   ('How To Use MySQL Well','After you went through a ...'),
    ->   ('Optimizing MySQL','In this tutorial, we show ...'),
    ->   ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ->   ('MySQL vs. YourSQL','In the following database comparison ...'),
    ->   ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

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

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

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

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

Для простого подсчета совпадений можно использовать такой запрос:

mysql> SELECT COUNT(*) FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

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

mysql> SELECT
    -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))
    -> AS count
    -> FROM articles;
+-------+
| count |
+-------+
|     2 |
+-------+
1 row in set (0.03 sec)

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

Для полнотекстового поиска на естественном языке столбцы, указанные в функции MATCH(), должны быть теми же столбцами, включенными в какой-либо индекс FULLTEXT в вашей таблице. Для предыдущего запроса обратите внимание, что столбцы, указанные в функции MATCH() (title и body), совпадают с теми, что указаны в определении индекса article таблицы FULLTEXT. Чтобы искать в столбцах title или body по отдельности, вам необходимо создать отдельные индексы FULLTEXT для каждого столбца.

Вы также можете выполнить булевый поиск или поиск с расширением запроса. Эти типы поиска описаны в разделе 14.9.2, «Булевые полнотекстовые поиски» и 14.9.3, «Полнотекстовые поиски с расширением запроса».

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

Приведенный выше пример — это базовый пример, показывающий, как использовать функцию MATCH(), где строки возвращаются в порядке убывания релевантности. Следующий пример демонстрирует, как явно извлечь значения релевантности. Возвращаемые строки не упорядочены, потому что оператор SELECT не включает ни предложения WHERE, ни предложения ORDER BY:

mysql> SELECT id, MATCH (title,body)
    -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles;
+----+---------------------+
| id | score               |
+----+---------------------+
|  1 | 0.22764469683170319 |
|  2 |                   0 |
|  3 | 0.22764469683170319 |
|  4 |                   0 |
|  5 |                   0 |
|  6 |                   0 |
+----+---------------------+
6 rows in set (0.00 sec)

Следующий пример более сложный. Запрос возвращает значения релевантности, а также сортирует строки в порядке убывания релевантности. Для достижения этого результата укажите функцию MATCH() дважды: один раз в списке SELECT и один раз в предложении WHERE. Это не создает дополнительной нагрузки, так как оптимизатор MySQL замечает, что оба вызова MATCH() идентичны и вызывают код полнотекстового поиска только один раз.

mysql> SELECT id, body, MATCH (title,body)
    ->   AGAINST ('Security implications of running MySQL as root'
    ->   IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles
    ->   WHERE MATCH (title,body)
    ->   AGAINST('Security implications of running MySQL as root'
    ->   IN NATURAL LANGUAGE MODE);
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+
2 rows in set (0.00 sec)

Фраза, заключенная в двойные кавычки ("), соответствует только строкам, которые содержат эту фразу буквально, как она была введена. Полнотекстовый движок разделяет фразу на слова и выполняет поиск в индексе FULLTEXT по словам. Несловесные символы не должны точно совпадать: для поиска по фразам требуется только то, чтобы совпадения содержали ровно те же слова, что и фраза, и в том же порядке. Например, "test phrase" соответствует "test, phrase". Если фраза не содержит слов, которые есть в индексе, результат пуст. Например, если все слова являются стоп-словами или короче минимальной длины индексируемых слов, результат пуст.

Реализация MySQL FULLTEXT рассматривает любую последовательность истинных символов слова (букв, цифр и подчеркиваний) как слово. Эта последовательность также может содержать апострофы ('), но не более одного подряд. Это означает, что aaa'bbb рассматривается как одно слово, а aaa''bbb — как два слова. Апострофы в начале или конце слова удаляются анализатором FULLTEXT; 'aaa'bbb' будет обработано как aaa'bbb.

Встроенный анализатор FULLTEXT определяет начало и конец слов, выявляя определенные символы разделителей; например, пробел ( ), , (запятая) и . (точка). Если слова не разделены разделителями (как, например, в китайском языке), встроенный анализатор FULLTEXT не может определить начало или конец слова. Чтобы добавить слова или другие индексируемые термины на таких языках в индекс FULLTEXT, использующий встроенный анализатор FULLTEXT, необходимо предварительно обработать их так, чтобы они были разделены каким-либо произвольным разделителем. В качестве альтернативы, можно создать индексы FULLTEXT, используя плагин анализатора n-грамм (для китайского, японского или корейского языков) или плагин анализатора MeCab (для японского языка).

Возможно создание плагина, который заменяет встроенный полнотекстовый анализатор. Подробности см. в API плагинов MySQL. Пример исходного кода плагина анализатора см. в каталоге plugin/fulltext дистрибутива исходного кода MySQL.

Некоторые слова игнорируются в полнотекстовых поисках:

См. раздел 14.9.4, «Стоп-слова полнотекстового поиска» для просмотра списков стоп-слов по умолчанию и способов их изменения. Минимальная длина слова по умолчанию может быть изменена, как описано в разделе 14.9.6, «Настройка параметров полнотекстового поиска MySQL».

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

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body)
    -> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);
Empty set (0.00 sec)