Wyszukiwanie wyrazów napisanych cyrilicą lub np. w jęz. arabskim oraz polskich słów pisanych bez użycia znaków diakrytycznych

Witam.
Tworzę w PHP projekt strony z ogłoszeniami akwarystycznymi - www.akwa-market.pl
Chciałem aby strona miała charakter międzynarodowy.
Mam utworząną bazę danych i tabele, jedna z nich jest z wpisami ogłoszeń. Mam też wbudowaną wyszukiwarkę słów.
Przyjmijmy, że wpisałem słowo - żółw
Wywołuję polecenie:

SELECT FROM * table_name WHERE item REGEXP '[[:<:]]{$keyword}[[:>:]]';

lub:

SELECT FROM * table_name WHERE item REGEXP '{$keyword}';

i znajduję rekord w bazie, który zawiera to słowo.
Problem pojawia się gdy wpiszę słowo po rosyjsku, np. Привет
Wyszukiwarka nie pokazuje rekordu z zadanym rosyjskim słowem.

Po połączeniu do bazy wywołuję.

mysqli_set_charset($db, "utf8mb4");

mysqli_query( $db, "SET NAMES utf8mb4");
mysqli_query( $db, "SET CHARACTER SET utf8mb4");

Próbowałem metodę porównywania napisów ustawić na utf8mb4_bin lub na utf8mb4_unicode_ci
ale to nie daje pomyślnych rezultatów. Wciąż daje się wyszukać jedynie słowa zawierające znaki alfabetu łacińskiego w tym znaki diakrytyczne języków europejskich.
Nie wiem, jak ustawić zapytanie aby móc wyszukiwać słowa w innych językach np. właśnie rosyjskim, chińskim, czy arabskim.

Chodzi mi o zastosowanie tego razem z REGEXP najlepiej, aby wyszukiwać kilka wyrazów jednocześnie rozdzielonych znakiem I czyli OR, gdzie np zmienna $keyword przyjmuje wartość:

$keyword = 'Привет | Андрей';

P.S. Dodatkowo nie wiem jak rozwiązać sprawę gdy ktoś wpisze - zółw przez z bez kropki, aby dało się wyszukać słowo żółw. Lub nawet napisane całkiem bez wciśnięcia ALT np. zolw

Pomoże ktoś? Strona już prawie funkcjonuje normalnie, ale właśnie wyszukiwarka kuleje i nie wiem za bardzo jak to rozwiązać.

OK. Jeden problem rozwiązałem. Chodziło o ustawienie metody porównywania COLLATE dla columny tabeli typu TEXT, której wiersze przeszukiwałem. Okazało się, że nie zauważyłem, iż kolumna ma ustawioną utf8mb4_bin a nie utf8mb4_unicode_ci lub choćby uff8_unicode_ci.
Teraz można wyszykiwać znaki w każdym języku w tym rosyjskim, chińskim, arabskim.

Tabela miała taką postać:

CREATE TABLE `items` (
  `index` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(11) COLLATE utf8_unicode_ci NOT NULL,
  `user_name` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_id` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `main_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `sub_category` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `country` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `voivodeship` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `town` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `latitude` decimal(11,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `entry_start` timestamp NULL DEFAULT NULL,
  `entry_expire` timestamp NULL DEFAULT NULL,
  `adv_type` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `adv_status` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `adv_views` decimal(9,0) DEFAULT 0,
  `users_report` text COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`index`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Zmieniłem columnę description na:

`description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL 

i wyszukiwarka działa. Dla każdego języka.

Teraz pozostaje druga sprawa do ogarnięcia.
Jak zrobić aby polskie słowo żółw lub rumuńskie gîgă lub hiszpańskie lápiz były odnajdywane nawet bez wpisania ich w pełni poprawnie, czyli bez znaków diakrytycznych danego języka.

Na przykład.

$keyword = 'zołw | lapiz';  //czyli słowo żółw pisane bez kropki nad z i bez kreseczki nad o oraz słowo lápiz pisane bez kreseczki nad a
SELECT FROM * items WHERE description REGEXP '{$keyword}';

Jak to ogarnąć? Czytałem o jakimś wyrażeniu regularnym preg_match albo preg_replace chyba z takim jakimiś ciągami znaków jak:

'/^[\s\p{L}]+$/u' /// oraz 
[oOóÓ][sSśŚ][cCćĆ]

Ale kompletnie nie wiem jak się za to zabrać i co podstawić do zmiennej $keyword dla REGEXP w zapytaniu MySQL.
Każda podpowiedź mile widziana.

Tego typu wyszukiwarki najczęściej opiera się o inny rodzaj bazy danych, np. Elasticsearch (w sensie, że trzymasz kanoniczny stan systemu w bazie relacyjnej, ale dane opisów ekstra synchronizujesz do Elastica i o niego opierasz samą wyszukiwarkę).

Ja bym nie szedł w regexy po stronie bazy. Skoro masz backend w PHP możesz użyć funkcji iconv do znalezienia reprezentacji słowa wpisanego przez użytkownika w ASCII i zrobić wyszukiwanie z like gdzie podasz obie wersje słowa. Pamiętaj tylko, że like nie korzysta z indeksu. Tylko co jeśli ktoś zapisze w bazie żólw?
Jeśli chcesz szukać wielu słów na raz to jest coś takiego jak Full Test Search. Zobacz czy silnik bazy jaki używasz wspiera takie zapytania https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

jurek1980 napisał(a):

Ja bym nie szedł w regexy po stronie bazy. Skoro masz backend w PHP możesz użyć funkcji iconv do znalezienia reprezentacji słowa wpisanego przez użytkownika w ASCII i zrobić wyszukiwanie z like gdzie podasz obie wersje słowa. Pamiętaj tylko, że like nie korzysta z indeksu. Tylko co jeśli ktoś zapisze w bazie żólw?
Jeśli chcesz szukać wielu słów na raz to jest coś takiego jak Full Test Search. Zobacz czy silnik bazy jaki używasz wspiera takie zapytania https://www.w3resource.com/mysql/mysql-full-text-search-functions.php

Ciekawy temat podniosłeś.
Kiedyś rozważałem zagadnienie "bliskości polskoleksykograficznej", tj podobieństwa żółwia, zólwia, żolwia i zolwia.

Nie widzę w polskim internecie jakiś konsekwentnych rozwiązań, słowniki nie pracują z tym konsekwentnie, raczej wyjątkowo (mniemam, że przez ręczne dodanie zwiazku w niektórych przypadkach, a nie zasadę generalną)

Jedno z tych rozważań, że coś tam owszem się myślało, kolekcjonowało spostrzeżenia, ale wyników brak

@ZrobieDobrze: No właśnie to już wtedy pewnie Elastic. Jest plugin do obsługi polskiego https://czterytygodnie.pl/elasticsearch/obsluga-jezykow.html
Nie wiem nawet czy nie użyty w forum. Tylko pytanie czy OP chce już czegoś takiego, czy może wystarczy jakaś prosta konwersja.

może coś takiego
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

Dziękuję za wszystkie podpowiedzi.
Zmieniłęm zapytanie REGEXP na MATCH AGAINST w BOOLEAN MODE:

SELECT FROM * items WHERE description REGEXP '{$keyword}';

Na coś takiego:

"SELECT * FROM item WHERE lower (adv_status) REGEXP '{$advert_status}' AND ( (MATCH(name) AGAINST('$keywords' IN BOOLEAN MODE) ) OR (MATCH(description) AGAINST('$keywords' IN BOOLEAN MODE)) OR name LIKE '$keywords%')";

Musiałem dodać:

OR name LIKE '$keywords%'

bo samo MATCH AGAINST nie wyszukiwało nic gdy zmienna $keywords nie przyjmowała żadnej wartości lub była po prostu pusta $keywords = "";

Teraz zmienna $keywords zawiera wyrazy rozdzielone spacją i poprzedzone znakiem +.
Np.

$keywords ="+Terrarium +wąż";

Wyszukiwarka działa znacznie lepiej, jednak działają tylko słowa 4 literowe i dłuższe.
Nie wyszukuje np słowa: "wąż", kiedy jest wpisane samodzielnie.
Ale wyszuka już 4 literowe słowo "żółw", kiedy jest napisane bez błędu, tak jak w tytule lub opisie ogłoszenia.
Jak zrobić aby wyszukiwało 3 literowe słowo "wąż", lub 4 literowe słowo "żółw", lub hiszpańskie słowo "lápiz" kiedy są one wpisane z błędami, bez znaków diakrytycznych?

Myślę, o zastosowaniu preg_replace() lub preg_match() lub ewentualnie iconv() jako podpowiada @jurek1980 , aby ułożyć dłuższy ciąg wyrazów w zapytaniu dla trybu BOOLEAN. Być może wtedy trzeba będzie ułożyć znacznie dłuższe zapytanie MySql, z włączaniem i wyłączaniem poprzez znak +/- konkretnych wersji danego wyrazu, ale to jest do zrobienia.
Chodzi teraz o to jak z wyrazu "Wąż", zrobić prosto jeszcze "wąz", "waz", i "waż" lub dla dłuższych wyrazów typu "żółw" zrobić więcej kombinacji. I żeby to działało też np. w niemieckim i hiszpańskim, czyli wszędzie tam gdzie mamy alfabet łaciński wzbogacony o znaki diakrytyczne.
Dzięki. To na razie tyle. Każda podpowiedź mile widziana.

A założyłeś indeks full test search na właściwej kolumnie/kolumnach? Może być konieczność zmiany https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size Proponuję ustawić dwa znaki, default jest 3. Ale sprawdź jeszcze dla swojego silnika.