Jak nejelegantněji v MySQL::NDB vyhledávat text bez ohledu na přítomnost diakritiky?

zacatecnik27
Člen | 26
+
-1
-

Mám jednoduchý požadavek, na web přistupuje významná skupina lidí, kteří diakritiku nepoužívají a často zadají do vyhledávače text bez ní, omyl si samozřejmě uvědomí a druhý dotaz už diakritiku obsahuje ale chci jím vyjít vstříc a hledat jednoduše bez ohledu na diakritiku pokud zadá text peskova měla by databáze vrátit vše co odpovídá regulárnímu výrazu p[eéě][sš]k[oó]v[aá] … pokud zadá znak s diakritikou tak už pochopitelně budu předpokládat že chce přesně daný řetězec a tomuto usnadnění se vyhnu zcela.

Jak na to, jak to řešíte vy ?

Prosím vyhněme se zcela tomuto řešení SELECT * FROM ulice WHERE REGEXP 'p[eéě][sš]k[oó]v[aá]' vím, že se přímo nabízí a je to to první co nás napadne, ale zajímá mne zda neexistuje elegantnější (no dobře, přiznám se že pravý důvod je ten že první řešení mi prostě nefunguje kvůli problému, který jsem popsal zde, a který se mi nedaří rozlousknout), hledám tedy jiný ideálně i efektivnější a třeba rychlejší způsob řešení problému. Nemusíte uvádět konkrétní kód stačí základní popis myšlenky :-) Detaily už dohledám. Děkuji.


(doplnění)
optimálního řešení, vyplývajícího z diskuse níže:

stačí jen prohledávaný sloupec mít typu „utf8_general_ci“ nikoliv „utf8_czech_ci“. Pak najednou můžete prohledávat s/bez diakritiky pomocí základních konstrukcí (ať už fulltext nebo like) a SQL vrátí řádky nejen ignorujíc velikost znaků ale dokonce i bez ohledu na diakritiku. Tohle v případě že použijete sloupec typu utf8_czech_ci překvapivě NEFUNGUJE, tenhle špek opravdu není na první pohled zjevný, takže pozor na to a srdci bližší utf_czech nebrat :-) Všem díky za čas a nový poznatek.

Optimálně tedy podminka nad sloupcem v kodovani utf8_general_ci vypada takto: SELECT *** ** ** WHERE MATCH (nazev) AGAINST ('pes*' IN BOOLEAN MODE) a využijeme rychlost fulltextového vyhledávání. Tímto způsobem najdeme počátky všech slov v textu tedy potenciálně i Náměstí Pěstitelů nikoliv však Harapesova :-) což je fajn a přesně to odpovídá očekávanému chování …


(doplnění)
Optimální řešení má ale přeci jen jeden dva háčky.

  1. řazení sloupce bude dávat trochu jiné výsledky než v případě utf8_czech_ci. Například slova začínající š a s (platí obecně pro jakékoliv kombinace s/bez diakritiky) budou pomíchána jako by začínala naprosto stejným znakem a o jejich pořadí rozhodnou až písmena následující … nic není dokonalé. Pokud vám toto vadí, pak raději zvolte jiné řešení problému, nebo holt přidejte další sloupec pro název, jeden v utf8_czech_ci, druhý v utf8_general_ci nebo jeden s a druhý bez diakritiky …
  2. pokud tazatel zadá do vyhledávače řetězec s diakritikou „peš“ je už naprosto zjevné že nechce vrátit ulici „U Pěstírny“, to dá rozum. Můžete využít toho že REGEXP se chová jinak a vždy přesně ctí hledané znaky i v případě utf8_general_ci, pokud tedy chcete najít přesný výraz s diakritikou holt asi nezbude než vsadit u takového sloupce na regexp nebo mít zvláštní duplicitní sloupec v utf8_czech_ci …

Editoval zacatecnik27 (21. 9. 2017 11:27)

Martk
Člen | 651
+
+1
-

Můžeš použít FULLTEXT, ale musíš použít pro daný sloupec utf8_general_ci. Tady je celkem pěkný přehled.

Další možností je vytvořit vyhledávací sloupec a tam odstranit diakritiku, převést vše na malé písmena, … Raději bych využil ten fulltext.

REGEXP jsem v mysql ještě nepoužil, s tím ti neporadím.

Pavel Janda
Člen | 977
+
+2
-

@zacatecnik27 http://lmgtfy.com/?…

Honza.Mottl
Člen | 104
+
-3
-

ja pouzivam hledani bez diakritiky pouze v ciselnicich (obce CR apod.). V tabulce si ukladam navic retezec bez diakritiky a ten uzivam pro vyhledavani.

v sql skriptu odstranim diakritiku:

UPDATE acompl_obec
SET obec_bez_diakritiky = remove_diacritics_lower(obec);

CREATE DEFINER=CURRENT_USER FUNCTION `remove_diacritics_lower` (`source` VARCHAR(255) CHARSET utf8 COLLATE utf8_czech_ci) RETURNS VARCHAR(255) CHARSET latin1 BEGIN
    DECLARE destination VARCHAR(255) CHARSET utf8 COLLATE utf8_czech_ci DEFAULT source;

    SET destination = REPLACE(destination, 'á', 'a');
    SET destination = REPLACE(destination, 'ä', 'a');
    SET destination = REPLACE(destination, 'č', 'c');
    SET destination = REPLACE(destination, 'ď', 'd');
    SET destination = REPLACE(destination, 'é', 'e');
    SET destination = REPLACE(destination, 'ě', 'e');
    SET destination = REPLACE(destination, 'ë', 'e');
    SET destination = REPLACE(destination, 'í', 'i');
    SET destination = REPLACE(destination, 'ň', 'n');
    SET destination = REPLACE(destination, 'ó', 'o');
    SET destination = REPLACE(destination, 'ö', 'o');
    SET destination = REPLACE(destination, 'ř', 'r');
    SET destination = REPLACE(destination, 'š', 's');
    SET destination = REPLACE(destination, 'ť', 't');
    SET destination = REPLACE(destination, 'ú', 'u');
    SET destination = REPLACE(destination, 'ů', 'u');
    SET destination = REPLACE(destination, 'ü', 'u');
    SET destination = REPLACE(destination, 'ý', 'y');
    SET destination = REPLACE(destination, 'ž', 'z');
    SET destination = REPLACE(destination, 'Á', 'A');
    SET destination = REPLACE(destination, 'Ä', 'A');
    SET destination = REPLACE(destination, 'Č', 'C');
    SET destination = REPLACE(destination, 'Ď', 'D');
    SET destination = REPLACE(destination, 'É', 'E');
    SET destination = REPLACE(destination, 'Ě', 'E');
    SET destination = REPLACE(destination, 'Ë', 'E');
    SET destination = REPLACE(destination, 'Í', 'I');
    SET destination = REPLACE(destination, 'Ň', 'N');
    SET destination = REPLACE(destination, 'Ó', 'O');
    SET destination = REPLACE(destination, 'Ö', 'O');
    SET destination = REPLACE(destination, 'Ř', 'R');
    SET destination = REPLACE(destination, 'Š', 'S');
    SET destination = REPLACE(destination, 'Ť', 'T');
    SET destination = REPLACE(destination, 'Ú', 'U');
    SET destination = REPLACE(destination, 'Ů', 'U');
    SET destination = REPLACE(destination, 'Ü', 'U');
    SET destination = REPLACE(destination, 'Ý', 'Y');
    SET destination = REPLACE(destination, 'Ž', 'Z');

    SET destination =  LOWER(destination);
    RETURN destination;
END$$

DELIMITER ;

V php na to mam funkci:

// Prevodni tabulka na likvidaci diakritiky
const PREVODNI_TABULKA = Array(
    'ä'=>'a',
    'Ä'=>'A',
    'á'=>'a',
    'Á'=>'A',
    'à'=>'a',
    'À'=>'A',
    'ã'=>'a',
    'Ã'=>'A',
    'â'=>'a',
    'Â'=>'A',
    'č'=>'c',
    'Č'=>'C',
    'ć'=>'c',
    'Ć'=>'C',
    'ď'=>'d',
    'Ď'=>'D',
    'ě'=>'e',
    'Ě'=>'E',
    'é'=>'e',
    'É'=>'E',
    'ë'=>'e',
    'Ë'=>'E',
    'è'=>'e',
    'È'=>'E',
    'ê'=>'e',
    'Ê'=>'E',
    'í'=>'i',
    'Í'=>'I',
    'ï'=>'i',
    'Ï'=>'I',
    'ì'=>'i',
    'Ì'=>'I',
    'î'=>'i',
    'Î'=>'I',
    'ľ'=>'l',
    'Ľ'=>'L',
    'ĺ'=>'l',
    'Ĺ'=>'L',
    'ń'=>'n',
    'Ń'=>'N',
    'ň'=>'n',
    'Ň'=>'N',
    'ñ'=>'n',
    'Ñ'=>'N',
    'ó'=>'o',
    'Ó'=>'O',
    'ö'=>'o',
    'Ö'=>'O',
    'ô'=>'o',
    'Ô'=>'O',
    'ò'=>'o',
    'Ò'=>'O',
    'õ'=>'o',
    'Õ'=>'O',
    'ő'=>'o',
    'Ő'=>'O',
    'ř'=>'r',
    'Ř'=>'R',
    'ŕ'=>'r',
    'Ŕ'=>'R',
    'š'=>'s',
    'Š'=>'S',
    'ś'=>'s',
    'Ś'=>'S',
    'ť'=>'t',
    'Ť'=>'T',
    'ú'=>'u',
    'Ú'=>'U',
    'ů'=>'u',
    'Ů'=>'U',
    'ü'=>'u',
    'Ü'=>'U',
    'ù'=>'u',
    'Ù'=>'U',
    'ũ'=>'u',
    'Ũ'=>'U',
    'û'=>'u',
    'Û'=>'U',
    'ý'=>'y',
    'Ý'=>'Y',
    'ž'=>'z',
    'Ž'=>'Z',
    'ź'=>'z',
    'Ź'=>'Z'
);

/**
 * Vrátí řetězec bez diakritiky jako malá písmena
 * @return řetězec bez diakritiky jako malá písmena
 */
private static function removeDiacriticsLwr ($inString)
{
    return strtolower(strtr($inString, self::PREVODNI_TABULKA));
}
Pavel Kravčík
Člen | 1180
+
+2
-

@Honza.Mottl: Pěkný pole. :)) Tohle by Ti nepomohlo? https://doc.nette.org/…tils/strings#…

Honza.Mottl
Člen | 104
+
0
-

jo, pomohlo :) Ale kdyz jsem to loni psal, tak jsem o tom nevedel. Rekl bych, ze to asi jeste nebylo v dokumentaci. Nebo jsem si toho nevsiml.

V tabulce mam indexovane sloupce bez diakritiky. Nevim jestli by se pouzily indexy v linku od Pavla Jandy.

zacatecnik27
Člen | 26
+
0
-

Díky za odpovědi, řešení tedy mám (doplnil jsem jej pod čarou úvodní otázky pro přehlednost).

Přeci jen mi ale v hlavě zůstává poslední otázečka k tématu. Proč vlastně vyhledávání nad sloupcem s kolekcí utf8_general_ci umí diakritiku i velikost znaků ignorovat, zatímco utf8_czech_ci nikoli, má to nějaký logický důvod ?

Editoval zacatecnik27 (21. 9. 2017 12:12)

Martk
Člen | 651
+
+6
-

@zacatecnik27 Tady je dobře graficky zpracovaná kolekce: utf8_czech_ci (tato kolekce vnímá s a š jako rozdílné znaky) a utf8_general_ci (tato kolekce vnímá s a š jako stejné znaky)

Pavel Kravčík
Člen | 1180
+
0
-

@Martk: To je bomba přehled. Díky!

Honza.Mottl
Člen | 104
+
+2
-

MySQL diacritic insensitive search setri misto za cenu nevyziti databazovych indexu. Na vetsich datech a pri pozadavcich na odezvu bych si na to dal pozor.

Šaman
Člen | 2632
+
+2
-

Kódováni sloupce neměň! Pak je problém s řazením. Použití utf8_czech_ci je správná volba.
Použij jen v dotazu změnu kolekce pro porovnání – MySQL COLLATE
(Tahle ukázka je pro Dibi Fluent):

<?php
->where("[firstname] LIKE %~like~ COLLATE [utf8_general_ci]", $value);
?>

Vyhledávání pak ignoruje diakritiku, ale ve výpisu je Š správně řazeno za S.


Dodatek: Tohle zároveň řeší problém č. 1 (řazení vs. vyhledávání)
Na problém č. 2 můžeš jít tak, že pokud ve vyhledávacím políčku detekuješ diakritiku, necháš to hledat v rámci utf8_czech_ci. (Takže peš ti nenajde pěstitelů.) Také můžeš přidat checkbox, kterým určiš, zda diakritiku ignorovat, či ne. Tohle už není věc databáze, ale aplikační logiky. (Bez kříšťálové koule nejde obecně rozhodnout, jestli na pes hledat jen psy, nebo i Pešky.)

Editoval Šaman (21. 9. 2017 14:26)