Metody porównywania znaków w MySQL

[sleo 0]

Witam,

Robię 2 strony na WP. Jedna będzie polskojęzyczna, a druga wielojęzyczna i mam problem z wyborem metody porównywania znaków w MySQL.

W panelu mam do wyboru m.in.:

utf8mb4_polish_ci
utf8mb4_unicode_ci
utf8mb4_general_ci
utf8mb4_bin

Czym należy się kierować przy wyborze metody porównywania znaków? Jakie są różnice między wymienionymi metodami porównywania znaków?

 

[maniack 403]

.

Edytowano Wrzesień 13, 2017 przez maniack (zobacz historię edycji)

[sleo 0]

@maniack dzięki za wyjaśnienie.

[Pan Kot 1535]

Jeśli nie potrzebujesz robienia MySQL case insensitive to najszybszym i jednocześnie najlepszym będzie utf8mb4_bin. Zawiera on wszystkie znaki jakie gdziekolwiek są używane, a jednocześnie porównuje stringi binarnie nie martwiąc się o aspekty kulturowe czy właśnie case.

 

Z kolei jeśli potrzebujesz to utf8mb4_unicode_ci - podobnie jak wyżej z dodatkowym overheadem w postaci kultury i case insensitive.

 

Cała reszta jest bardzo specyficzna i powinna być używana wyłącznie jeśli masz taką potrzebę. Przykładowo do zapisywania hashy w postaci cyfr i znaków A-F (np. FF0000) warto użyć latin1_bin - doprecyzowanie kodowania potrafi ładnie poprawić wydajność, w szczególności używanie typów binarnych zamiast _ci czy _cs.

Edytowano Maj 2, 2016 przez Archi (zobacz historię edycji)

[sleo 0]

@Archi dzięki za dodatkowe wyjaśnienia.

Strona WP wielojęzyczna ma być w następujących językach: polski, angielski, niemiecki (w przyszłości być może dojdą języki skandynawskie).

Będę wdzięczy za wyjaśnienie co znaczą terminy/frazy: case insensitive, overhead, aspekty kulturowe.

Zanim napisałeś zainstalowałem WP na utf8mb4_unicode_ci i znalazłem kwiatek. Po instalacji wtyczki Widgetkit2 została automatycznie stworzona tabela w utf8_general_ci.

Czy wtyczki mogą wymuszać metodę porównywania znaków?

Jeśli tak, to czy można przekonwertować daną tabelę z utf8_general_ci do utf8mb4_unicode_ci lub utf8mb4_bin ?

Rozumiem, że najlepszym/bezproblemowym wyborem będzie utf8mb4_bin?

Edytowano Maj 2, 2016 przez sleo (zobacz historię edycji)

[regdos 1848]

To są metody porównywania napisów więc mają skutek przy sortowaniu. Z tego co pamiętam przy utf8_general_ci po sortowaniu jeżeli jakiś wyraz zaczyna się na np. ą to poleci na koniec. Przy utf8_polish_ci będzie sortował dobrze.

Trzeba poeksperymentować.

 

Z poziomu PMA wchodzisz na tabelę, potem u góry wybierasz opcje i tam możesz ustawić wersję porównywania.

[Pan Kot 1535]

To są metody porównywania napisów więc mają skutek przy sortowaniu. Z tego co pamiętam przy utf8_general_ci po sortowaniu jeżeli jakiś wyraz zaczyna się na np. ą to poleci na koniec. Przy utf8_polish_ci będzie sortował dobrze.

Trzeba poeksperymentować.

 

Nie tylko, metoda porównywania napisów odgrywa największą rolę przy wszelkich warunkach where i podobnych. MySQL jest w stanie dużo szybciej porównywać stringi przez ich binarną reprezentację (_bin) niż przez porównywanie kulturowe czy case-insensitive. Ma to też dużą rolę przy kolizjach - przykładowo ja mam w bazie coś takiego jak ID, gdzie ID to 5 znaków 0-9 A-Z. ID "00AAF" to nie to samo co ID "00aAF", i jeśli użyłbym tutaj utf8mb4_unicode_ci to bym miał błąd w warstwie logiki bazy danych, i od cholery problemów po stronie kodu. M.in dlatego właśnie stwierdziłem, że porównywania _ci to samo zło i dopóki ktoś nie potrzebuje tego case-insensitivity (a w większości przypadków się tego nie stosuje), to powinien się upewnić, że używa wszędzie typów _bin.

 

I już nawet pomijam to, że porównywanie _bin jest o wiele szybsze. Jest też o wiele bezpieczniejsze kiedy operujemy na takich systemach jak Linux, gdzie filesystemy i generalnie cała otoczka jest case-sensitive. Już swoje godziny straciłem na debugowanie takiego problemu.

 

Jedyny przypadek gdy chcemy skorzystać z _ci lub _cs to właśnie taki gdy albo potrzebujemy tego case-insensitivity, np. upewniając się, że zapytanie WHERE nazwisko = "regDoS" zwróci ten sam wynik co "Regdos", lub w przypadku gdy niektóre znaki mają specjalne znaczenie w różnych kulturach (np. i w Tureckim). Generalnie więc, _bin all the way.

Edytowano Maj 2, 2016 przez Archi (zobacz historię edycji)

[maniack 403]

.

Edytowano Wrzesień 13, 2017 przez maniack (zobacz historię edycji)

[sleo 0]

Będę wdzięczy za wyjaśnienie co znaczą terminy/frazy: case insensitive, overhead, aspekty kulturowe.

Pytanie nieaktualne.

 

Wygląda na to, że WP nie obsługuje utf8mb4_bin. Tworzę bazę MySQL w utf8mb4_bin, a WP tworzy tabele utf8mb4_unicode_ci.

 

Dzięki Panowie za pomoc.

Edytowano Maj 3, 2016 przez sleo (zobacz historię edycji)

[Piotr GRD 608]

Bo z założenia Wordpress służy głównie do tekstu w najróżniejszych językach, wprawdzie obecnie można z nim zrobić wszystko dzięki wtyczkom, ale pierwotnie to platforma służąca do prowadzenia bloga. W zdecydowanej większości przypadków użytkownicy potrzebują więc braku wrażliwości na wielkość liter. Za wyjątkiem niewielu osób doskonale wiedzących co robią, pozostali mieliby problemy używając _bin i w rezultacie szukając "klucz" nie znaleźliby tekstów, gdzie występuje on na początku zdania w formie "Klucz", albo dla wyróżnienia napisany jest jako "KLUCZ".

 

Poza tym Wordpress dedykowany jest w dużej mierze laikom, nawet jak ktoś się nie zna ni w ząb na webmasterce, ma sobie z Wordpressem poradzić. Więc i wybrana jest taka metoda porównywania napisów, która zadziała dla każdego, aby żaden laik nie musiał zastanawiać się co też ustawić w konfiguracji.

 

Użytkownik zaawansowany zawsze może po instalacji tabele sobie pozmieniać, jeśli uzna, że tak będzie dla niego lepiej.