OpenNET: стаття - Пошук в MySQL / PgSQL (mysql postgresql sql search database)

Пошук в MySQL / PgSQL (mysql postgresql sql search database)

Пошук в MySQL / PgSQL (mysql postgresql sql search database)

Ключові слова: 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 ( ' "форма чи зміст"') ... "форма або зміст в новому світлі" але не "форма і зміст ". ps Отже від MySQL до PgSQL і назад. До функції MATCH AGAINST, якої в базовій версії PostgreSQL немає альтернативи, все приклади йшли в порядку зростання можливостей і відповідно тормознутости виконання. З FULLTEXT SEARCH все набагато цікавіша. Працює на подив щодо швидко, видає якийсь relevant, вельми зручний (хоча і вельми підозрілий і нестабільний від запиту до запиту) для сортування результатів. Вибирайте і пробуйте, пробуйте, пробуйте ...

Обговорення [ RSS ]