- MATCH {столбец!, столбец2,…) AGAINTS {выражение[IN BOOLEAN MODE | WITH QUERY EXPANSION])
Начиная с версии 3.23.23, MySQL поддерживает полнотекстовую индексацию и поиск. Полнотекстовый индекс в MySQL – это индекс типа FULLTEXT. Такие индексы применяются только в таблицах MyISAM, и создаваться могут только на столбцах типа CHAR, VARCHAR или TEXT во время выполнения оператора CREATE TABLE, либо добавляться позже с помощью операторов ALTER TABLE или CREATE INDEX. Для больших наборов данных гораздо быстрее будет сначала загрузить данные в таблицу, которая не имеет индекса FULLTEXT, а затем создать его с помощью ALTER TABLE или CREATE INDEX. Загрузка данных в таблицу, у которой уже есть такой индекс, выполняется ощутимо медленнее. Ограничения на полнотекстовый поиск перечислены в разделе 5.6.3. Полнотекстовый поиск выполняется с применением функции MATCH ().
mysql> CREATE TABLE articles (
-> id INT UNSIGNED AUTOJENCREMENT NOT NULL PRIMARY KEY,
-> title VARCHAR(200),
-> body TEXT,
-> FULLTEXT (title,body)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO articles (title,body) VALUES
-> (‘MySQL Tutorial1 /DBMS stands for DataBase …’),
-> (‘How To Use MySQL Well’,’After you went through a …’),
-> (‘Optimizing MySQL’,’In this tutorial we will 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.00 sec)
Records: б Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST (‘database’);
Функция MATCH() осуществляет естественный языковый поиск строки в текстовой коллекции. Коллекция – это набор из одного или более столбцов, включенных в индекс
FULLTEXT. Искомая строка задается аргументом AGAINST (). Поиск осуществляется в режиме, нечувствительном к регистру. Для каждой строки в таблице MATCH () возвращает релевантное значение, то есть измеренную степень сходства между искомой строкой и текстом в строке таблицы, состоящим из столбцов, перечисленных в списке MATCH ().
Когда MATCH () применяется в конструкции WHERE, как в предыдущем примере, возвращаемые строки автоматически сортируются в порядке убывания релевантного значения (степени сходства). Релевантные значения – это неотрицательные числа с плавающей точкой. Нулевая релевантность означает отсутствие сходства. Оно вычисляется на базе количества слов в строке, количества уникальных слов в строке, общего числа слов в коллекции и количества документов (строк таблицы), содержащих конкретное слово.
Для естественного языкового полнотекстового поиска существует требование, что столбцы, перечисленные в аргументах функции MATCH(), были теми же, по которым построен индекс FULLTEXT вашей таблицы. Заметьте, что в предшествующем запросе столбцы, перечисленные в аргументах функции MATCH() (title и body), – те же самые, что и в определении FULLTEXT-индекса таблицы articles. Если вы хотите искать раздельно по столбцам title и body, то должны создать отдельный FULLTEXT-индекс для каждого столбца.
Существует также возможность булевского поиска или поиска с расширением запроса. Эти типы поиска описаны в разделах 5.6.1 и 5.6.2.
Предыдущий пример – это базовая иллюстрация, показывающая как использовать функцию MATCH (), в которой строки возвращаются в порядке убывания релевантности. Следующий пример демонстрирует, как явно извлекать значения релевантности. Возвращаемые строки не упорядочены, потому что оператор SELECT не содержит ни конструкции ORDER BY, ни конструкции WHERE:
mysql> SELECT id, MATCH (title,body) AGAINST (‘Tutorial’) -> FROM articles;
Представленный ниже пример более сложный. Запрос возвращает значения релевантности, а также строки в порядке его убывания. Чтобы достичь этого результата, вы должны указать MATCH () дважды: один раз в списке столбцов SELECT и второй – в конструкции WHERE. Это не приводит к дополнительной нагрузке, поскольку оптимизатор MySQL определяет, что два вызова MATCH () идентичны и выполняет код полнотекстово-го поиска только один раз.
mysql> SELECT id, body, MATCH (title,body) AGAINST
-> (‘Security implications of running MySQL as root1) AS score
-> FROM articles WHERE MATCH (title,body) AGAINST
-> (‘Security implications of running MySQL as root’);
MySQL применяет очень простой анализатор для разделения текста на слова. "Слово" – это любая последовательность букв, цифр, "’" или "_". Некоторые слова при полнотекстовом поиске игнорируются:
- Любые слишком короткие слова игнорируются. Минимальная длина по умолчанию слова, которое может быть найдено при полнотекстовом поиске, – четыре символа.
- Слова из списка "стоп-слов" игнорируются. Стоп-слова – это слова вроде "the",
"some", которые настолько часто встречаются, что рассматриваются как имеющие нулевое семантическое значение. Существует встроенный список стоп-слов.
Минимальная длина слова по умолчанию и список стоп-слов могут быть изменены, как описано в разделе .Тонкая настройка полнотекстового поиска MySQL.
Каждое правильное слово в коллекции и в запросе "взвешивается" в соответствии с его важностью в коллекции или запросе. Таким образом, слово, представленное во многих документах, имеет меньший вес (может даже иметь нулевой вес), поскольку имеет меньшее семантическое значение в данной коллекции. И наоборот, если слово редкое, то оно получает больший вес. Показатели веса слов затем комбинируются для вычисления релевантности строки.
Такая техника работает наилучшим образом с большими коллекциями (фактически, она специально настроена на это). Для очень маленьких таблиц статистическое распределение слов не отражает адекватно их семантические значения, что иногда может приводить к причудливым результатам. Например, несмотря на то, что слово "MySQL" представлено в каждой строке таблицы articles, поиск по этому слову не дает результата:
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST (‘MySQL’);
Empty set (0.00 sec)
Результат поиска пуст, потому что слово "MySQL" содержится, по меньшей мере, в 50% всех строк. В связи с этим, оно рассматривается как стоп-слово. Для больших наборов данных это наиболее желательное поведение – запрос на естественном языке не должен ежесекундно возвращать строку из таблицы размером, скажем, в 1 Гбайт. Для малых наборов, это, конечно же, менее желательно.
Слово, которому соответствует половина строк таблицы, менее полезно для поиска релевантных документов. Фактически, вероятно, что оно найдет большое число нерелевантных документов. Мы все знаем, что такое случается слишком часто, когда пытаемся найти что-то в Internet с помощью поисковых систем. Это происходит по той причине, что строки содержат слово, имеющее низкое семантическое значение в конкретном наборе данных, в котором оно встречается. Определенное слово может превысить порог "попаданий" в 50% в одном наборе данных, а в другом – нет.
Этот 50%-ный порог, скорее всего, будет достигнут, когда вы впервые пытаетесь выполнить полнотекстовый поиск, чтобы посмотреть, как он работает. Если вы создаете таблицу и вставляете в нее только одну или две строки текста, то получается, что каждое слово текста встречается как минимум в 50% строк. В результате поиск не вернет никакого результата. Для уверенности введите минимум три строки, а лучше – намного больше.