The OpenNET Project / Index page

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

Полнотекстовый поиск в MySQL на PHP (mysql search php web)


<< Предыдущая ИНДЕКС Поиск в статьях src Установить закладку Перейти на закладку Следующая >>
Ключевые слова: mysql, search, php, web,  (найти похожие документы)
From: Дмитрий Лебедев, Олег Юсов Newsgroups: http://detail.phpclub.net/ Date: Mon, 20 Sep 2004 18:21:07 +0000 (UTC) Subject: Полнотекстовый поиск в MySQL на PHP Оригинал: http://detail.phpclub.net/article/mysql_search Безопасный и удобный поиск в mySQL Дмитрий Лебедев 2000-12-18 Краткая справка по реализации поиска: Обработка строки, вырезание служебных символов, составление запроса к базе, логика, постраничный вывод, релевантность. 1. Общие ведомости 2. Кратко о релевантности 3. Упражнения c релевантностью 4. Продолежение начатого Часть 1: Общие ведомости Обработка строки Первым делом надо порезать ручками строку. $search = substr($search, 0, 64); 64 символов пользователю будет достаточно для поиска. Теперь каленым железом выжжем все "ненормальные" символы. $search = preg_replace("/[^\w\x7F-\xFF\s]/", " ", $search); По идее, нельзя давать пользователю возможности искать по слишком коротким словам - кроме всего прочего, это сильно загружает сервер. Итак, разрешим искать только по словам, которые длиннее двух букв (если ограничение больше, надо заменить "{1,2}" на "{1, кол-во символов}"). $good = trim(preg_replace("/\s(\S{1,2})\s/", " ", ereg_replace(" +", " "," $search "))); А после замены плохих слов - надо сжать двойные пробелы (они были сделаны специально для корректного поиска коротких слов). $good = ereg_eplace(" +", " ", $good); Логика Допустим, мы хотим предоставить пользователю возможность выбирать логику поиска - искать все слова или только одно из нескольких. Если вы хотите сделать как в [27]Яндексе - два амперсанта означают "И" (слово1&&слово2&&слово3) или как-то еще, то я не советчик. Шаманство со строками на небольшом сайте imho не оправдывает затраченного времени. Поэтому форму для поиска рисуем так: <form name="some"> <input type=text name="stroka"> <select name="logic"> <option value="OR">искать любое из слов <option value="AND">искать все слова </select> </form> А в поисковом скрипте лишний раз проверяем, что пользователь ввел: if (($logic!="AND") && ($logic!="OR")) $logic = "OR"; Как будет использоваться логика -- ниже. Статистика поиска Неплохо будет сразу информировать пользователя, сколько он нашел строк таблицы. Для этого делается дополнительный запрос в базу: $query = "SELECT id FROM table WHERE field LIKE '%". str_replace(" ", "%' OR field LIKE '%", $good). "%'"; Для статистики по отдельным словам можно сделать следующее: $word = explode(" ", $search); while (list($k, $v) = each($word)) { if (strlen($v)>2) $stat[]="$v:". mysql_num_rows(mysql_query("SELECT id FROM table WHERE field LIKE '%$v%'")); else $stat[]="$v: <font color=#cc0000>короткое</font>"; }; $word_stats = "Статистика слов: ". implode("", $stat). "<br>"; unset($stat); Постраничный вывод результатов Ну, когда у нас есть макет для поиска и количество строк результата поиска, сделать постраничный поиск - пара пустяков. Проверяем переменную $page (не меньше 0, не больше $results_amount/$rows_in_page).В запрос, который подсчитывает количество строк (смотри выше), пишем нужные нам поля и поля для сортировки. А потом дописываем if ($page==0) $request .= "LIMIT $rows_in_page"; else $request .= "LIMIT ". $page*$rows_in_page. ",". $rows_in_page; (синтаксис: LIMIT <кол-во строк> либо LIMIT <кол-во строк отступа>, <кол-во строк>) В результате выполнения подобного запроса мы получим именно те самые строки, которые надо выводить на странице.Для навигации можно либо рисовать ссылки на следующую и предыдущую страницы, либо, что сложнее, делать панель навигации на несколько страниц. if ($page>0) print ("<a href=search.php?search=". rawurlencode($good). "&page=". ($page-1). ">предыдущая страница</a>"); if ($page<$results_amount/$rows_in_page) print ("<a href=search.php?search=". rawurlencode($good). "&page=". ($page+1). ">следующая страница</a>"); Подсветка Чтобы подсвечивать светом или жирным шрифтом искомые слова в тексте, надо сделать всего лишь следующее: $highlight = str_replace(" ", "|", $good); Пробелы (а они у нас между словами стоят поодиночке, и нигде двойной пробел не встречается, к тому же с концов строки мы их тоже вырезали) достаточно заменить на вертикальную черту - разделитель вариантов в регулярных выражениях. "Плохие" слова мы не подсвечиваем, потому что в базе их не ищем :). В коде, который выводит текст пишем: $row["text"] = ereg_replace($highlight, "<font color=#cc0000>\\0</font>", $row["text"]); После написания выпуска я кинулся, было, писать и себе "подсветку". Не тут-то было! У меня в тексте встречаются теги HTML, поэтому пришлось много подумать... Получилась вот такая вещь (строка со словами для подсветки есть): $text = eregi_replace(">([^<]*)($words)", ">\\1<font color=#cc0000>\\2</font>", $text); Приходится смотреть, нет в теге ли это слово. Однако тут встает проблема ресурсоемкости такой замены (мой K6-266 над текстом в 5 килобайт думал целых семь секунд). Печально. Итог Применяя такие приемы, можно, во-первых, ограничить свободу действий пользователя и не дать ему а) узнать программную структуру сайта б) вызвать перегрузку сервера (например, отправив мегабайт текста, состоящего из слов длиной в три буквы (фраза получилась двусмысленная, но переписывать не буду :), чтобы скрипт 250 тысяч раз лазил в базу) в) увидеть сообщение об ошибке в результате попадания в строку спецсимволов языка запросов. Во-вторых, некоторое удобство для пользователя - постраничный вывод и подсветка. Помнится в статье "Безопасный и удобный поиск" была такая фраза: Часть 2. Кратко о релевантности Олег Юсов Для вывода результатов поиска по релевантности необходимо: * Требуемые поля VARCHAR, либо любые из разновидностей полей TEXT (SMALLTEXT, MEDIUMTEXT и т.п.) сделать ключами FULLTEXT: ALTER TABLE table ADD FULLTEXT(field) * Дальше -- еще проще: $query = "SELECT *, MATCH field AGAINST ('$searchwords') as relev FROM table ORDER BY relev DESC" Далее можно навешивать всякие LIMIT'ы и прочее для удобного вывода. Заметки: * По умолчанию установлен поиск слов, содержащих не менее 4 символов. Правится установкой #define MIN_WORD_LEN 4 в исходнике ft_static.c, хотя на мой взгляд править это не нужно. * Недоступны символы % в поисковой фразе, слова в поисковой фразе парсятся с использованием списка разделетелей. * Список разделителей слов правится в исходнике ft_static.c. * Необходимо минимум десяток записей в таблице для начала вычисления релевантности. * Нельзя поле relev использовать в клаузе WHERE: SELECT *, MATCH field AGAINST ('$searchwords') as relev FROM table WHERE relev>0 ORDER BY relev DESC хотя можно: SELECT *, MATCH field AGAINST ('$searchwords') as relev FROM table WHERE MATCH field AGAINST ('$searchwords')>0 ORDER BY relev DESC * Скорость достаточно высокая -- даже в некоторых случаях быстрее like поиска * Все вышесказанное работает начиная с версии MySQL 3.23.23 При создании индексов FULLTEXT по нескольким полям возможны 2 варианта: CREATE TABLE table ( field1 VARCHAR (255), field2 TEXT, FULLTEXT (field1, field2) ) CREATE TABLE table ( field1 VARCHAR (255), field2 TEXT, FULLTEXT (field1), FULLTEXT (field2) ) В первом случае возможен запрос: SELECT *, MATCH field1, field2 AGAINST ('$searchwords') as relev FROM table ORDER BY relev DESC релевантность вычисляется у всех полей сразу. Во втором случае такой запрос выдаст ошибку. Здесь вычисляем релевантность следующим образом: SELECT *, MATCH field1 AGAINST ('$searchwords')+MATCH field2 AGAINST ('$searchwords') as relev FROM table ORDER BY relev DESC Второй вариант несколько сложнее в запросах, однако, на мой взгляд лучше, т.к. увеличивается гибкость поиска -- к каждому из полей можно задать, например, коэффициент значимости и при суммировании релевантностей полей умножать их на этот коэффициент. Поисковая фраза будет "больше" искаться в полях с большим коэффициентом. Например, если мы делаем поиск по проиндексированным страницам каталога ресурсов, то поле имени страницы обычно задают с большим коэффициентом, чем поля мета-тегов описаний или ключевых слов. Часть 3: Упражнения c релевантностью Сначала как добавить FULLTEXT-индекс: mysql> alter table articlea add fulltext(ztext); ERROR 1073: BLOB column 'ztext' can't be used in key specification with the used table type mysql> alter table articlea type=myisam; Query OK, 36 rows affected (0.60 sec) Records: 36 Duplicates: 0 Warnings: 0 mysql> alter table articlea add fulltext(ztext); Query OK, 36 rows affected (10.00 sec) Records: 36 Duplicates: 0 Warnings: 0 Текстовые индексы можно делать только в таблицах типа MyISAM. Тексты берутся из таблицы и скидываются в файл индекса, и растёт объём базы. По поводу запросов. Нельзя поле relev использовать в клаузе WHERE: SELECT *, MATCH field AGAINST ('$searchwords') as relev FROM table WHERE relev>0 ORDER BY relev DESC хотя можно: SELECT *, MATCH field AGAINST ('$searchwords') as relev FROM table WHERE MATCH field AGAINST ('$searchwords')>0 ORDER BY relev DESC Вычисленное поле, конечно же, нельзя использовать в WHERE по всем правилам синтаксиса, но можно использовать в HAVING: SELECT *, MATCH field AGAINST ('$searchwords') as relev FROM table HAVING relev>0 ORDER BY relev DESC Поиск через MATCH, как писал Олег, делается только по слову целиком. ...Впрочем, по релевантности можно только сортировать, а выбирать по LIKE (это, конечно, скажется на производительности, даже не знаю, насколько). Убираем условие "relev>0", оставляем сортировку. Остальное, как и раньше -- рубим полученную строку и превращаем в запрос с несколькими операторами LIKE: SELECT *,MATCH field AGAINST ('$searchwords') AS relev FROM table WHERE field LIKE '%$word1%' OR field LIKE '%$word2%' ORDER BY relev DESC, datefield DESC Часть 4: Продолежение начатого Продолжаю начатую в сентябре тему поиска с сортировкой по релевантности в базе MySQL. MySQL предлагает в последних версиях базы данных использовать для полнотекстового поиска индексацию FULLTEXT и конструкцию MATCH field AGAINST. Однако не на всех серверах стоит последняя версия MySQL, и не все хостинг-провайдеры хотят обновлять софт по соображениям надежности системы. В своё время я предполагал, что поиск с сортировкой по релевантности надо будет делать в несколько запросов, и, следовательно, лучше вовсе не браться за это. Мысли, что релевантность можно подсчитывать в самом запросе отдалённо меня посещали, но я боялся и представить такую конструкцию. Однако же, работник одной из сайтостроительных фирм Н-ска похвастался мне системой поиска, которую они применяют на своих сайтах. Я точно не запомнил запрос, попробую так воспроизвести его: SELECT title, date_format(material_date,'%e.%c.%y') AS date1, IF(text like '%word1 word2 word3%', 3*10, 0) + IF(text LIKE '%word1%', 9, 0) + IF(text LIKE '%word2%', 9, 0) + IF(text LIKE '%word3%', 9, 0) AS relevance FROM table WHERE text LIKE '%word1%' OR text LIKE '%word2%' OR text LIKE '%word3%' ORDER BY relevance DESC, material_date DESC Ужасно выглядит, но работает даже на старых версиях MySQL. Попробовал сравнить скорость работы с вот таким запросом: SELECT title, date_format(material_date,'%e.%c.%y') AS date1, MATCH text AGAINST('word1 word2 word3') AS relevance FROM table WHERE text LIKE '%word1%' OR text LIKE '%word2%' OR text LIKE '%word3%' ORDER BY relevance DESC, material_date DESC В среднем скорость универсального запроса в два раза меньше, чем использующего новые конструкции. Что вполне логично -- чем больше универсальность, тем больше ресурсоёмкость. Попробуем построить такой запрос автоматически. Отрезаем длинную строку, а так же все неправильные символы и короткие слова. Рисуем запрос. $query = "SELECT title, date_format(material_date,'%e.%c.%y') AS date1, IF(text like '%". $good_words. "%', ". (substr_count($good_words, " ") + 1). "*10, 0) + IF(text LIKE '%". str_replace(" ", "%', 9, 0) + IF(text LIKE '%", $good_words). "%', 9, 0) AS relevance FROM table WHERE text LIKE '%". str_replace(" ", "%' OR text LIKE '%", $good_words). "%' ORDER BY relevance DESC, material_date DESC"; Не очень-то сложно. Для надёжности и защиты от флуда можно ограничить количество слов в запросе. Некоторые дополнения к прежним публикациям Общее количество найденных строк в таблице. Для вывода результатов поиска, разумеется, надо пользоваться оператором LIMIT (чтобы не писать каждый раз формирование этого параметра, пользуйтесь готовыми функциями). Если никаких операций группировки в запросе не делается, лучше подсчитать количество строк сразу в запросе -- COUNT(*), а не через функцию php mysql_num_rows(). Можете проверить на больших таблицах. Если производятся групповые операции, делаем запрос с COUNT(DISTINCT()), но без GROUP BY. Подсветка. Если в текстах не бывает html-тегов, жить проще $text = preg_replace("/word1|word2|word3/i", "<b>\\0</b>", $text); Если в тексте теги используются, то есть три варианта а) не делать подсветку б) поскольку теги пользователь не видит (разве что очень любопытный пользователь), то можно сделать поле индекса, в котором не будет тегов а символы [^\w\x7F-\xFF\s] будут заменены на пробелы (именно эти символы вырезаются из поисковой строки в самом начале, так что поиск по ним не производится). Поиск и подсветку в таком случае сделать именно по индексу. в) делать подсветку текста из обычного поля, предварительно вырезав теги функцией srip_tags(). Полная версия поискового кода, как всегда, в списке файлов. Поиск с разбивкой на страницы (Дмитрий Бородин) http://php.spb.ru/mysql/limit.html

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

Обсуждение [ Линейный режим | Показать все | RSS ]
 
  • 1.1, Spirit, 19:33, 01/04/2005 [ответить] [смотреть все]
  • +/
    Поиск через MATCH, как писал Олег, делается только по слову целиком Впроч... весь текст скрыт [показать]
     
     
  • 2.10, Алексей, 12:25, 02/06/2009 [^] [ответить] [смотреть все]  
  • +/
    По релевантности можно и отсортировать и выбрать, задать критерии поиска, примерно так:
    http://aw-creative.ru/webmasters/1/
     
  • 1.2, LineDir, 00:10, 02/12/2007 [ответить] [смотреть все]  
  • +/
    на счет подсветки..хотел бы посмотреть как она у тебя работает...бред
     
  • 1.3, Игорь, 04:02, 19/01/2008 [ответить] [смотреть все]  
  • +/
    Полнотекстовый поиск в MySQL на PHP (mys, LineDir, 00:10:53, 02/12/2007 [ответить] (2)
    на счет подсветки..хотел бы посмотреть как она у тебя работает...бред

    Не знаю почему это бред, у меня всё работает нормально.

     
  • 1.4, pavel_i, 19:20, 11/02/2008 [ответить] [смотреть все]  
  • +/
    если искомое слово в тексте начинается с большой буквы, то данная подсветка работать не будет
     
  • 1.5, Андрей, 19:32, 14/02/2008 [ответить] [смотреть все]  
  • +/
    Спасибо за статью... очень пригодилась
     
  • 1.6, Юрий, 19:53, 11/03/2008 [ответить] [смотреть все]  
  • +/
    Подъебка это, а не полнотекстовый поиск. Даже Гугл делает это лучше - а он вовсе не шедевр. Парсер поисковых строк должен быть полноценным, а кстати - что делать, если надо индексировать не гипертекстовые или плоские файлы? Как насчет фильтрации? Песта?
     
  • 1.7, KRoT, 00:04, 01/05/2008 [ответить] [смотреть все]  
  • +/
    Вот наработки хорошей системы поиска, думаю, статья вскоре будет дописана, но додумать и так можно...
    http://www.xbrain.ru/content/view/41/10/
    > Подъебка это, а не полнотекстовый поиск

    Скрипты Дмитрия Лебедева никогда не были "подъебкой"! А если у кого-то не хватает ума (до/пере)делать скрипт - ДО-СВИ-ДА-НИ-Я!

     
  • 1.8, ZloY, 08:13, 16/07/2008 [ответить] [смотреть все]  
  • +/
    В статье опечатка
    $good = ereg_eplace(" +", " ", $good);
    ereg_Replace
     
  • 1.9, Memamanopen, 15:14, 04/03/2009 [ответить] [смотреть все]  
  • +/
    Мне кажется все равно полнотекстовый поик на чистом MySQL это бред, медленно.....  по крайней мере можно использовать
    http://www.deepestweb.com/?p=7
     
  • 1.11, Стас, 13:29, 20/05/2010 [ответить] [смотреть все]  
  • +/
    Да это вообще полный бред. Для полнотекстового поиска давно есть специализированные вещи - к примеру Sphinx
     
  • 1.12, robotsall, 08:26, 15/07/2010 [ответить] [смотреть все]  
  • +/
    Отличная статья, очень интересная, остальной сайт еще не смотрел, но если все в таком-же духе, то просто супер.
    З.Ы. Добавил себе в закладки))
     
  • 1.13, Антон, 06:18, 02/08/2010 [ответить] [смотреть все]  
  • +/
    Кули такие умные люди заходят на такие сайты и какают в комментах? хорошая статья - не готовое решение, а, так сказать, набор подсказок, пища для размышлений. Спасибо автору. Если нужен поиск по простому сайту на php+mysql с пивком покатит=)
     
  • 1.14, Саня, 16:34, 27/08/2010 [ответить] [смотреть все]  
  • +/
    Нормальный поиск
     
  • 1.15, Alex, 01:08, 09/03/2011 [ответить] [смотреть все]  
  • +/
    Нормальный поиск для маленького проекта где юзеров online минимум 10 man. Хотите сервер mysql чтобы падал не испоьзуйте для больших проектов типа бд за 100 MB переваливает это если всё в одной таблице myisam. sphinx и создавался для маштабирования бд избавления LIKE, FULLTEXT...
     
  • 1.17, Владимир, 14:25, 24/09/2011 [ответить] [смотреть все]  
  • +/
    Добрый день.
    По умолчанию поиск происходит, если слово не менее 4 символов. Как сделать, чтобы находило слова из 2 символов? Заранее спасибо.
     
  • 1.18, Павел, 11:32, 10/12/2011 [ответить] [смотреть все]  
  • +/
    Спасибо автору! Хорошая статья.. узнал для себя немного новенького и полезного :)
    Не могу понять одного почему у Вас проблема с слов окруженных тегами. Для этого есть strip_tags

    На выходе все чисто и красиво :)

     
  • 1.19, Алексей, 18:25, 19/04/2012 [ответить] [смотреть все]  
  • +/
    Спасибо! Использовал правда только кусочек кода (самому лень думать было:)), но все же автор помог, за что и благодарю:)
     
  • 1.20, Александр, 03:35, 26/03/2014 [ответить] [смотреть все]  
  • +/
    Я понял кое-что. Сидел долго и думал почему не работает, пока не нашел проблему почему у меня не выводит: http://wikikak.org/poisk_po_saytu_php_mysql
    проблема в том, что ограничение нужно поставить и все. Думаю это поможет кому-то.
     

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





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