Безопасный и удобный поиск в mySQL
Автор:Дмитрий Лебедев
Краткая справка по реализации поиска: Обработка строки, вырезание служебных символов, составление запроса к базе, логика, постраничный вывод, релевантность.
1. Общие ведомости
2. Кратко о релевантности
3. Упражнения c релевантностью
4. Продолежение начатого
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);
Логика
Допустим, мы хотим предоставить пользователю возможность выбирать логику поиска - искать все слова или только одно из нескольких. Если вы хотите сделать как в Яндексе - два амперсанта означают "И" (слово1&&слово2&&слово3) или как-то еще, то я не советчик. Шаманство со строками на небольшом сайте imho не оправдывает затраченного времени. Поэтому форму для поиска рисуем так:
<form name = "some"> <input type = text name = "stroka"> <select name = "logic"> <option value="OR">искать любое из слов <option value="AND">искать все слова </ Выберите> </ Форма>
А в поисковом скрипте лишний раз проверяем, что пользователь ввел:
if (($ logic! = "AND") && ($ logic! = "OR")) $ logic = "ИЛИ";
Как будет использоваться логика — ниже.
Статистика поиска
Неплохо будет сразу информировать пользователя, сколько он нашел строк таблицы. Для этого делается дополнительный запрос в базу:
$ query = "SELECT id ИЗ таблицы, ГДЕ поле LIKE '%". str_replace ("", "% 'OR field LIKE'%", $ good). "%";
Для статистики по отдельным словам можно сделать следующее:
$ word = explode ("", $ search); while (список ($ k, $ v) = каждый ($ слово)) { if (strlen ($ v)> 2) $ Стат [] = "$ v:". mysql_num_rows (mysql_query ("ВЫБЕРИТЕ идентификатор ИЗ таблицы, ГДЕ поле LIKE"% $ v% '")); еще $ stat [] = "$ v: <font color = # cc0000> короткое </ font>"; }; $ word_stats = "Статистика слов:". implode ("", $ stat). "<br>"; снята с охраны ($ стат);
Постраничный вывод результатов
Ну, когда у нас есть макет для поиска и количество строк результата поиска, сделать постраничный поиск - пара пустяков. Проверяем переменную $page (не меньше 0, не больше $results_amount/$rows_in_page).В запрос, который подсчитывает количество строк (смотри выше), пишем нужные нам поля и поля для сортировки. А потом дописываем
если ($ page == 0) $ request. = "LIMIT $ lines_in_page"; еще $ request. = "LIMIT". $ Страница * $ rows_in_page. "". $ Rows_in_page;
(синтаксис: LIMIT <кол-во строк> либо LIMIT <кол-во строк отступа>, <кол-во строк>)
В результате выполнения подобного запроса мы получим именно те самые строки, которые надо выводить на странице.Для навигации можно либо рисовать ссылки на следующую и предыдущую страницы, либо, что сложнее, делать панель навигации на несколько страниц.
если ($ page> 0) print ("<a href = search.php? search =". rawurlencode ($ good). "& page =". ($ page-1). ">предыдущая страница</a>"); если ($ page <$ results_amount / $ lines_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 ADD FULLTEXT (поле)
- Дальше — еще проще:$ query = "SELECT *, поле MATCH AGAINST ('$ searchwords') как релевантное из таблицы ORDER BY релевантным DESC"Далее можно навешивать всякие LIMIT'ы и прочее для удобного вывода.
Заметки:
- По умолчанию установлен поиск слов, содержащих не менее 4 символов. Правится установкой #define MIN_WORD_LEN 4 в исходнике ft_static.c, хотя на мой взгляд править это не нужно.
- Недоступны символы % в поисковой фразе, слова в поисковой фразе парсятся с использованием списка разделетелей.
- Список разделителей слов правится в исходнике ft_static.c.
- Необходимо минимум десяток записей в таблице для начала вычисления релевантности.
- Нельзя поле relev использовать в клаузе WHERE:SELECT *, поле MATCH против ('$ searchwords') как релевантное из таблицы ГДЕ релевантное> 0 ЗАКАЗАТЬ по релевантному DESCхотя можно:SELECT *, поле MATCH против («$ searchwords») как релевантное из таблицы ГДЕ поле MATA против («$ searchwords»)> 0 ЗАКАЗАТЬ по релевантному DESC
- Скорость достаточно высокая — даже в некоторых случаях быстрее like поиска
- Все вышесказанное работает начиная с версии MySQL 3.23.23
При создании индексов FULLTEXT по нескольким полям возможны 2 варианта:
CREATE TABLE стол ( field1 VARCHAR (255), field2 TEXT, FULLTEXT (поле1, поле2) )
CREATE TABLE стол ( field1 VARCHAR (255), field2 TEXT, FULLTEXT (поле1), FULLTEXT (поле 2) )
В первом случае возможен запрос:
SELECT *, MATCH field1, field2 ПРОТИВ ('$ searchwords') как релевантные из таблицы ORDER BY релевантные DESC
релевантность вычисляется у всех полей сразу. Во втором случае такой запрос выдаст ошибку. Здесь вычисляем релевантность следующим образом:
SELECT *, MATCH field1 AGAINST ('$ searchwords') + MATCH field2 AGAINST ('$ searchwords') как релевантные из таблицы ORDER BY релевантной DESC
Второй вариант несколько сложнее в запросах, однако, на мой взгляд лучше, т.к. увеличивается гибкость поиска — к каждому из полей можно задать, например, коэффициент значимости и при суммировании релевантностей полей умножать их на этот коэффициент. Поисковая фраза будет "больше" искаться в полях с большим коэффициентом. Например, если мы делаем поиск по проиндексированным страницам каталога ресурсов, то поле имени страницы обычно задают с большим коэффициентом, чем поля мета-тегов описаний или ключевых слов.
Часть 3: Упражнения c релевантностью
Сначала как добавить FULLTEXT-индекс:
mysql> изменить таблицу articlea add fulltext (ztext); ОШИБКА 1073: столбец BLOB 'ztext' не может использоваться в спецификации ключа с использованием тип таблицы mysql> изменить таблицу articlea type = myisam; Запрос в порядке, затронуто 36 строк (0,60 с) Записи: 36 Дубликаты: 0 Предупреждения: 0 mysql> изменить таблицу articlea add fulltext (ztext); Запрос в порядке, затронуто 36 строк (10,00 с) Записи: 36 Дубликаты: 0 Предупреждения: 0
Текстовые индексы можно делать только в таблицах типа MyISAM. Тексты берутся из таблицы и скидываются в файл индекса, и растёт объём базы. По поводу запросов. Нельзя поле relev использовать в клаузе WHERE:
SELECT *, поле MATCH против ('$ searchwords') как релевантное из таблицы ГДЕ релевантное> 0 ЗАКАЗАТЬ по релевантному DESC
хотя можно:
SELECT *, поле MATCH против («$ searchwords») как релевантное из таблицы ГДЕ поле MATA против («$ searchwords»)> 0 ЗАКАЗАТЬ по релевантному DESC
Вычисленное поле, конечно же, нельзя использовать в WHERE по всем правилам синтаксиса, но можно использовать в HAVING:
SELECT *, поле MATCH AGAINST ('$ searchwords') как релевантное из таблицы ИМЕЮЩЕЕ> 0 ЗАКАЗАТЬ по релевантному DESC
Поиск через MATCH, как писал Олег, делается только по слову целиком. ...Впрочем, по релевантности можно только сортировать, а выбирать по LIKE (это, конечно, скажется на производительности, даже не знаю, насколько).
Убираем условие "relev>0", оставляем сортировку. Остальное, как и раньше — рубим полученную строку и превращаем в запрос с несколькими операторами LIKE:
SELECT *, поле MATCH против ('$ searchwords') как релевантно из таблицы ГДЕ поле LIKE '% $ word1%' ИЛИ поле LIKE '% $ word2%' ЗАКАЗАТЬ по релевантному DESC, поле даты DESC
Часть 4: Продолежение начатого
Продолжаю начатую в сентябре тему поиска с сортировкой по релевантности в базе MySQL.
MySQL предлагает в последних версиях базы данных использовать для полнотекстового поиска индексацию FULLTEXT и конструкцию MATCH field AGAINST. Однако не на всех серверах стоит последняя версия MySQL, и не все хостинг-провайдеры хотят обновлять софт по соображениям надежности системы.
В своё время я предполагал, что поиск с сортировкой по релевантности надо будет делать в несколько запросов, и, следовательно, лучше вовсе не браться за это. Мысли, что релевантность можно подсчитывать в самом запросе отдалённо меня посещали, но я боялся и представить такую конструкцию.
Однако же, работник одной из сайтостроительных фирм Н-ска похвастался мне системой поиска, которую они применяют на своих сайтах. Я точно не запомнил запрос, попробую так воспроизвести его:
ВЫБЕРИТЕ title, date_format (material_date, '% e.% C.% Y') AS date1, IF (текст похож на '% word1 word2 word3%', 3 * 10, 0) + IF (текст LIKE '% word1%', 9, 0) + IF (текст LIKE '% word2%', 9, 0) + IF (текст LIKE '% word3%', 9, 0) КАК релевантность из таблицы ГДЕ текст LIKE '% word1%' ИЛИ текст LIKE ' % word2% 'OR text LIKE'% word3% 'ORDER BY по релевантности DESC, material_date DESC
Ужасно выглядит, но работает даже на старых версиях MySQL. Попробовал сравнить скорость работы с вот таким запросом:
ВЫБЕРИТЕ title, date_format (material_date, '% e.% C.% Y') КАК date1, СООТВЕТСТВУЕТ тексту ПРОТИВ ('word1 word2 word3') КАК релевантность ИЗ таблицы ГДЕ текст LIKE '% word1%' ИЛИ текст LIKE '% word2% 'OR text LIKE'% word3% 'ORDER BY по релевантности DESC, material_date DESC
В среднем скорость универсального запроса в два раза меньше, чем использующего новые конструкции. Что вполне логично — чем больше универсальность, тем больше ресурсоёмкость.
Попробуем построить такой запрос автоматически. Отрезаем длинную строку, а так же все неправильные символы и короткие слова. Рисуем запрос.
$ query = "SELECT title, date_format (material_date, '% e.% c.% y') AS date1, IF (текст похож на '%". $ good_words. "%',". (substr_count ($ good_words, "" ) + 1). "* 10, 0) + IF (текст LIKE '%". Str_replace ("", "%', 9, 0) + IF (текст LIKE"% ", $ good_words)."% ', 9, 0) КАК релевантность из таблицы ГДЕ текст LIKE '% ". str_replace ("", "% 'OR text LIKE'%", $ good_words). "% 'ORDER BY по релевантности 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().
Полная версия поискового кода, как всегда, в списке файлов.
