Hledání výrazů a fulltext v MySQL

Klauzule LIKE

Používá se jako podmínka za klíčovým slovem WHERE a slouží k vyhledání záznamů obsahujících určité výrazy.

Znak %

Procento nahrazuje libovolný počet znaků.

SELECT id,login from forum WHERE txt LIKE ('%MySQL%')

Vybere z tabulky jména a čísla všech lidí, co napsali příspěvek obsahující "MySQL".

Znak _

Podtržítko zastupuje jeden libovolný znak.

SELECT id,login from ctenari WHERE login LIKE ('Nov_')

Ukáže čtenáře s příjmením Nový a čtenářky s příjmením Nová.

Co je v normě ale v MySQL chybí

Hranaté zárovky [ ]

Na místě závorky se vyskytuje jeden ze znaků v ní uvedený.

SELECT jmeno,txt from forum
WHERE subject LIKE ('%M[Sy]SQL%')

Vybere z tabulky příspěvky o MySQL nebo o MSSQL.

Hranaté zárovky a pomlčka [-]

SELECT id,jmeno from ctenari
WHERE jmeno LIKE ('[A-F]%')

Vybere čtenáře se jménem začínajícím na A,B,C,D,E nebo F

Hranaté závorky a stříška [^]

Stříška (^) znamená negaci. Uvedené znaky za ní se ve výrazu nemají vyskytovat. Můžeme dané znaky vypsat [^abcde] anebo použít rozsah [^a-e].

Fulltextové vyhledávání

Funguje od verze 3.23.23.
Hodně usnadňuje hledání, protože už není třeba používat složité konstrukce s mnoha podmínkami LIKE, kde se dá snadno udělat chyba.

Funguje na základě indexu typu FULLTEXT. Tento index se musí před vyhledáváním vytvořit buď už při vzniku tabulky anebo později pomocí ALTER TABLE nebo CREATE INDEX. U velkých tabulek ale fulltextový index brzdí vkládání dat, takže je lepší jej vytvářet až před hledáním.

Vytvoření indexu

ALTER TABLE tabulka ADD FULLTEXT vyhledavani
(sloupec1,sloupec2,sloupec3)

sloupce musí být typu varchar anebo text

Vyhledávání

Příkazy na vyhledávání pomocí fulltextového indexu jsou
MATCH(seznam sloupců) AGAINST (výraz)
MySQL hledá výraz v zadaných sloupcích. Tyto musejí mít vytvořený index.
MATCH(seznam sloupců) AGAINST (výraz IN BOOLEAN MODE)
možnost přesnějšího určení podmínek vyhledávání.

SELECT * FROM tabulka WHERE
MATCH(sloupec1,sloupec2,sloupec3) AGAINST('slovo')

Funkce MATCH je case insensitive.

SELECT *, MATCH(sloupec1,sloupec2,sloupec3)
AGAINST('slovo') AS score FROM tabulka

Funkce MATCH spolu s AGAINST vrací celé kladné číslo, tzv. score. Skóre určuje kolikrát byl výraz v tabulce nalezen. Tento příklad vybere všecky záznamy a jejich skóre.

SELECT *, MATCH(sloupec1,sloupec2,sloupec3)
AGAINST('slovo') AS score FROM tabulka
WHERE MATCH(sloupec1,sloupec2,sloupec3) AGAINST('slovo')

Takto se řádky seřadí podle skóre - nahoře s nejvyšším, dole s nejnižším.

Váha slov

Parser MySQL přiřazuje každému slovu z tabulky nějakou "váhu". Slovo je skupina znaků z písmen, číslic, ' znak apostrofu a _ podtrhnutí. Slova s největším výskytem ve sloupci mají nejnižší váhu. Váha některých velmi častých slov může klesnout i na nulu a potom jej fulltextové vyhledávání nenajde.

Kritéria vyhledávání

SELECT * FROM tabulka WHERE
MATCH(sloupec1,sloupec2,sloupec3)
AGAINST('+slovo -neco' IN BOOLEAN MODE)

vybere záznamy obsahující 'slovo' a neobsahující 'neco'. Protože jsme v BOOLEAN MODE, tak je seznam nesetříděný podle score. Připoužití BOOLEAN MODE není třeba mít vytvořený fulltext index ale vyhledávání je potom pomalejší.

nic	výsledkem jsou záznamy obsahující aspoň jedno ze slov v AGAINST
+	toto slovo ve výsledku být musí
-	toto slovo ve výsledku být nesmí
< , >	zvýší nebo sníží slovu score
( )	seskupování a vytvoření složitějších podmínek
~	výrazy začínající tímto znakem budou mít nejnižší score, ale i tak větší než 0, takže budou vráceny.
*	náhrada libovolných znaků - musí být na konci slova
"	ve výsledku budou pouze řádky s přesně takovýmto výrazem

Příklady BOOLEAN MODE hledání

SELECT * FROM tabulka WHERE MATCH(sloupec1,sloupec2,sloupec3)
AGAINST('+banan ~jablko' IN BOOLEAN MODE)

Vybere řádky obsahující banán a jablko, avšak řádky, kdese nachází i jablko, budou mít nižší skóre.

SELECT * FROM tabulka WHERE MATCH(sloupec1,sloupec2,sloupec3)
AGAINST('+banan +(>jablko <hruska)' IN BOOLEAN MODE)

Vybere řádky obsahující banán a jablko a ty řádky, obsahující banán a hruška budou mít nižší skóre.

SELECT * FROM tabulka WHERE MATCH(sloupec1,sloupec2,sloupec3)
AGAINST('banan*' IN BOOLEAN MODE)

Vybere řádky obsahující slova banán, banánový, banány atp...