Только для читателей Lifeexample возможно открыть интернет-магазин на Moguta.CMS со скидкой в 15%

<<< Как работать с vk API вконтакте || PHP работа с изображениями >>>

Полнотекстовый поиск MySQL

13.03.2013
Полнотекстовый поиск MySQL

Здравствуйте, уважаемые читатели блога LifeExample, задача полнотекстового поиска по таблицам базы данных, является неотъемлемой, практически, для каждого веб-ресурса. Все новички рано или поздно сталкиваются с вопросом, "как реализовать полнотекстовый поиск по сайту?" конечно сначала задача всплывает несколько иначе: "как сделать поиск по сайту?".

Отличие обычного поиска от полнотекстового в том что, при первом искать сущности сайта, будь то товары магазина или статьи блога, можно по конкретному параметру, например по дате добавления. Полнотекстовый поиск, в свою очередь, подразумевает поиск по совпадающим ключевым словам запроса и текста искомого товара или статьи.

Самым наглядным примером полнотекстового поиска являются поисковые системы, такие как yandex.ru или google.ru, при вводе запроса "Полнотекстовый поиск MySQL" в поисковую строку система разобьет фразу на три слова:

  • Полнотекстовый
  • поиск
  • MySQL

Затем проанализирует имеющиеся сущности, в данном случае это набор проиндексированных страниц сайтов, на наличие совпадений с каждым словом. Те страницы, которые имеют хотя бы одно из слов всей фразы либо их словоформ, будут выведены в поисковую выдачу.

Надо заметить, что поисковые системы обладают множеством алгоритмов позволяющих найти наиболее релевантную информацию по введенной фразе, но мы сегодня создадим самый простейший полнотекстовый поиск с помощью MySQL.

Как сделать полнотекстовый поиск MySQL

Какой бы не казалась задача полнотекстового поиска сложной, решается она очень быстро и просто. Беря в расчет, что сегодня большинство сайтов используют в качестве хранилища информации базу данных MySQL, то далее речь пойдет о реализации поиска по сайту именно по таблицам MySQL.

Реализовать поиск по таблицам MySQL можно разными способами:

  1. Оператор LIKE
  2. Оператор REGEXP
  3. Операторы AGAINST и MATCH

Первые два оператора самый примитивный вариант для поиска по сайту, и кроме того очень медлительный и требующий большой производительности сервера.

Поиск по сайту с помощью оператора LIKE

Например, реализовать поиск по таблице статей, используя оператор LIKE, можно следующим запросом:

1
SELECT * FROM `aticle` WHERE `content` LIKE “%новость%

Данный запрос выдаст все записи, т.е. все статьи в тексте которых встретится слово "новость", если в содержании самой статьи нет данного слова, но оно в присутствует в заголовке, статья не будет найдена.

Чтобы искать статьи также и по совпадению в заголовках, можно усовершенствовать запрос:

1
2
3
4
SELECT *
FROM  `aticle`
WHERE `content` LIKE “%новость%
   OR `title` LIKE “%новость%

Этот SQL запрос учитывает два текстовых поля, по которым произойдет поиск статьи, но использует уже несколько операторов LIKE, а это двойная нагрузка на сервер. Кроме того если пользователь запросит статьи по фразе "новость дня", то получи те статьи, которые содержат исключительно именно такую фразу: "новость дня".

MySql match – все что нужно для поиска по сайту

В арсенале программиста, использующего mysql, имеется стандартный инструмент для полнотекстового поиска MySql – операторы: MATCH и AGAINST.

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

Используя для реализации полнотекстового поиска MATCH и AGAINST запрос может выглядит так:

1
2
SELECT *
FROM `product` WHERE MATCH(`meta_desc`, `meta_keywords`) AGAINST('Full')

При этом, для работоспособности запроса должен существовать индекс таблицы товаров с типом FULLTEXT, объединяющий в себе перечень полей для поиска:

полнотекстовый-индекс-для-полнотекстового-поиска

Имя индекса может быть любым, а в полях должны быть перечислены, все те, которые будут участвовать в поиске. При данном индексе, показанном на картинке, нельзя делать выборку только по одному из указанных полей.

WHERE MATCH(`meta_keywords`) – такой запрос выдаст ошибку и чтобы ее избежать нужно создать отдельный индекс и включить в него только одно поле (`meta_keywords`).

Для поиска части строки, а не строгого соответствия , нужно использовать модификатор INBOOLEANMODE и символ ‘*‘:

1
2
3
4
5
SELECT *
FROM `product`
WHERE
  MATCH(`meta_desc`, `meta_keywords`)
  AGAINST('*ful*' IN BOOLEAN MODE)

Данный запрос аналогичен результату при использовании LIKE ="%ful%", только использует значительно меньше ресурсов благодаря системе индексов MySQL

Искать не точное соответствие по введенной фразе и соответствие по ее частям можно таким образом:

1
2
3
4
5
SELECT *
FROM `product`
WHERE
  MATCH(`meta_desc`, `meta_keywords`)
  AGAINST('*ful**nam*' IN BOOLEAN MODE)

При таком запросе в результат попадут записи имеющие вхождения фрагментов слов ful и nam

Надо учесть, что при работе с MATCH запрещено использовать в качестве именно полей зарезервированные слова.

Существует список запрещенных к использованию слов, то есть следующими словами нельзя называть поля таблиц, чтобы потом использовать их в полнотекстовом поиске MySql:

a’s able about above according
accordingly across actually after afterwards
again against ain’t all allow
allows almost alone along already
also although always am among
amongst an and another any
anybody anyhow anyone anything anyway
anyways anywhere apart appear appreciate
appropriate are aren’t around as
aside ask asking associated at
available away awfully be became
because become becomes becoming been
before beforehand behind being believe
below beside besides best better
between beyond both brief but
by c’mon c’s came can
can’t cannot cant cause causes
certain certainly changes clearly co
com come comes concerning consequently
consider considering contain containing contains
corresponding could couldn’t course currently
definitely described despite did didn’t
different do does doesn’t doing
don’t done down downwards during
each edu eg eight either
else elsewhere enough entirely especially
et etc even ever every
everybody everyone everything everywhere ex
exactly example except far few
fifth first five followed following
follows for former formerly forth
four from further furthermore get
gets getting given gives go
goes going gone got gotten
greetings had hadn’t happens hardly
has hasn’t have haven’t having
he he’s hello help hence
her here here’s hereafter hereby
herein hereupon hers herself hi
him himself his hither hopefully
how howbeit however i’d i’ll
i’m i’ve ie if ignored
immediate in inasmuch inc indeed
indicate indicated indicates inner insofar
instead into inward is isn’t
it it’d it’ll it’s its
itself just keep keeps kept
know known knows last lately
later latter latterly least less
lest let let’s like liked
likely little look looking looks
ltd mainly many may maybe
me mean meanwhile merely might
more moreover most mostly much
must my myself name namely
nd near nearly necessary need
needs neither never nevertheless new
next nine no nobody non
none noone nor normally not
nothing novel now nowhere obviously
of off often oh ok
okay old on once one
ones only onto or other
others otherwise ought our ours
ourselves out outside over overall
own particular particularly per perhaps
placed please plus possible presumably
probably provides que quite qv
rather rd re really reasonably
regarding regardless regards relatively respectively
right said same saw say
saying says second secondly see
seeing seem seemed seeming seems
seen self selves sensible sent
serious seriously seven several shall
she should shouldn’t since six
so some somebody somehow someone
something sometime sometimes somewhat somewhere
soon sorry specified specify specifying
still sub such sup sure
t’s take taken tell tends
th than thank thanks thanx
that that’s thats the their
theirs them themselves then thence
there there’s thereafter thereby therefore
therein theres thereupon these they
they’d they’ll they’re they’ve think
third this thorough thoroughly those
though three through throughout thru
thus to together too took
toward towards tried tries truly
try trying twice two un
under unfortunately unless unlikely until
unto up upon us use
used useful uses using usually
value various very via viz
vs want wants was wasn’t
way we we’d we’ll we’re
we’ve welcome well went were
weren’t what what’s whatever when
whence whenever where where’s whereafter
whereas whereby wherein whereupon wherever
whether which while whither who
who’s whoever whole whom whose
why will willing wish with
within without won’t wonder would
wouldn’t yes yet you you’d
you’ll you’re you’ve your yours
yourself yourselves zero    

Надо сказать, я потратил не один час на то чтобы понять, почему мой полнотекстовый поиск MySql то работает, то не очень, а ведь все дело оказалось именно в совпадении наименований полей с зарезервированными словами.

Как я и обещал, в начале статьи, задача реализации полнотекстового поиска в MySQL сводится к минимуму действий:

  1. Создание индекса для полей занятых в поиске.
  2. Выполнение sql запроса.

Возникли вопросы? Пишите в комментариях! Подписывайтесь на RSS рассылку, впереди много интересных и полезных статей.

Чтобы не пропустить публикацию следующей статьи подписывайтесь на рассылку по E-mail или RSS ленту блога.

Нравится

Комментарии

  • ЛЕнур

    Упустил из виду то, что движок надо изменить на MyISAM. Поиск с помощью LIKE или REGEXP очень плохо, в FULLTEXT используется MATCH. Статья неплохая, но надо было дополнить про операторы такие как дефис, плюс и тд.
    А так очень неплохо, спасибо за статью.

    • Да, про MyISAM действительно забыл упомянуть.
      А вот про операторы дефис и плюс,
      я к сожалению ничего не знаю, если вы поделитесь знаниями
      об этих операторах, буду признателен.

  • Ленур

    Ну смотрите, примеры которые вы привели вообще то не очень правильные…
    Например это
    AGAINST(‘*ful**nam*’ IN BOOLEAN MODE
    Оператор звездочка он ставится в конец, и является оператором усечения, то есть поиск будет происходить так:
    — nam, name, names и тд.
    Остальные операторы:
    1) Дефис — Этот оператор указывает, что данное ключевое слово не должно содержаться в поле (NOT);
    2) Плюс указывает, что слово должно обязательно содержаться в запросе (AND);
    3) Ковычки — предполагается, что будут слова искаться целиком.
    Это не окончательный список, это все что я помню.
    Кстати есть еще Boolean MODE, вы его используете, а ведь можно его и не использовать, результаты будут разные….

  • Skifskif

    Начал создавать индекс таблицы, нажимаю Структура и вижу, что у меня среди типов нет FULLTEXT. Начинается с VARCHAR, TINYINT и так далее, а нужного нет. Использую МАМР.
    И еще, у меня таблица отличается от вашей: сперва идет #, потом Имя, затем Тип, Сравнение, Атрибуты и тд. Может, не там смотрю?

  • Skifskif

    Перепутал. Не могу найти DESCTEXT!

    • VARCHAR, TINYINT и тп. это типы полей, а FULLTEXT — индекс таблицы. DESCTEXT — это произвольное название индекса.

  • Skifskif

    Куда вписывать эти FULLTEXT и DESCTEXT? Захожу в таблицу products (там у меня все, что относится к товарам: id, title, description и тд). Жму добавить 1 поле, появляется окно Добавить столбцы. В нем Имя, Тип, Длина/значения и тд. Хотелось бы узнать.

  • Dominator

    Огромное спасибо за статью. Люблю когда автор, пишет все максимально просто и понятно. Сколько я в интернете прочел подобных статей, но до конца понял как работает полнотекстовый поиск, только прочев эту именно статью. 🙂

  • Большое спасибо, было очень полезно. Нашол то что искал, только в комментах… 😉 Но за статью тоже Respect, потому что …

  • Дмитрий

    Все отлично, но один вопрос:
    есть запрос:
    SELECT *, (SELECT txtName FROM tblObjects WHERE id=req.ObjectId) as obj,(SELECT txtAddress FROM tblObjects WHERE id=req.ObjectId) as adrs,(SELECT txtName FROM tblTexts WHERE id=req.StatusId) as status FROM tblRequests as req WHERE
    добавляем:
    AND MATCH(`txtBug`, `txtChange`, `txtComments`, `id`) AGAINST(‘*».$_REQUEST[‘find’].»*’ IN BOOLEAN MODE)

    В итоге все работает,
    но когда пытаюсь добавить поля такие как adrs,status — не находит ничего, помогите плиз что можно сделать?

    • adrs,status возьмите в кавычки — тильды.

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

    Подписаться на комментарии к этой статье по RSS

    Яндекс.Метрика