Ключевые слова: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
Полнотекстовый поиск в MySQL на PHP (mys, LineDir, 00:10:53, 02/12/2007 [ответить] (2)
на счет подсветки..хотел бы посмотреть как она у тебя работает...бред
Не знаю почему это бред, у меня всё работает нормально.
Подъебка это, а не полнотекстовый поиск. Даже Гугл делает это лучше - а он вовсе не шедевр. Парсер поисковых строк должен быть полноценным, а кстати - что делать, если надо индексировать не гипертекстовые или плоские файлы? Как насчет фильтрации? Песта?
Вот наработки хорошей системы поиска, думаю, статья вскоре будет дописана, но додумать и так можно...
http://www.xbrain.ru/content/view/41/10/ > Подъебка это, а не полнотекстовый поиск
Скрипты Дмитрия Лебедева никогда не были "подъебкой"! А если у кого-то не хватает ума (до/пере)делать скрипт - ДО-СВИ-ДА-НИ-Я!
Кули такие умные люди заходят на такие сайты и какают в комментах? хорошая статья - не готовое решение, а, так сказать, набор подсказок, пища для размышлений. Спасибо автору. Если нужен поиск по простому сайту на php+mysql с пивком покатит=)
Нормальный поиск для маленького проекта где юзеров online минимум 10 man. Хотите сервер mysql чтобы падал не испоьзуйте для больших проектов типа бд за 100 MB переваливает это если всё в одной таблице myisam. sphinx и создавался для маштабирования бд избавления LIKE, FULLTEXT...
Спасибо автору! Хорошая статья.. узнал для себя немного новенького и полезного :)
Не могу понять одного почему у Вас проблема с слов окруженных тегами. Для этого есть strip_tags
Я понял кое-что. Сидел долго и думал почему не работает, пока не нашел проблему почему у меня не выводит: http://wikikak.org/poisk_po_saytu_php_mysql проблема в том, что ограничение нужно поставить и все. Думаю это поможет кому-то.
