Полнотекстовый поиск MySQL
Здравствуйте, уважаемые читатели блога,
задача полнотекстового поиска по таблицам базы данных, является
неотъемлемой, практически, для каждого веб-ресурса. Все новички рано или
поздно сталкиваются с вопросом, "как реализовать полнотекстовый поиск по сайту?" конечно сначала задача всплывает несколько иначе: "как сделать поиск по сайту?".
Отличие обычного поиска от полнотекстового в том что, при первом
искать сущности сайта, будь то товары магазина или статьи блога, можно
по конкретному параметру, например по дате добавления. Полнотекстовый
поиск, в свою очередь, подразумевает поиск по совпадающим ключевым
словам запроса и текста искомого товара или статьи.
Самым наглядным примером полнотекстового поиска являются поисковые
системы, такие как yandex.ru или google.ru, при вводе запроса "Полнотекстовый поиск MySQL" в поисковую строку система разобьет фразу на три слова:
- Полнотекстовый
- поиск
- MySQL
Затем проанализирует имеющиеся сущности, в данном случае это набор
проиндексированных страниц сайтов, на наличие совпадений с каждым
словом. Те страницы, которые имеют хотя бы одно из слов всей фразы либо
их словоформ, будут выведены в поисковую выдачу.
Надо заметить, что поисковые системы обладают множеством алгоритмов позволяющих найти наиболее релевантную информацию по введенной фразе, но мы сегодня создадим самый простейший полнотекстовый поиск с помощью MySQL.
Как сделать полнотекстовый поиск MySQL
Какой бы не казалась задача полнотекстового поиска сложной, решается
она очень быстро и просто. Беря в расчет, что сегодня большинство
сайтов используют в качестве хранилища информации базу данных MySQL, то
далее речь пойдет о реализации поиска по сайту именно по таблицам MySQL.
Реализовать поиск по таблицам MySQL можно разными способами:
- Оператор LIKE
- Оператор REGEXP
- Операторы 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 сводится к минимуму действий:
- Создание индекса для полей занятых в поиске.
- Выполнение sql запроса.
|