Безопасный и удобный поиск в mySQL

Автор: content Понедельник, Апрель 9th, 2012 Нет комментариев

Рубрика: Язык PHP

Краткая справка по реализации поиска: Обработка строки, вырезание служебных символов, составление запроса к базе, логика, постраничный вывод, релевантность.

Часть 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">искать все слова
	</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>\</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>\</b>», $text);

Если в тексте теги используются, то есть три варианта а) не делать подсветку б) поскольку теги пользователь не видит (разве что очень любопытный пользователь), то можно сделать поле индекса, в котором не будет тегов а символы [^\w\x7F-\xFF\s] будут заменены на пробелы (именно эти символы вырезаются из поисковой строки в самом начале, так что поиск по ним не производится). Поиск и подсветку в таком случае сделать именно по индексу. в) делать подсветку текста из обычного поля, предварительно вырезав теги функцией srip_tags().

Полная версия поискового кода, как всегда, в списке файлов.

Автор: Дмитрий Лебедев

Источник: http://www.php.su/articles/?cat=phpdb&page=015

Оставить комментарий

Чтобы оставлять комментарии Вы должны быть авторизованы.

Похожие посты