Как да си направим MySQL FULLTEXT търсеща машина?

Тази статия е предназначена да бъде полезна на всички онези колеги по хоби, които не желаят да робуват на ограниченията на безплатните CMS и framework системи.

Какво е MySQL FULLTEXT INDEX?

MySQL предлага възможност за търсене на текст директно в базата данни без да се използват методите на регулярните изрази (regex, pattern-matching operations …).

Има три начина за извършване на FULLTEXT търсене:

  1. Natural language searching – изразът за търсене се разделя на думи и търсещата машина търси редовете, които съдържат тези думи.
  2. Boolean mode searching - изразът за търсене се разделя на думи, но към думите се добавя оператор, който определя допълнителни изисквания към търсенето като например да има или няма определени думи в редовете, да се търси точно въведената дума/фраза или пък думи, съдържащи определен префикс ...
  3. Query expansion searching – този начин на търсене преминава в две фази: първата е познатата ни вече Natural language searching. Втората фаза се извършва като към резултатите от първата фаза се добавят думите от най-близко съответстващите редове и крайният резултат може да съдържа повече редове, отколкото FULLTEXT би върнал само с Natural language searching.

С представените по-долу примери тези опции ще станат съвсем ясни.

FULLTEXT възможностите за търсене се отнасят за една конкретна таблица чрез създаване на специален индекс и има следните характеристики:

  • FULLTEXT търсене на базата на FULLTEXT индекси може да се извършва само за MyISAM таблици.
  • Само текстови колони CHAR, VARCHAR, и TEXT може да се добавят в FULLTEXT индекса.
  • FULLTEXT търсенето пропуска думи, които присъстват в повече от половината редове. Това е много важно да се знае когато се тества опцията за да се създадат поне три реда с текст. Ако редовете са само два, за всяко търсене ще има поне 50% съвпадение и вие никога няма да получите резултат от търсенето !
  • FULLTEXT има вграден списък с stopwords, които винаги се игнорират – the, after, other ...
  • FULLTEXT работи само с думи, които съдържат четири или повече знака.
  • Изразът nice-looking се счита за комбинация от две думи – nice и looking. FULLTEXT търсачката в този случай ще търси редове, които съдържат тези думи. За да се намери точно изразът nice-looking трябва да се ползва Boolean mode searching като се въведе изразът в кавички "nice-looking".
  • FULLTEXT индекс може да се създаде за една колона или за много колони. Ако се отнася за много колони, търсенето става едновременно във всички колони. Ако вашата таблица има две текстови полета и вие желаете в различни случаи да търсите или само в първото, или само във второто, или и в двете заедно, трябва да се създадат три индекса – по един за всяка колона самостоятелно и един за двете колони.

Как рабти MySQL FULLTEXT търсеща машина?

Създаваме база данни, потребител и даваме пълни права на потребителя.

Отваряме базата данни с phpmyadmin и в SQL прозореца изпълняваме следващите команди:

Създаваме таблица с три текстови колони - char, varchar и text:

CREATE TABLE IF NOT EXISTS `fs_table` (

  `id` int(4) NOT NULL AUTO_INCREMENT,

  `text1` char(250) COLLATE utf8_unicode_ci NOT NULL,

  `text2` varchar(2500) COLLATE utf8_unicode_ci NOT NULL,

  `text3` text COLLATE utf8_unicode_ci NOT NULL,

  PRIMARY KEY (`id`) 

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Импортираме информацията, с която ще тестваме FULLTEXT:

INSERT INTO `fs_table` (`id`, `text1`, `text2`, `text3`) VALUES

(1, 'Тенис Tennis', 'Федерер Надал Мъри', 'ракета гейм точки сервис лимит маркет'),

(2, 'Snooker Снукър', 'Селби Хигинс Тръмп', 'маса билярд болд зона точки лимит топка маркетолог'),

(3, 'Формула Formula', 'Макларън Ферари Мерцедес Рено', 'Фетел Шумахер Алонсо маркетинг'),

(4, 'Footbal Футбол', 'Меси Роналдо Руни', 'засада корнер подаване лимит маркиране'),

(5, 'Голф Golf', 'Уудс Микелсен Балестерос Норман', 'дупка топка стик флаг бункер маркер');

Добавяме FULLTEXT индекс към текстовите колони:

ALTER TABLE fs_table ADD FULLTEXT INDEX SEARCH (text1, text2, text3);

Така трябва да изглежда структурата на индексите на таблицата fs_table:

структура на mysql таблица

Кодът за целия тест се съдържа в един единствен файл fsearch.php

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>FSEARCH</title>

</head>

<body>

<div style="margin: 50px auto; width:500px; padding:20px; font-family:Verdana, Geneva, sans-serif; font-size:12px; border:1px solid [#CCC](https://www.icn.bg/bg/blog/?tag=CCC){: .tag-link};">

<form action="<?php $_SERVER['PHP_SELF']; ?>" method="post" />

<input type="text" name="query" style=" width:195px; height:20px;" />

<input type="submit" name="search" value="Search"  />

</form>

<?php 

$query = trim($_POST['query']);

$checkterm = preg_match( "/^[a-zA-Z0-9-_@.+*"p{Cyrillic}s]+$/u", $query); 

if ((strlen($query) < 4) || ($checkterm == 0)) {

echo "<p>Думата за търсене трябва да съдържа нам-малко <strong>четири знака</strong>.<br/>

Разрешени символи: <strong>латиница, кирилица, арабски цифри</strong>, ( <strong>_ - . @</strong> ) без скобите.<br />

Търсенето е <strong>case insensitive</strong> - скрипта не прави разлика между малки и главни букви.";

} else {

$con = mysql_connect ('localhost', 'your-db-user', 'your-db-pass');

$db_sel = mysql_select_db ('your-db-name', $con);

mysql_query ('SET NAMES utf8', $con);

$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."')");

//$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."'IN BOOLEAN MODE)");

//$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."'WITH QUERY EXPANSION)");$nom = mysql_num_rows($res);

if ($nom > '0') {

echo ("<br/>Резултати от търсене за: <strong>$query ($nom)</strong><br/><br/>");

while ($result = mysql_fetch_array($res)) {

echo ("<strong>{$result['text1']}</strong><br/><em>{$result['text2']}</em><br/>{$result['text3']}<br/><br/>");

}

} elseif ($nom == '0') {

echo ("<br/><br/>Търсенето за <strong>$query</strong> не даде резултат.");

}

}

?>

</div>

</body>

</html>

За да работи този код на ред 20 и 21 трябва да въведете името, потребителя и паролата на вашата база данни.

Има няколко реда в кода, на които трябва да обърнем внимание:

Ред 15: $checkterm = preg_match( "/^[a-zA-Z0-9-_@.+*"p{Cyrillic}s]+$/u", $query);

Този ред служи за филтриране на постъпващата от формата за търсене информация (за предотвратяване на mysql инжекция), осигурява работата на допълнителните опции на FULLTEXT както и прави възможно търсенето на кирилица.

Ред 16: if ((strlen($query) < 4) || ($checkterm == 0)) {

Oпределяме минималният брой символи на думата за търсене и проверяваме дали е въведена дума за търсене. При дума за търсене с по-малко от 4 знака или ако не е въведена такава, кодът връща съобщение за грешка (в нашият случай информация за настройките на търсещата машина).

Настройката по подразбиране на MySQL за минимален брой символи също е четири, така че ако в кода няма такава проверка, FULLTEXT няма да върне резултат при въведена дума за търсене с по-малко от 4 знака.

Редове 24, 25 и 26:

$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."')");



//$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."'IN BOOLEAN MODE)");



//$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."'WITH QUERY EXPANSION)");

Това е на практика нашата FULLTEXT търсеща машина.

Тук, както виждате, два от редовете (25 и 26) са изключени със символа // (в оригиналния файл кодът е на един ред, ако във вашият файл кодът е на два реда изключването става с таговете / код / ).

Всеки ред представлява кодът, с който ще демонстрираме всяка една от трите опции на FULLTEXT, другите два реда трябва да бъдат изключени.

Natural language searching

Най-напред искам да демонстрирам правилото за 50% - ако думата за търсене се съдържа в 50% или повече от редовете, FULLTEXT не връща резултат при Natural language searching.

Когато във формата въведем за търсене думата лимит FULLTEXT ни връща празен резултат, въпреки че думата се съдържа в 3 от 5 реда в нашата таблица fs_table.

fulltext 50% limit test

При въвеждане на повече от една дума за търсене, FULLTEXT търси съвпадение за всяка дума поотделно във всички колони едновременно.

natural language searching

Boolean mode searching

Във файлът fsearch.php изключваме ред 24 с две наклонени // , а наклонените пред кода на ред 25 ги изтриваме:

//$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."')");



$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."'IN BOOLEAN MODE)");



//$res = mysql_query("SELECT * FROM fs_table WHERE MATCH (`text1` ,`text2` ,`text3`) AGAINST ('". $query ."'WITH QUERY EXPANSION)");

При Boolean mode правилото за 50% се изключва – търсене за лимит, което при Natural language върна нула резултати, сега връща 3 резултата.

Boolean mode без кавички

Boolean mode позволява добавянето на допълнителни оператори за още по-фино настройване на резултатите от търсенето.

Оператор двойни кавички " "

Въвеждаме фраза за търсене - точки лимит топка (без кавички) и получаваме като резултат четири реда.

Boolean mode с кавички

Сега въвеждаме същата фраза с двойни кавички – „точки лимит топка” и получаваме само един резултат, в който присъстват всички думи и те са подредени по същия начин както в кавичките.

Boolean mode без 50% ограничение

Оператор + -

Търсене за топка точки връща 3 реда с намерени поне една от двете думи.

Boolean mode без допълнителен оператор

Търсене за +топка -точки обаче връща само един ред с думата топка, но без думата точки.

Boolean mode с допълнителен оператор

Оператор wildcart *

Boolean mode позволява добавянето на знак wildcard () в края на думата за търсене и FULLTEXT връща като резултат всички думи, които започват с този префикс. Има едно изискване – броят на символите преди wildcard () да бъде поне равен на минималният брой позволени символи.

Boolean mode wildcard

Търсене за марк* върна като резултат редове, съдържащи: маркер, маркет, маркетинг, маркетолог и маркиране.

Тъй като Boolean mode searching има най-много и най-полезни опции, бих ви препоръчал да ползвате FULLTEXT в този режим.

Query expansion searching

Query expansion търсене преминава в две фази – първата е нормалнo Natural language търсене, към което във втората фаза се прибавят думите от редовете с най-голяма степен на съвпадение от първата фаза. По този начин в крайният резултат обикновено се добавят редове, които само Natural language не показва.

За да демонстрираме как работи Query expansion searching първо ще направим Natural language търсене на думите ракета зона (за целта изключваме ред 25 и включваме ред 24).

Query expansion непълен тест

Сега изключваме ред 24, включваме ред 26, запазваме промените във файла и отново въвеждаме думите за търсене ракета зона

Query expansion реален тест

Имаме още един резултат – думата за съвпадение е топка, съдържаща се в редовете Snooker и Golf, въпреки че тази дума не участва във фразата за търсене.

Настройки на FULLTEXT търсещата машина

Параметрите, които минималният и максималният брой символи в думата за търсене се определят от променливите ftminwordlen и ftmaxwordlen, и имат съответно стойности 4 и 84.

Всички думи/фрази извън тези рамки се игнорират от търсещата машина.

Ако решите да промените минималният брой символи от 4 на 3 трябва да направите следното:

Променете стойността на променливата:

ft_min_word_len=3

и стартирайте MySQL сървъра (ако тествате на ваш сървър или VPS).

За да се запази индекса за всички съществуващи таблици, трябва индекса да се възстанови с функцията REPAIR:

REPAIR TABLE tbl_name QUICK;

Всички нови FULLTEXT индекси ще ползват новата стойност автоматично.

Ако ползвате FULLTEXT на споделен хостинг, след промяна на индекса или въвеждане на нов, трябва да пресъздадете индекса с опцията REPAIR, която се активира от бутона Operations (Oперации) > Repair table (поправяне на таблица).

Желая ви успешно интегриране на MySQL FULLTEXT търсеща машина на вашият сайт.