По умолчанию или с модификатором 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)

По умолчанию поиск выполняется без учёта регистра. Чтобы выполнить поиск по полному тексту, учитывая регистр, используйте двоичное упорядочение для индексированных столбцов. Например, столбец, использующий кодовую страницу latin1, может быть назначен упорядочение latin1_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 для каждого столбца.

Также можно выполнить булевый поиск или поиск с расширением запроса. Эти типы поиска описаны в Разделе 12.9.2, «Булевый поиск по полному тексту» и Разделе 12.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.

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

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

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

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