Fulltextové vyhľadávanie v MySQL – teória

6. února 2004

•

MySQL od verzie 3.23.23 podporuje fulltextové vyhľadávanie a indexovanie. Pýtate sa, v čom nám niečo také pomôže? No napríklad už nie je potrebné vymýšľať „šialené“ žiadosti (anglicky query, česky dotazy), v ktorých je nespočetné množstvo podmienok a kde sa veľmi ľahko môže vyskytnúť nejaká chybička, a nemusíme vymýšľať rôzne algoritmy na podmienky vyhľadávania. Fulltextové indexovanie prináša veľmi elegantné riešenie, na ktoré sa teraz spoločne pozrieme.

Vytváranie FULLTEXT indexu

Na začiatok si uveďme trochu teórie. Ako prvé treba určite spomenúť, že fulltextové vyhľadávanie prebieha na základe indexu typu FULLTEXT. Tento index je možné vytvoriť iba nad stĺpcami typu varchar alebo text a to pri vzniku tabuľky (v príkaze CREATE TABLE), alebo neskôr (pomocou príkazu ALTER TABLE alebo CREATE INDEX). Pre veľké dátové množiny je omnoho rýchlejšie nahrávanie do tabuliek, v ktorej nie sú vytvorené žiadne FULLTEXT indexy. Tie je možné vytvoriť neskôr. Ukážme si teda formálny zápis príkazu pre vznik fulltextového indexu:

FULLTEXT [názov indexu] (zoznam stĺpcov): Vytvorí fulltextový index s názvom názov indexu nad stĺpcami, ktoré sú uvedené v reťazci zoznam stĺpcov. Jednotlivé názvy stĺpcov sú oddelené čiarkou. Názov indexu je nepovinný parameter.

Jeden názorný príklad na lepšie pochopenie:

ALTER TABLE tabulka ADD FULLTEXT vyhladavanie (stlpec1, stlpec2, stlpec3)

Tento príkaz vytvorí fulltextový index s názvom vyhladavanie v tabuľke tabulka nad stĺpcami stlpec1, stlpec2 a stlpec3.

Príkazy na vyhľadávanie

Samotné vyhľadávanie prebieha pomocou funkcií MATCH() a AGAINST(). Ukážme si formálny zápis týchto funkcií:

MATCH(zoznam stĺpcov) AGAINST (výraz): MySQL hľadá výraz nad stĺpcami zoznam stĺpcov. Parameter výraz je ľubovolný textový reťazec. Ak sú v parametri zoznam stĺpcov uvedené stĺpce tabuľky, nad ktorými nie je vytvorený fulltext index, MySQL hlási chybu.
MATCH(zoznam stĺpcov) AGAINST (výraz IN BOOLEAN MODE): Pracuje skoro rovnako ako predchádzajúci príkaz, avšak vďaka modifikátoru IN BOOLEAN MODE presnejšie určujeme podmienky vyhľadávania. Príkaz je dostupný od MySQL 4.0.1.

Ak ste z predchádzajúcich zápisov nepochopili ako vyhľadávať, nezúfajte. Všetko si podrobne vysvetlíme na nasledujúcich príkladoch.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚slovo‘)

Takýto zápis spôsobí, že MySQL hľadá v tabuľke tabulka všetky záznamy, ktoré obsahujú reťazec slovo v stĺpci stlpec1,stlpec2 alebo stlpec3.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚slovo‘)
SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚SLOVO‘)
SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚sloVO‘)

Tieto tri zápisy sú úplne rovnaké, pretože funkcia AGAINST je case-insensitive, čo značí, že na veľkosti písmen hľadaného výrazu nezáleží.

SELECT *, MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚slovo‘) AS score FROM tabulka

Dvojica funkcii MATCH() AGAINST() vracia určité kladné číslo, takzvané score. Ak je toto score rovné nule, hľadaný výraz sa v zázname nenašiel. Čím vyššie je skóre, tým je väčší výskyt hľadaného výrazu v danom zázname. Uvedený príkaz vyberie všetky záznamy a každému záznamu pridelí score.

SELECT *, MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚slovo‘) AS score FROM tabulka
WHERE MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚slovo‘)

Ak je dvojica funkcií uvedena MATCH() AGAINST() v podmienke WHERE, výsledok je radený podľa score jednotlivých záznamov zostupne (prvý bude záznam s najväčším score, posledný s najmenším) a záznamy s nulovým score nie sú vybrané.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3) AGAINST(‚dva‘)

Tento príkaz nevráti ani jeden záznam. Prečo? MySQL používa jednoduchý parser na „vyťahovanie“ slov z textov. Tu si treba uvedomiť, čo považuje MySQL za slovo. Podľa parsera MySQL je slovo skupina znakov pozostávajúcich z písmen, číslic, ‚ (znak apostrofu) a _ (podtrhnutie). Takže napríklad emailovú adresu webmaster@server.com rozdelí na dve slová a to webmaster a server (com MySQL parser nebude brať ako slovo – vysvetlené ďalej). Každému slovu prideľuje určitú „váhu“. Čím je váha nižšia, tým viac sa dané slovo v stĺpci vyskytuje. Slová s nulovou váhou sú pri vyhľadávaní ignorované. Slová, ktoré majú tri a menej znakov (náš uvedený príklad hore a príklad s emailovou adresou), majú nulovú váhu. Takisto sa vám môže stať, že zadáte slovo, ktoré má viac ako tri znaky a určite sa v databáze vyskytuje, ale SELECT vám nevráti nič. Prečo? Je to kvôli tomu, že jeho váha klesla na nulu. Určite nechcete, aby po zadaní toho vášho slova databáza, ktorá obsahuje niekoľko tisíc záznamov, vám vrátila polovicu z nich ako výsledok. Takto budete mať ešte dosť práce, kým nájdete presne ten záznam, ktorý hľadáte.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚+slovo -nieco‘ IN BOOLEAN MODE)

Kritéria vyhľadávania

Ako som sa už na začiatku zmienil, modifikátor IN BOOLEAN MODE nám umožňuje zadávať kritéria vyhľadávania. Ako prvé chcem pripomenúť, že ak je uvedený modifikátor IN BOOLEAN MODE, tak SELECT nám nevráti záznamy zoradené podľa score. To však môžeme dosiahnuť príkazom ORDER BY. Príklad, ktorý sme si uviedli, teda vráti nezotriedený zoznam záznamov, ktoré musia obsahovať slovo slovo a nesmú obsahovať slovo nieco. Skôr ako si vysvetlíme operátory špecifikujúce kritérium vyhľadávania, chcem pripomenúť, že ak používame modifikátor IN BOOLEAN MODE, nemusíme mať vytvorený FULLTEXT index, avšak vyhľadávanie je potom pomalšie.

(žiaden): Ak nie je uvedený žiaden operátor (plus, mínus), tak výsledkom vyhľadávania sú záznamy, ktoré obsahujú aspoň jedno z uvedených slov.
+ (plus): Ak operátorom + začína hľadaný výraz, výsledkom vyhľadávania sú všetky riadky, ktoré musia obsahovať tento výraz.
– (mínus): Operátor – pred hľadaným výrazom značí, že výsledkom vyhľadávania budú tie riadky, v ktorých sa toto slovo nesmie vyskytovať.
< (menšie), > (väčšie): Tieto dva operátory slúžia na zvýšenie (>) alebo zníženie (<) score. Najlepšie to pochopíte z príkladu uvedenom nižšie.
( ) (zátvorky): Slúžia na zoskupovanie a vytvorenie zložitejších podmienok.
~ (tilda): Je to operátor negácie. Všetky výrazy začínajúce týmto znakom budú mať najnižšie score, ale i tak to score nebude rovné nule. To znamená, že záznamy budú vrátené. Jednoducho, je to slabšia odroda operátora – (mínus).
* (hviezdička): Ako jediný zo všetkých operátorov je pridávaný na koniec hľadaného výrazu. Slúži ako náhrada ľubovolných znakov.
“ (uvodzovky): Výsledkom vyhľadávania výrazu uzavretého dvojitými úvodzovkami sú tie riadky, ktoré obsahujú presne takýto výraz.

Príklady použitia operátorov

Uvediem niekoľko príkladov použitia operátorov na lepšie pochopenie.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚banan jablko‘ IN BOOLEAN MODE)

Vráti tie riadky, ktoré obsahujú slovo banan alebo slovo jablko, alebo obsahujú obidve slová.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚+banan +jablko‘ IN BOOLEAN MODE)

Vráti iba tie riadky, ktoré obsahujú obidve slová, teda aj banan aj jablko.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚+banan jablko‘ IN BOOLEAN MODE)

Vráti všetky riadky, ktoré obsahujú banan, ale riadky, ktoré obsahujú aj jablko budú mať vyššie score.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚+banan -jablko‘ IN BOOLEAN MODE)

Vráti tie riadky, ktoré obsahujú slovo banan a zároveň nesmú obsahovať slovo jablko.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚+banan ~jablko‘ IN BOOLEAN MODE)

Vráti tie riadky, ktoré obsahujú slovo banan, avšak riadky, kde sa nachádza aj jablko, budú mať najnižšie score.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚+banan +(>jablko <hruska)‘ IN BOOLEAN MODE)

Vrátené budú tie riadky, ktoré obsahujú slová banan a jablko, a tie riadky, ktoré obsahujú slová banan a hruska, avšak tie budú mať nižšie score.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚banan*‘ IN BOOLEAN MODE)

Takýto výraz odpovedá napríklad slovám banan, bananovy, banana a tak ďalej.

SELECT * FROM tabulka WHERE MATCH(stlpec1, stlpec2, stlpec3)
AGAINST(‚“banan jablko“‚ IN BOOLEAN MODE)

Tomuto výrazu vyhovuje napríklad Dostal som banan jablko a hrusku a nevyhovuje napríklad Dostal som banan a jablko, hrusku uz nemali. V tomto prípade je tento výraz braný ako jedno slovo a hľadá sa presne také isté vo vete.