Fulltextové vyhľadávanie v MySQL – prax

20. února 2004

•

V predchádzajúcom článku sme si vysvetlili teoretické základy používania fulltextu v MySQL. Tentoraz si ukážeme, ako takýto spôsob vyhľadávania uplatniť v praxi. No a na záver si zhodnotíme klady a zápory fulltextového vyhľadávania v MySQL.

Pýtate sa, kde využiť fulltext? Dá sa povedať, že všade, kde sú v databáze uložené rozsiahlejšie textové informácie. Určite ste sa už stretli s fulltextovým vyhľadávaním pri používaní rôznych vyhľadávačov alebo hľadaní príspevkov v diskusných fórach. A práve to bude našou hlavnou témou.

Vytvorenie databázy

Predpokladajme, že máme web podobný Intervalu. To znamená, že uverejňujeme články a chceme našim návštevníkom ponúknuť možnosť vyhľadávania. Články máme uložené v databáze a zadelené do kategórií. Najprv je potrebné takúto databázu vytvoriť. Nasledujúca časť kódu v SQL ukazuje, ako vytvoriť tabuľky.

# Vytvorenie tabuľky článkov
CREATE TABLE clanky
(
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title varchar(255),
  body text,
  author varchar(100),
  id_category int(11)
)
# Vytvorenie tabuľky kategórií
CREATE TABLE category
(
  id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(50)
)

V tabuľke clanky máme stĺpce id (jednoznačný identifikátor článku), title (titulok článku), body (text celého článku), author (meno autora) a id_category, ktoré nám ukazuje na meno kategórie v tabuľke category. Tabuľka category obsahuje id (jednoznačný identifikátor kategórie) a name (názov kategórie). Takéto rozdelenie údajov nám pre náš ukážkový skript bude úplne stačiť.

Skript vyhľadávania

Teraz sa pustíme do tvorby skriptu, ktorý nám zabezpečí vyhľadávanie. Nazveme ho index.php. Najprv sa musíme pripojiť k databáze. Tu myslím netreba nič vysvetľovať, nasledujúce funkcie sú notoricky známe.

<?
$db_server=“localhost“;      // názov servra
$db_database=“fulltext_search“;  // názov databázy
$db_login=“root“;        // login do databázy
$db_password=““;         // heslo do databázy
// pripojenie na databázu
$db=mysql_connect($db_server,$db_login,$db_password)
  or die(„Pripojenie na server zlyhalo!“);
mysql_select_db($db_database)
  or die(„Pripojenie k tabulke zlyhalo!“);
?>

Po pripojení k databáze vytvoríme v HTML formulár na vyhľadávanie. Bude pozostávať z textového poľa, v ktorom sa bude zadávať hľadaný výraz, z troch radiobuttonov na voľbu vyhľadávania (AND, OR, presne ten istý reťazec), z troch checkboxov, kde sa má vyhľadávať (v mene autora, v titulku článku a v celom článku), a z jedného selectu na určenie kategórie. Všimnite si, ako je select vytváraný. Napevno v HTML vytvárame iba položku (anglicky option) Vsetky s hodnotou 0. Ostatné položky sú generované pomocou PHP tak, že ako názov uvádzame meno kategórie (category.name) a ako hodnotu id (category.id). To nám zabezpečí jednoznačne určiť kategóriu, v ktorej vyhľadávať.

&lt!– Vytvorenie formuláru –>
<html>
<head>
<title>Fulltext search</title>
</head>
<body>
<h1>Vyhľadávanie v databáze článkov</h1>
<form method=“post“>
Hľadaný reťazec: <input type=“text“ name=“searchtext“><br><br>
Slova spájať ako:<br>
<input type=“radio“ name=“operators“ value=“and“> AND<br>
<input type=“radio“ name=“operators“ value=“or“ checked> OR<br>
<input type=“radio“ name=“operators“ value=“quotes“> Hľadať presne taký istý reťazec<br><br>
Hľadat:<br>
<input type=“checkbox“ name=“options[1]“ value=“yes“ checked>
v mene autora<br>
<input type=“checkbox“ name=“options[2]“ value=“yes“ checked>
v titulku článku<br>
<input type=“checkbox“ name=“options[3]“ value=“yes“ checked>
v celom článku<br>
&lt!– Výpis kategórií –>
v kategórií:
<select name=“category“>
<option value=“0″>Všetky
<?
$result=mysql_query(„SELECT * FROM category“);
while($pole=mysql_fetch_array($result))
echo „<option value=\““.$pole[„id“].“\“> „.$pole[„name“];
?>
</select><br><br>
<input type=“submit“>
</form>

Teraz prejdeme na výpis výsledkov. Zistíme, či bol formulár odoslaný, to znamená, či je registrované superglobálne pole $_POST. Ak sa tak stalo, zistíme, v akých položkách máme vyhľadávať a na ktorých vytvoriť FULLTEXT index. Udáva nám to pole $_POST[„options“][]. Ak je tých položiek viac, musíme ich zapísať ako reťazec stĺpcov oddelených čiarkou. Ak nebola zadaná žiadna položka, vypíšeme chybu.

<? // ak už bol formulár poslany (sú registrované premenne $_POST)
if(isset($_POST[„searchtext“]))
{
  // zisťujeme, ktoré checkboxy boli zaškrtnuté
  if($_POST[„options“][1])
    $indexes=“author“;
  if($_POST[„options“][2])
    $indexes.=($indexes!=““ ? „, title“ : „title“);
  if($_POST[„options“][3])
    $indexes.=($indexes!=““ ? „, body“ : „body“);
  // ak nebol ani jeden žaškrtnutý vypíše chybu
  if($indexes==““)
    die(„<b>Chyba pri vyhľadávaní:<br>Nebola zvolená ani jedna voľba, v ktorej hľadať!</b>“);

Ďalej musíme vyselektovať z hľadaného výrazu operátory vyhľadávania. Takto dostaneme reťazec slov oddelených medzerou. Funkcia quotemeta(string) pridáva pred všetky metaznaky regulárnych výrazov v reťazci string spätné lomítka. Otestujeme, či reťazec po vyselektovaní obsahuje aspoň jedno slovo dlhšie ako tri znaky. Ak nie, vypíšeme chybu. Robíme to kvôli tomu, že MySQL parser považuje za slová iba reťazce dlhšie ako tri znaky.

  // vyselektovanie metaznakov z hľadaného reťazca
  $_POST[„searchtext“]=ereg_replace(quotemeta(„+|-|*|~|\“|\\|<|>|(|)“),““,$_POST[„searchtext“]);
  // ak nie je zadaný hľadaný reťazec, vypíše chybu
  if($_POST[„searchtext“]==““)
    die(„<b>Chyba pri vyhľadávaní:<br>Nebol zadaný žiaden reťazec, ktorý treba hľadať!</b>“);
  else
  {
    // rozdelenie reťazca na slova a uloženie do poľa
    $searchtext=explode(“ „,$_POST[„searchtext“]);
    // premenná, do ktorej sa bude ukladať maximalna dĺžka slova
    $maximum=0;
    for($i=0;$i<count($searchtext);$i++)
    {
      $pocet=strlen($searchtext[$i]);
      // ak je dĺžka slova dlhšia ako $maximum, tak sa do
      // premennej $maximum uloží dĺžka tohto slova
      $maximum=($maximum < $pocet ? $pocet : $maximum);
    }
    // ak žiadne slovo nie je dlhšie ako 3 znaky, tak vypíše chybu
    if($maximum<3)
      die(„<b>Chyba pri vyhľadávaní:<br>Aspoň jedno slovo v reťazci musí byť dlhšie ako 3 znaky</b>“);
  }

Podľa toho, aká voľba bola zadaná vo formulári, pridáme operátory. Ak bolo zadané AND, musíme hľadaný reťazec rozdeliť na slová a pred každé pridať operátor „+“, ak bolo zadané hľadať presne taký istý reťazec, pridáme pred a za reťazec úvodzovky, no a napokon ak bolo zadané OR, nemusíme s reťazcom nič vykonávať, pretože pre OR nie je žiaden operátor.

switch($_POST[„operators“])
{
  // ak sa má hľadať presne daný výraz, pridáme úvodzovky na začiatok a koniec reťazca
  case „quotes“:
    $_POST[„searchtext“]=“\““.$_POST[„searchtext“].“\““;
    break;
  // ak je zadané AND, pridáme operátor + pred každé slovo
  case „and“:
    // rozdelenie reťazca na slová
    $words=explode(“ „,$_POST[„searchtext“]);
    //pridanie pred každé slovo operátor +
    for($i=0;$i<count($words);$i++)
      $words[$i]=“+“.$words[$i];
    //spojenie reťazca
    $_POST[„searchtext“]=implode(“ „,$words);
    break;
  // ak je zadané OR nemusíme nič robiť
}

Vytvoríme FULLTEXT index na položkách, ktoré boli zadané vo formulári. Vytvoríme požiadavku $query pre MySQL server, ktorá vyberie všetky záznamy, vyhovujúce podmienke hľadania. Zistíme, či bola zadaná špecifická kategória. Ak áno, pridáme požiadavke ešte podmienku WHERE, kde špecifikujeme id kategórie, v ktorej sa má hľadať. Nakoniec doplníme požiadavku o príkaz ORDER BY, ktorý nám zabezpečí radenie záznamov podľa score. Teraz môžeme požiadavku poslať.

// ak je všetko zadané, potom vytvoríme fulltextový index tabuľky
mysql_query(„ALTER TABLE clanky ADD FULLTEXT search („.$indexes.“)“);
// zapíšeme požiadavku pre MySQL;
$query=“SELECT *,MATCH(„.$indexes.“) AGAINST(‚“.$_POST[„searchtext“].“‚ IN BOOLEAN MODE) as score FROM clanky WHERE MATCH(„.$indexes.“) AGAINST(‚“.$_POST[„searchtext“].“‚ IN BOOLEAN MODE)“;
// ak bola vybraná špecifická kategória, tak pribudne ešte jedna podmienka v požiadavke
if($_POST[„category“]!=0)
{
  $query.=“ AND id_category=“.$_POST[„category“];
  // výpis, v akej kategórií vyhľadávame
  $pole=mysql_fetch_array(mysql_query(„SELECT * FROM category where id=“.$_POST[„category“]));
  echo „Vyhladavame v kategorii: <b>“.$pole[„name“].“</b><br>“;
}
// doplnenie požiadavky o zostupné radenie záznamov podľa score
$query.=“ ORDER BY score DESC“;
// poslanie požiadavky
$result=mysql_query($query);

Nakoniec vypíšeme počet vrátených záznamov, vypíšeme ich a zrušíme FULLTEXT index.

  echo „Počet nájdených vyhovujúcich výsledkov: <b>“.mysql_num_rows($result).“</b><br>“;
  // výpis výsledku
  echo „<table width=\“100%\“ border=\“1\“>
    <caption>Výsledky vyhľadávania</caption>
    <thead>
      <tr>
      <th>SCORE</th>
      <th>AUTOR</th>
      <th>TITULOK</th>
      <th>TEXT</th>
      <th>KATEGÓRIA</th>
      </tr>
    </thead>
    <tbody>“;
  while($pole=mysql_fetch_array($result))
  {
    echo „<tr>
      <td>“.$pole[„score“].“</td>
      <td>“.$pole[„author“].“</td>
      <td>“.$pole[„title“].“</td>
      <td>“.$pole[„body“].“</td>“;
    // zistenie názvu kategórie
    $pole_category=mysql_fetch_array(mysql_query(„SELECT * FROM category WHERE id=“.$pole[„id_category“]));
    echo „<td>“.$pole_category[„name“].“</td>
      </tr>“;
  }
  echo „</tbody>
    </table>“;
  // zrušenie indexu
  mysql_query(„ALTER TABLE clanky DROP INDEX search“);
}
?>
</body>
</html>

A ešte jedná dobrá rada. V našom príklade sme pomocou radiobuttonov menili podmienky vyhľadávania (AND, OR, alebo presne taký istý výraz). Ak chceme poskytnúť plnú silu fulltextového vyhľadávania v MySQL, nemali by sme filtrovať operátory vyhľadávania ale poskytnúť nápovedu, ako používať operátory a čo znamenajú (napríklad nejaké také príklady, aké som uviedol v predchádzajúcom článku). S niečím podobným sa môžete stretnúť napríklad aj u Google. Je už len na vás, pre aký štýl sa rozhodnete a čo ponúknete svojim návštevníkom.

Samozrejme, zdrojový kód príkladu, popísaného v tomto článku, si môžete stiahnuť.

Klady a zápory fulltextu v MySQL

Určite veľa z vás ocení jednoduchosť zápisu hľadaných výrazov a triedenie záznamov podľa frekvencie výskytu slov. To všetko za nás vykoná MySQL. Ale pozor! Jednou nevýhodou je to, že FULLTEXT index môže byť vytvorený iba nad stĺpcami patriacich jednej tabuľke. Keby sme v našom príklade mali mená autorov článkov uvedené v inej tabuľke a odkazovali sa na nich pomocou id, nemohli by sme index vytvoriť. No asi najväčšiu nevýhodu zistíme pri jazykoch, ktoré používajú diakritiku (smola, že k ním patrí ako čeština, tak aj slovenčina), pretože veľká časť užívateľov internetu zadáva hľadané výrazy bez diakritiky. Poviete si, veď MySQL vie pracovať s regulárnymi výrazmi, tak potom kde je problém? Ach len keby to tak bolo. Je pravda, že MySQL pozná regulárne výrazy (viď článok od Roberta Bisoma Jak na regulární výrazy v SQL), ale nie je možné ich použitie vo funkcii AGAINST(). Keby to tak autori MySQL vyriešili, mali by sme po problémoch.