Ключевые слова:mysql, postgresql, sql, search, database, (найти похожие документы)
From: MEDBEDb <[email protected]>
Newsgroups: email
Date: Mon, 21 Apr 2004 14:31:37 +0000 (UTC)
Subject: Поиск в MySQL/PgSQL
Статья посвящена различным способам поиска в СУБД PgSQL и MySQL. Речь не
пойдет о том какая система лучше или какая из них быстрее. Каждая из них
расчитана на свои задачи. У каждой свои удобства и специфика.
Начнем с элементарного.
Функция LIKE. При помощи этой функции мы с легкостью найдем любое слово
в таблице с символьными переменными.
Возьмем простейшую таблицу для примера:
Таблица N1.
+----+------------------+
! id ! Column !
+----+------------------+
! 1 ! Инфо !
! 2 ! Новая информация !
! 3 ! Формация !
! 4 ! Форма,Содержание !
! 5 ! Новое содержание !
+----+------------------+
Построенный нами запрос:
mysql=> SELECT * FROM table WHERE column LIKE '%инфо%'; => 1
вернет все строки, в которых column содержит 'инфо':
Таблица N2.1
+----+------------------+
! id ! Column !
+----+------------------+
! 1 ! Инфо !
! 2 ! Новая информация !
+----+------------------+
Маленькое отступление. При проведении тестов для данной статьи я
столкнулся с небольшой проблемой, пройдя по форумам я понял что есть
мученники, у которых есть подобные баги... Вот выдержка из одного поста:
>> Ввел слово "бабы" получил 29 ответов, проверил несколько -
>> нигде такого слова нет.
Буквально сегодня налетел на подобные проделки PostNuke'овского поиска
(да собственно, оказалолсь, PostNuke тут не причем - все дело в
особенностях реализации оператора LIKE в БД MySQL, а разработчики
модулей им лишь доверчиво пользуется). Итак, задал я у себя на сайте
поиск по слову "ИУДА", так поиск выдал мне 76 результатов (из 250-то
статей !!! ). Подивившись такой распространенности вроде бы редкого
слова начал разбираться. Оказалось, этот оператор LIKE имеет претензии
на интеллектуальность и пытается искать неправильно написанные слова
(насколько я понял, отличающиеся написанием одной буквой) и в итоге
найденные статьи содержали слова госУДАрство кУДА сУДАрь и даже
(извиняюсь за интимную подробность) мУДАки
И сразу стало понятно, почему мне всегда не нравился поиск в PostNuke
Вывод: вместо LIKE нужно пользоваться REGEXP - он себе таких вольностей
не позволяет. Фишка заключается в следующем - на таблице с 50000
записями провожу запросы LIKE и REGEXP ... И что бы вы думали? LIKE
выдает 10027 записей, REGEXP всего 414!!! Просматривая резульирующие
таблицы я пришел к выводу что LIKE нагло врет... Просмотреть все
результаты было невозможно, и все же REGEXP давал близкие ответы.
Источником проблемы стал CHARSET. System charset был UTF-8, а на базе
стоял latin1. После установки на базу другой кодировки, например
cp1251... LIKE уже выдавал 100 строк, а REGEXP так и оставался на 414.
Только поставив при компиляции
./configure --with-charset=utf-8
я добился нужного результата - 407 строк как в LIKE так и в REGEXP.
Вот. Все просто как никогда. Главное - не надо делать поспешных выводов
о разработчиках модулей. И естественно никакой "интеллектуальности"
LIKE не несет!
Знак '%' означает "любое количество любых символов/либо отсутствие
таковых" (не путать с общепринятым '*' !!! Хотя подразумевается одно и
тоже, но только не в SQL). Частенько пробегая форумы замечешь посты
вроде:
"... почему у меня не работает
SELECT * FROM table WHERE column LIKE '*инфо*';
% [[ ..."
Что сказать... Нужная вещь в ненужном месте - абсолютно бесполезная
штука. Читайте мануал! (К стати к '*' мы еще вернемся!)
Так же существует меньший брат процента - '_' (подчеркивание). Этот
знак означает "один любой символ".
Итак супер символ управления - '%'. Используем его для поиска внутри строки.
При поиске 100%-идентичной строки, либо чисел лучше воспользоваться "=":
SELECT * FROM table WHERE column = 'Новая информация'; => 1
Можно и LIKE, но так куда быстрее! Еще варианты использования:
mysql=> SELECT * FROM table WHERE column LIKE '%и_фо%'; => 1
mysql=> SELECT * FROM table WHERE column LIKE '%инФо%я'; => 1
mysql=> SELECT * FROM table WHERE column LIKE 'орм'; => 0
Все работает замечательно и самое главное - быстро.
Далее смотрим PgSQL:
pgsql=> SELECT * FROM table WHERE column LIKE '%инфо%'; => t
Таблица N2.2
+----+------------------+
! id ! Column !
+----+------------------+
! 2 ! Новая информация !
+----+------------------+
Найдите 10 отличий. (10 не получится ... а вот пара точно есть).
Во-первых: запрос возвращает "t" (true) вместо любимой "1".
Во-вторых: в результате - одна строка вместо двух (см таб.N2.1)
В принципе, первое замечание нам особо не повредит. Мы всеравно отыщем
то, что нам необходимо. И при желании переведем результат в число.
Воспользуемся командой CASE:
SELECT *,column LIKE '%инфо%'
CASE WHERE true THEN 1 ELSE 0 END AS in
WHERE column LIKE '%инфо%';
Таблица N2.3
+----+------------------+----+
! id ! Column ! in !
+----+------------------+----+
! 2 ! Новая информация ! 1 !
+----+------------------+----+
И второе ... где же наша строка "Инфо" ??? В запрос не вошла строка с
заглавной буквой "И". LIKE в MySQL проводит case-insensive поиск, в то
время как в PgSQL - case-sensive. И что же делать?
На самом деле все очень просто ... в отличие от MySQL, PgSQL снабжен
еще парой аналогичных функций! Прошу знакомиться: ILIKE и SIMILAR TO.
ILIKE - абсолютный аналог LIKE в MySQL.
Case-insensive поиск.
pgsql=> SELECT * FROM table WHERE column ILIKE '%и_фо%'; => t
Вот и искомый результат:
Таблица N2.4
+----+------------------+
! id ! Column !
+----+------------------+
! 1 ! Инфо !
! 2 ! Новая информация !
+----+------------------+
С ILIKE все ясно ... Что же такое SIMILAR TO? Как нам объясняют одни
источники - это нечто среднее между стандартными регулярками и функцией
LIKE. Точнее нечто большее чем LIKE, нечто меньшее чем RegExp. Некий
стандарт - SQL'99 Regular Expressions.
От LIKE - SIMILAR TO достались '%' и '_' , а от регулярок унаследовались
'|', '*', '+', '()', '[]' ... вот пожалуй и все ...
А выглядит это все примерно так:
pgsql=> SELECT * FROM table
WHERE column SIMILAR TO '%(о_ма|держа)%'; => t
Таблица N2.5.
+----+------------------+
! id ! Column !
+----+------------------+
! 2 ! Новая информация !
! 3 ! Формация !
! 4 ! Форма,Содержание !
! 5 ! Новое содержание !
+----+------------------+
Великие и ужасные... РЕГУЛЯРКИ!!!
Ими можно сделать все! Ну практически все. Но цена этого - время.
То что "тупой LIKE" сделает за тысячные доли секунды - REGEXP будет
обдумывать в десятки (если не сотни) раз дольше. При малых объемах это
не значительно, но при тысячах обращений даже к небольшой таблице - урон
быстродействию непоправим.
Разберемся с аналогами/синонимами PgSQL ...
~~ = LIKE (case sensive)
~~* = ILIKE (case insensive)
!~~ = NOT LIKE (case sensive)
!~~* = NOT ILIKE (case insensive)
PgSQL=> SELECT * FROM table WHERE column ~~* '%и_фо%'; => t
тоже что и
pgsql=> SELECT * FROM table WHERE column ILIKE '%и_фо%'; => t
Далее регулярные выражения в PgSQL:
~ = regular expresion (case sensive)
~* = regular expresion (case insensive)
тот же пример в регулярных выражениях:
pgsql=> SELECT * FROM table WHERE column ~* 'и.фо'; => t
Регулярные выражения в MySQL:
REGEXP = regular expresion
RLIKE = REGEXP
mysql=> SELECT * FROM table WHERE column RLIKE 'и.фо'; => 1
Подробнее о синтаксисе регулярных выражений в последующих статьях.
А сейчас от PgSQL к MySQL ...
Еще один нерассмотренный прием: MATCH AGAINST ... С претензией на
интеллект! Так называемый FULL TEXT SEARCH (начиная с MySQL v.3.23.23).
Функция является специфичной и не везде используется.
Для начала посмотрим как правильно создать таблицу:
mysql=> CREATE TABLE table (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
column TEXT,
FULLTEXT (column)
);
Ключевое слово "FULLTEXT"!!!
Ну вот мы создали нашу табличку, заполнили ее нужными INSERT-ами ...
Приступаем к выборке:
mysql=> SELECT * FROM table WHERE
MATCH (column) AGAINST ('информация'); => rel
Данный запрос производит "естественный языковой поиск" текста в нужных
текстовых колонках. В подобные запросы попадает множество подходящих и
не очень подходящих строк. Но вся прелесть MATCH AGAINST заключается в
получении "коэфициента релевантности":
mysql=> SELECT *,
MATCH (column) AGAINST ('информация') AS rel
FROM table WHERE MATCH (column) AGAINST ('информация');
В колонку 'rel' у нас попадают некие величины, максимальная из которых
указывает на самое подходящее выражение.
Так же в поле AGAINST() можно регулировать запрос в нужной нам форме,
отсекая ненужные словосочетания:
AGAINST ('форма содержание')
... все выражения, содержащие хотя бы одно из слов.
AGAINST ('+форма +содержание')
... оба слова.
AGAINST ('+форма содержание')
... искать "форма", но при наличии "содержание"
выдавать больший релевант.
AGAINST ('+форма -содержание')
... "форма" без "содержание".
AGAINST ('+форма +(>содержание <формация)')
... "форма" и "содержание",
или "форма" и "формация" (в любом порядке),
но у "форма содержание" релевант больше,
чем у "форма формация".
AGAINST ('форма*')
... "форма", "формат", "формация" ...
AGAINST ('"форма или содержание"')
... "форма или содержание в новом свете"
но не "форма и содержание".
p.s. Итак от MySQL к PgSQL и обратно. До функции MATCH AGAINST, которой
в базовой версии PostgreSQL нет альтернативы, все примеры шли в порядке
возрастания возможностей и соответственно тормознутости выполнения.
С FULLTEXT SEARCH все гораздо забавнее. Работает на удивление
относительно быстро, выдает некий relevant, весьма удобный (хотя и
весьма подозрительный и нестабильный от запроса к запросу) для
сортировки результатов.
Выбирайте и пробуйте, пробуйте, пробуйте ...
