The OpenNET Project / Index page

[ новости/++ | форум | wiki | теги ]

Поиск в MySQL/PgSQL (mysql postgresql sql search database)


<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>
Ключевые слова: mysql, postgresql, sql, search, database,  (найти похожие документы)
From: MEDBEDb <interro@mail.ru> 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, весьма удобный (хотя и весьма подозрительный и нестабильный от запроса к запросу) для сортировки результатов. Выбирайте и пробуйте, пробуйте, пробуйте ...

<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>

Обсуждение [ RSS ]
 
  • 1.1, Konstantin, 15:24, 23/12/2005 [ответить] [смотреть все]
  • +/
    В postgres contrib советую посмотреть модуль tsearch2. По моему очень не плохо реализован полнотекстовый поиск с довольно неплохой релевантностью.
     
  • 1.2, john_j, 06:26, 23/07/2008 [ответить] [смотреть все]
  • +/
    > Еще один нерассмотренный прием: MATCH AGAINST ... С претензией на

    интеллект!

    Интересно, а как этот "интелект" дружит с русскими словами ???

     
  • 1.3, migon, 11:24, 28/10/2009 [ответить] [смотреть все]
  • +/
    Отлично дружит!
     
  • 1.4, Oleg, 18:02, 13/01/2010 [ответить] [смотреть все]
  • +/
    Убери % в LIKE, и его "интеллект" исчезнет. А у тебя может появится.
     
  • 1.5, Антон, 15:20, 17/11/2015 [ответить] [смотреть все]
  • +/
    Почему в этом варианте:
    AGAINST ('+форма +содержание')
      ... оба слова.

    При запросе Железный человек, выводится в первых рядах Человек паук ?

    MATCH (title) AGAINST ('+Железный человек')

     
  • 1.6, Антон, 15:25, 17/11/2015 [ответить] [смотреть все]  
  • +/
    MATCH (title) AGAINST ('+Железный +человек')
     
  • 1.7, Unkas, 21:00, 11/05/2016 [ответить] [смотреть все]  
  • +/
    Ни фига ILIKE не абсолютный аналог LIKE - он отличается тем что делает искомое поле нечувствительным к регистру
     

    Ваш комментарий
    Имя:         
    E-Mail:      
    Заголовок:
    Текст:





      Закладки на сайте
      Проследить за страницей
    Created 1996-2017 by Maxim Chirkov  
    ДобавитьРекламаВебмастеруГИД  
    Hosting by Ihor