Только для читателей Lifeexample возможно открыть интернет-магазин на Moguta.CMS со скидкой в 15%
Полнотекстовый поиск MySQL
Здравствуйте, уважаемые читатели блога LifeExample, задача полнотекстового поиска по таблицам базы данных, является неотъемлемой, практически, для каждого веб-ресурса. Все новички рано или поздно сталкиваются с вопросом, "как реализовать полнотекстовый поиск по сайту?" конечно сначала задача всплывает несколько иначе: "как сделать поиск по сайту?".
Отличие обычного поиска от полнотекстового в том что, при первом искать сущности сайта, будь то товары магазина или статьи блога, можно по конкретному параметру, например по дате добавления. Полнотекстовый поиск, в свою очередь, подразумевает поиск по совпадающим ключевым словам запроса и текста искомого товара или статьи.
Самым наглядным примером полнотекстового поиска являются поисковые системы, такие как 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 запроса.
Возникли вопросы? Пишите в комментариях! Подписывайтесь на 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, вы его используете, а ведь можно его и не использовать, результаты будут разные….
Начал создавать индекс таблицы, нажимаю Структура и вижу, что у меня среди типов нет FULLTEXT. Начинается с VARCHAR, TINYINT и так далее, а нужного нет. Использую МАМР.
И еще, у меня таблица отличается от вашей: сперва идет #, потом Имя, затем Тип, Сравнение, Атрибуты и тд. Может, не там смотрю?
Перепутал. Не могу найти DESCTEXT!
VARCHAR, TINYINT и тп. это типы полей, а FULLTEXT — индекс таблицы. DESCTEXT — это произвольное название индекса.
Куда вписывать эти FULLTEXT и DESCTEXT? Захожу в таблицу products (там у меня все, что относится к товарам: id, title, description и тд). Жму добавить 1 поле, появляется окно Добавить столбцы. В нем Имя, Тип, Длина/значения и тд. Хотелось бы узнать.
Огромное спасибо за статью. Люблю когда автор, пишет все максимально просто и понятно. Сколько я в интернете прочел подобных статей, но до конца понял как работает полнотекстовый поиск, только прочев эту именно статью. 🙂
Рад, что оказалась полезной.
Большое спасибо, было очень полезно. Нашол то что искал, только в комментах… 😉 Но за статью тоже 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 возьмите в кавычки — тильды.
А REGEXP что-то упустили в описании..:))
И тот кто пишет такое пытается тырить статьи, а сам понимаешь что пишешь?
Оператор LIKE
Оператор REGEXP
Операторы AGAINST и MATCH
Первые два оператора самый примитивный вариант для поиска по сайту, и кроме того очень медлительный и требующий большой производительности сервера.
А операторы AGAINST и MATCH ты хоть знаешь систему их работы, что чушь тут пишешь?
Надо сказать, я потратил не один час на то чтобы понять, почему мой полнотекстовый поиск MySql то работает, то не очень, а ведь все дело оказалось именно в совпадении наименований полей с зарезервированными словами.
Ну и как работают супер быстрые операторы AGAINST и MATCH???)))))