По умолчанию или с модификатором 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, как показано в примере ранее, возвращаемые строки автоматически сортируются по убыванию релевантности, при соблюдении следующих условий:

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

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

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

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 с помощью плагина анализатора ngram (для китайского, японского или корейского языков) или плагина анализатора 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)