MySQL fulltext: prosil bych jeden čaj

PHP MySQL Fulltext

Mějme tabulku jídel na kterou chceme napsat hledání.

CREATE TABLE `food` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `description` text COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

INSERT INTO `food` (`name`, `description`) VALUES
('Semtex', 'energiťák'), ('Čaj', 'heřmánkovej');

Máme dvě možnost, buďto použijeme search engine (ElasticSearch, Sphinx, …) nebo se s tím budeme srát v MySQL. No a aby to bylo zajímavé, tak se s tím pojďme srát :)

InnoDB neumí FULLTEXT index

První problém, jak ho vyřešit? Triggery.

Takže si vytvoříme tabulku do které budeme duplikovat data (což je v podstatě to stejné co byste dělali s externí službou na hledání)

CREATE TABLE `food_fulltext` (
  `food_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_czech_ci NOT NULL,
  `description` text COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`food_id`),
  FULLTEXT KEY `name_description` (`name`,`description`),
  FULLTEXT KEY `name` (`name`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

A napíšeme si triggery

DELIMITER ;;

CREATE PROCEDURE `food_fulltext_update` (IN `updated_id` int(11))
BEGIN
    DECLARE `name` TEXT ;
    DECLARE `description` TEXT ;

    SELECT food.`name`, food.`description` INTO `name`, `description`
    FROM food WHERE `id` = `updated_id`;

    INSERT INTO `food_fulltext` (`food_id`, `name`, `description`) VALUES (`updated_id`, `name`, `description`)
    ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `description` = VALUES(`description`);
END;;

CREATE TRIGGER `food_ai` AFTER INSERT ON `food` FOR EACH ROW
IF @disable_triggers IS NULL THEN
    CALL food_fulltext_update(NEW.`id`);
END IF;;

CREATE TRIGGER `food_au` AFTER UPDATE ON `food` FOR EACH ROW
IF @disable_triggers IS NULL THEN
    CALL food_fulltext_update(NEW.`id`);
END IF;;

CREATE TRIGGER `food_ad` AFTER DELETE ON `food` FOR EACH ROW
IF @disable_triggers IS NULL THEN
    DELETE FROM food_fulltext WHERE `food_id` = OLD.`id`;
END IF;;

DELIMITER ;

INSERT INTO `food_fulltext` (`food_id`, `name`, `description`)
SELECT food.`id`, food.`name`, food.`description` FROM food

Ta proměnná @disable_triggers je vychytávka, aby se daly triggery vypnout při hromadných operacích nad daty. Trigger může jednoduchý update spomalit klidne i exponenciálně (fakt se to hodí mít možnost vypnout).

Fajn, takže při zápisu do tabulky s jídly se nám data zprelikují pod fulltext a můžeme hned začít s hledáním.

Hledáme Semtex

Takhle nějak by mohla vypadat search query (inspirovaná článkem od Jakuba)

SELECT food_id FROM food_fulltext
WHERE MATCH(name, description) AGAINST (? IN BOOLEAN MODE)
ORDER BY 5 * MATCH(name) AGAINST (?) + MATCH(description) AGAINST (?) DESC
LIMIT 1000

a když ji pak proženeme přes Nette\Database\Context

function search($string)
{

    $sql = "...";

    return $this->db->query($sql, $string, $string, $string)->fetchAll();
}

s hledaným výrazem od uživatele

dump($fulltext->search("Semtex")); // [['food_id' => 1]]

Super, našli jsme Semtex!

Hledáme Čaj

Jenže když dáme hledat čaj tak máme problém (konkrétně dva)

dump($fulltext->search("Čaj")); // []

Ten první je, že mysql má výchozí minimální délku slova pro fulltext větší než 3, to se dá změnit celkem snadno

$ sudo nano /etc/mysql/my.cnf

[mysqld]
# Fine Tuning
ft_min_word_len = 3

$ sudo service mysql restart

REPAIR TABLE `food_fulltext` QUICK;

Ten druhý problém je, že dost agresivně zohledňuje diakritiku, takže na dotaz "Čaj" se nám sice vrátí výsledek, ale na dotaz "caj" se nevrátí nic.

Zbavujeme se diakritiky

Protože chceme mít proces automatický, abychom nemuseli řešit ukládání do dvou tabulek, tak máme triggery. A protože máme triggery, musíme dělat konverzi na úrovni databáze.

DELIMITER ;;

--
-- https://github.com/falcacibar/mysql-routines-collection/blob/28ef383092ffa5a0e4e7e377fa5d1a3badcc488c/tr.func.sql
-- @author Felipe Alcacibar <[email protected]>
--
CREATE FUNCTION `strtr`(`str` TEXT, `dict_from` VARCHAR(1024), `dict_to` VARCHAR(1024)) RETURNS text LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY INVOKER COMMENT ''
BEGIN
    DECLARE len INTEGER;
    DECLARE i INTEGER;

    IF dict_to IS NOT NULL AND (CHAR_LENGTH(dict_from) != CHAR_LENGTH(dict_to)) THEN
        SET @error = CONCAT('Length of dicts does not match.');
        SIGNAL SQLSTATE '49999'
            SET MESSAGE_TEXT = @error;
    END IF;

    SET len = CHAR_LENGTH(dict_from);
    SET i = 1;

    WHILE len >= i  DO
        SET @f = SUBSTR(dict_from, i, 1);
        SET @t = IF(dict_to IS NULL, '', SUBSTR(dict_to, i, 1));

        SET str = REPLACE(str, @f, @t);
        SET i = i + 1;

    END WHILE;

    RETURN str;
END;;

CREATE FUNCTION `to_ascii`(`str` TEXT) RETURNS text LANGUAGE SQL DETERMINISTIC NO SQL SQL SECURITY INVOKER COMMENT ''
BEGIN
    RETURN strtr(LOWER(str), 'áäčďéěëíµňôóöŕřšťúůüýžÁÄČĎÉĚËÍĄŇÓÖÔŘŔŠŤÚŮÜÝŽ', 'aacdeeeilnooorrstuuuyzaacdeeelinooorrstuuuyz');
END;;

Upravíme proceduru která synchronizuje fulltext

DELIMITER ;;

DROP PROCEDURE `food_fulltext_update`;;
CREATE PROCEDURE `food_fulltext_update` (IN `updated_id` int(11))
BEGIN
    DECLARE `name` TEXT ;
    DECLARE `description` TEXT ;

    SELECT to_ascii(food.`name`), to_ascii(food.`description`) INTO `name`, `description`
    FROM food WHERE `id` = `updated_id`;

    INSERT INTO `food_fulltext` (`food_id`, `name`, `description`) VALUES (`updated_id`, `name`, `description`)
    ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `description` = VALUES(`description`);
END;; -- 0.001 s

A ještě upravíme zpracování vstupu do SQLka

use Nette\Utils\Strings

function search($string)
{
    $string = Strings::lower(Strings::normalize($string);
    $string = Strings::replace($string, '/[^\d\w]/u', ' ');

    $words = Strings::split(Strings::trim($string), '/\s+/u');
    $words = array_unique(array_filter($words, function ($word) {
        return Strings::length($word) > 1;
    }));
    $words = array_map(function ($word) {
        return Strings::toAscii($word) . '*';
    }, $words);

    $string = implode(' ', $words);

    $sql = "...";

    return $this->db->query($sql, $string, $string, $string)->fetchAll();
}

Našli jsme čaj!

Už jenom otestovat

dump([
    $fulltext->search("Čaj"),
    $fulltext->search("Caj"),
    $fulltext->search("čaj"),
    $fulltext->search("caj"),
]); //  [['food_id' => 2]], [['food_id' => 2]], [['food_id' => 2]], [['food_id' => 2]]

a máme to hotovo. Doufám že tohle je naposledy co jsem musel řešit fulltext v MySQL a vám to přeji taky ;)

Have you found a typo? Fix me

Autor:

comments powered by Disqus