Коректне сортування українських літер

За замовчуванням MySQL сортує український алфавіт у такий спосіб: першими йдуть «Є, І, Ї», а потім «А, Б, В». Ми можемо виправити це використовуючі відповідні директиви при створенні таблиць: DEFAULT CHARSET і COLLATE.

Зараз MySQL переходить від старішої, недосконалої реалізації UTF-8. Наразі потрібно використовувати utf8mb4 замість utf8 для кодування символів, що і роблять останні версії. Версія з недоліками залишається для зворотної сумісності, хоча вона застаріла.

Ключові відмінності

• utf8mb4_unicode_ci - базується на офіційних правилах Unicode для універсального сортування та порівняння, які точно сортують у широкому діапазоні мов.

• utf8mb4_general_ci - (за замовчуванням) це спрощений набір правил сортування, який має на меті працювати якнайкраще, одночасно використовуючи багато скорочень, призначених для підвищення швидкості. Це не відповідає правилам Unicode і призведе до небажаного сортування або порівняння в деяких ситуаціях, наприклад, під час використання певних мов або символів.

На сучасних серверах це підвищення продуктивності буде майже незначним. Він був розроблений у той час, коли сервери мали незначну частку продуктивності ЦП, ніж сучасні комп’ютери.

Що потрібно використовувати?

Майже напевно немає причин для використання utf8mb4_general_ci, оскільки ми залишили позаду точку, коли швидкість ЦП є достатньо низькою, щоб різниця в продуктивності була важливою. Ваша база даних майже напевно буде обмежена іншими вузькими місцями, крім цього.

У минулому деякі люди рекомендували використовувати utf8mb4_general_ci за винятком випадків, коли точне сортування було достатньо важливим, щоб виправдати вартість продуктивності. Сьогодні ця вартість продуктивності майже зникла, і розробники більш серйозно ставляться до інтернаціоналізації.

Існує аргумент, що якщо швидкість для вас важливіша за точність, ви можете взагалі не виконувати сортування. Тривіально зробити алгоритм швидшим, якщо вам не потрібно, щоб він був точним. Отже, utf8mb4_general_ci це компроміс, який, ймовірно, не потрібен з причин швидкості та, ймовірно, також не підходить з причин точності.

Ще одна річ, яку я додам, полягає в тому, що навіть якщо ви знаєте, що ваша програма підтримує лише англійську мову, вона все одно може мати справу з іменами людей, які часто можуть містити символи, що використовуються в інших мовах, у яких так само важливо правильно сортувати . Використання правил Unicode для всього допомагає переконатися, що дуже розумні люди Unicode дуже багато працювали, щоб сортування працювало належним чином.

Що означають частини

По-перше, ci - для сортування та порівняння без урахування регістру . Це означає, що він підходить для текстових даних, і регістр не важливий. Інші типи порівняння cs (з урахуванням регістру) для текстових даних, де регістр важливий, і bin, коли кодування має збігатися, біт за біт, що підходить для полів, які дійсно є закодованими двійковими даними (включаючи, наприклад, Base64). Сортування з урахуванням регістру призводить до деяких дивних результатів, а порівняння з урахуванням регістру може призвести до повторюваних значень, що відрізняються лише регістром літер, тому порівняння з урахуванням регістру втрачає перевагу для текстових даних – якщо для вас важливий регістр, тоді пунктуацію можна не враховувати. і так далі, ймовірно, також є важливим, і двійкове зіставлення може бути більш доречним.

Далі unicode або general стосується конкретних правил сортування та порівняння, зокрема способу нормалізації чи порівняння тексту. Існує багато різних наборів правил для кодування символів utf8mb4, два з unicode яких general намагаються добре працювати на всіх можливих мовах, а не на одній конкретній. Відмінності між цими двома наборами правил є предметом цієї статті. Зауважте, що unicode використовує правила з Unicode 4.0. Останні версії MySQL і MariaDB додають набори правил unicode_520, використовуючи правила з Unicode 5.2, а MySQL 8.x додає 0900 (відкидаючи частину «unicode_»), використовуючи правила з Unicode 9.0.

І, нарешті, utf8mb4 кодування символів, яке використовується всередині. У цій відповіді я говорю лише про кодування на основі Unicode.

https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci

Тобто, щоб не мати проблем з сортуванням, таблиці слід створювати так:

CREATE TABLE `table1` (
...
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Якщо в таблиці нема інших даних ніж цифри та основні латинські літери, можна так:

CREATE TABLE `table2` (
...
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

це дасть приріст продуктивності, але дуже незначний.

В окремих випадках директиви можна призначати полям, нарпиклад:

CREATE TABLE test (name CHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci);

> select name from test;
+------------------+
| name             |
+------------------+
| Петро            |
| Василь           |
| Їбобо            |
| Микола           |
| Єва              |
| Сашко            |
| Івасик           |
| Наталка          |
| Андрійко         |
| Андрєйко         |
| Андрїйко         |
+------------------+

> select name from test order by name;
+------------------+
| name             |
+------------------+
| Андрєйко         |
| Андрійко         |
| Андрїйко         |
| Василь           |
| Єва              |
| Івасик           |
| Їбобо            |
| Микола           |
| Наталка          |
| Петро            |
| Сашко            |
+------------------+