Безопасный и удобный поиск в mySQL
Автор: Дмитрий Лебедев
Источник: detail.phpclub.net
Краткая справка по реализации
поиска: Обработка строки, вырезание служебных символов,
составление запроса к базе, логика, постраничный вывод,
релевантность.
Часть 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>\\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().
Полная версия поискового кода, как всегда, в списке
файлов.
