utf8 vs utf8mb4 у MySQL: Повний технічний посібник

Набір символів utf8 у MySQL є неправильною назвою — це не справжня реалізація UTF-8. Він кодує символи, використовуючи лише від 1 до 3 байт, що означає, що він мовчки відкидає або відхиляє будь-яку кодову точку Unicode вище U+FFFF, включаючи всі emoji та значну частину додаткових символів CJK. utf8mb4 — це правильна, повна реалізація UTF-8 у MySQL, що підтримує від 1 до 4 байт на символ і повний діапазон Unicode. Для будь-якої виробничої бази даних, створеної після 2010 року, utf8mb4 є єдиним виправданим вибором.

Цей посібник пояснює, чому ця відмінність має значення, де оригінальний дизайн utf8 пішов не так, як безпечно виконати міграцію та як правильно налаштувати MySQL на рівні сервера, бази даних, таблиці та з’єднання.

Основна проблема: чому utf8 у MySQL зламаний за своєю конструкцією

Стандарт кодування UTF-8 (RFC 3629) визначає схему змінної ширини, яка використовує від 1 до 4 байт для представлення кожної дійсної кодової точки Unicode — понад 1,1 мільйона можливих символів. Коли MySQL представив набір символів `utf8` у версії 4.1, реалізація була навмисно обмежена 3 байтами на символ. Це було навмисним інженерним скороченням, а не недоглядом.

На той час формат рядків InnoDB накладав обмеження у 767 байт на префікси ключів індексу. Підтримка 4-байтових символів скоротила б максимальну довжину індексованого префікса для стовпців `VARCHAR`, створюючи проблеми сумісності індексів. Обмеження у 3 байти було прагматичним обхідним рішенням, яке стало довгостроковою проблемою.

Практичний наслідок: будь-яка кодова точка Unicode у Додатковій багатомовній площині (SMP) — кодові точки U+10000 і вище — не може бути збережена у стовпці `utf8`. Це включає:

• Усі стандартні emoji (U+1F600 і далі)
• Математичні буквено-цифрові символи (U+1D400–U+1D7FF)
• Символи музичної нотації
• Історичні писемності, такі як Лінійне письмо Б, готське та клинопис
• Додаткові уніфіковані ідеографи CJK (U+20000–U+2A6DF)
• Певні символи валют та технічні оператори, додані в останніх версіях Unicode

Коли програма намагається вставити 4-байтовий символ у стовпець `utf8`, MySQL або повертає помилку `Incorrect string value`, або, якщо `sql_mode` є дозвільним, мовчки обрізає дані. Мовчазне обрізання є, мабуть, більш небезпечним результатом — ваша програма не отримує жодної помилки, але ваші дані пошкоджені.

utf8mb4: правильна реалізація

MySQL представив utf8mb4 у версії 5.5.3 (випущеній у 2010 році) спеціально для усунення цього недоліку. Суфікс `mb4` означає «багатобайтовий, максимум 4 байти». Це строге надмножина `utf8` — кожен символ, який можна представити в `utf8`, ідентично представляється в `utf8mb4`. При міграції з `utf8` на `utf8mb4` немає втрати даних.

utf8mb4 безпосередньо відповідає стандарту UTF-8 RFC 3629. Він обробляє повний простір кодових точок Unicode від U+0000 до U+10FFFF без обмежень.

utf8 проти utf8mb4: порівняння функцій

Вибір зіставлення в межах utf8mb4

Вибір utf8mb4 як набору символів — це лише половина рішення. Зіставлення визначає, як рядки порівнюються, сортуються та індексуються. Неправильне зіставлення спричиняє тонку, важку для налагодження поведінку запитів.

utf8mb4_unicode_ci

Базується на алгоритмі зіставлення Unicode (UCA). Правильно обробляє мовно-специфічні правила сортування. Дещо повільніше, ніж `utf8mb4_general_ci`, через складнішу логіку порівняння, але різниця в продуктивності є незначною на сучасному обладнанні.

utf8mb4_general_ci

Спрощене зіставлення, яке не повністю реалізує UCA. Швидше в тестах початку 2010-х років, але перевага у швидкості є неактуальною на сучасних CPU. Воно неправильно обробляє деякі граничні випадки — наприклад, вважає певні німецькі символи еквівалентними, хоча вони такими не є. Уникайте для нових проєктів.

utf8mb4_0900_ai_ci

Доступне в MySQL 8.0+. Базується на Unicode 9.0 з нечутливим до наголосів (`ai`) та нечутливим до регістру (`ci`) порівнянням. Це рекомендоване значення за замовчуванням для MySQL 8.0 і пізніших версій. Воно швидше, ніж `utf8mb4_unicode_ci`, і точніше.

utf8mb4_bin

Двійкове порівняння — чутливе до регістру, чутливе до наголосів, без локально-специфічних правил. Використовуйте, коли потрібне точне побайтове співставлення, наприклад, для хешів паролів або чутливих до регістру ідентифікаторів.

Рекомендація: Використовуйте `utf8mb4_0900_ai_ci` на MySQL 8.0+. Використовуйте `utf8mb4_unicode_ci` на MySQL 5.7 і раніших версіях.

Наслідки для зберігання та індексів

Поширеною проблемою при міграції з utf8 на utf8mb4 є накладні витрати зберігання. На практиці вплив є мінімальним:

• Символи ASCII (U+0000–U+007F) займають рівно 1 байт в обох кодуваннях.
• Більшість латинських, грецьких, кириличних, арабських та єврейських символів займають 2 байти в обох кодуваннях.
• Символи CJK у BMP займають 3 байти в обох кодуваннях.
• Лише додаткові символи (emoji, додаткові символи CJK) потребують 4 байти — і вони просто не могли бути представлені в utf8 раніше.

Реальна проблема з індексами — це обмеження префікса індексу InnoDB у 767 байт у старих конфігураціях. З utf8mb4, у найгіршому випадку 4 байти на символ означають, що префікс індексу `VARCHAR` з 191 символом досягає стелі у 767 байт. З `utf8` та тим самим обмеженням дозволялося 255 символів. Якщо у вас є стовпці `VARCHAR(255)` з індексами на весь стовпець, під час міграції можуть виникнути помилки `Specified key was too long`.

Рішення:

• Увімкніть `innodb_large_prefix = ON` (MySQL 5.6/5.7), щоб підвищити обмеження до 3072 байт.
• Використовуйте `ROW_FORMAT=DYNAMIC` або `ROW_FORMAT=COMPRESSED` для відповідних таблиць.
• У MySQL 8.0 `innodb_large_prefix` увімкнено за замовчуванням, і параметр видалено.
• Скоротіть префікси індексів: `INDEX (column(191))` замість `INDEX (column(255))`.

Це найпоширеніша точка відмови міграції та та, яка найчастіше недостатньо задокументована в базових посібниках.

Як перенести базу даних MySQL з utf8 на utf8mb4

Міграція є простою, але вимагає точності. Пропуск будь-якого рівня — сервера, бази даних, таблиці або з’єднання — залишає вашу програму мовчки повертатися до старого кодування.

Крок 1: Резервне копіювання бази даних

Ніколи не змінюйте кодування символів у живій базі даних без перевіреної резервної копії.

“`bash

mysqldump -u username -p –single-transaction –routines –triggers

database_name > database_backup_$(date +%F).sql

“`

Прапорець `–single-transaction` забезпечує узгоджений знімок для таблиць InnoDB без блокування. Збережіть резервну копію в місці, окремому від сервера бази даних, перш ніж продовжувати.

Крок 2: Оновлення конфігурації сервера MySQL

Відредагуйте `/etc/mysql/my.cnf` або `/etc/mysql/mysql.conf.d/mysqld.cnf` залежно від вашого дистрибутива:

“`ini

[client]

default-character-set = utf8mb4

[mysql]

default-character-set = utf8mb4

[mysqld]

character-set-server = utf8mb4

collation-server = utf8mb4_unicode_ci

For MySQL 5.6/5.7 only — remove on MySQL 8.0

innodb_large_prefix = ON

innodb_file_format = Barracuda

innodb_file_per_table = ON

“`

Перезапустіть MySQL:

“`bash

sudo systemctl restart mysql

“`

Крок 3: Конвертація бази даних

“`sql

ALTER DATABASE database_name

CHARACTER SET = utf8mb4

COLLATE = utf8mb4_unicode_ci;

“`

Крок 4: Конвертація всіх таблиць

Згенеруйте та виконайте оператори `ALTER TABLE` для кожної таблиці. Виконання їх вручну у великих схемах є схильним до помилок. Використовуйте цей запит для автоматичного генерування операторів:

“`sql

SELECT CONCAT(

'ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'

)

FROM information_schema.TABLES

WHERE TABLE_SCHEMA = 'database_name'

AND TABLE_TYPE = 'BASE TABLE';

“`

Виконайте кожен згенерований оператор. Синтаксис `CONVERT TO CHARACTER SET` змінює як стандартне значення таблиці, так і всі існуючі символьні стовпці в одній операції.

Крок 5: Виправлення помилок довжини індексу

Якщо ви стикаєтеся з `Specified key was too long; max key length is 767 bytes`, визначте проблемний індекс:

“`sql

— Change full-column index to prefix index

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name ADD INDEX index_name (column_name(191));

“`

Для баз даних WordPress зокрема, стовпець `option_name` таблиці `wp_options` та стовпець `meta_key` таблиці `wp_postmeta` є поширеними джерелами цієї помилки.

Крок 6: Перевірка конвертації

“`sql

— Check server-level variables

SHOW VARIABLES LIKE 'character_set%';

SHOW VARIABLES LIKE 'collation%';

— Check a specific table

SHOW CREATE TABLE table_nameG

— Check all columns in a database

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME

FROM information_schema.COLUMNS

WHERE TABLE_SCHEMA = 'database_name'

AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext');

“`

Кожне значення `CHARACTER_SET_NAME` має відображати `utf8mb4`.

Крок 7: Оновлення рядків підключення програми

Кодування сервера та схеми не має значення, якщо ваша програма підключається з використанням неправильного набору символів. Кодування на рівні з’єднання перевизначає стандартне значення сервера.

PHP (PDO):

“`php

$dsn = 'mysql:host=localhost;dbname=database_name;charset=utf8mb4';

$pdo = new PDO($dsn, $user, $pass, [

PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"

]);

“`

PHP (MySQLi):

“`php

$mysqli = new mysqli('localhost', $user, $pass, $db);

$mysqli->set_charset('utf8mb4');

“`

Python (mysql-connector-python):

“`python

cnx = mysql.connector.connect(

host='localhost', user=user, password=pass,

database=db, charset='utf8mb4', collation='utf8mb4_unicode_ci'

)

“`

Node.js (mysql2):

“`javascript

const pool = mysql2.createPool({

host: 'localhost', user: user, password: pass,

database: db, charset: 'utf8mb4'

});

“`

Невстановлення набору символів з’єднання є найпоширенішою причиною того, чому emoji все ще не вдається вставити після нібито завершеної міграції.

Особливості WordPress

WordPress постачається з utf8mb4 як стандартним набором символів починаючи з версії 4.2 (квітень 2015 року). Якщо ви запускаєте інсталяцію WordPress на старішій базі даних, яка ніколи не мігрувала, файл `wp-config.php` може все ще містити:

“`php

define('DB_CHARSET', 'utf8');

“`

Змініть це на:

“`php

define('DB_CHARSET', 'utf8mb4');

define('DB_COLLATE', 'utf8mb4_unicode_ci');

“`

WordPress також включає вбудовану процедуру оновлення (`maybe_convert_table_to_utf8mb4()`), яка запускається під час оновлень ядра. Однак ця процедура не завжди охоплює кожну таблицю, особливо ті, що створені плагінами. Ручний підхід `ALTER TABLE`, описаний вище, є більш надійним.

У середовищі VPS Хостингу з root-доступом ви можете автоматизувати весь цей процес за допомогою shell-скрипту та запланувати його як одноразове завдання cron, що дає вам повний контроль над часом виконання та журналюванням.

Міркування щодо продуктивності

Вплив на продуктивність utf8mb4 порівняно з utf8 є незначним для переважної більшості робочих навантажень:

• Запити на читання: Немає вимірюваної різниці для символів BMP. Додаткові символи потребують одного додаткового байта введення-виведення, який поглинається кешуванням пулу буферів.
• Запити на запис: Ідентичні для вмісту ASCII та BMP. Незначно вищі для додаткових символів.
• Операції з індексами: Зменшена максимальна довжина префікса (191 проти 255 символів для індексів на весь стовпець) може впливати на плани запитів, якщо у вас є індекси на весь стовпець для довгих стовпців `VARCHAR`. Перевіряйте свої індекси до та після міграції.
• Пам’ять: MySQL виділяє буфери фіксованої ширини для рядкових операцій на основі максимальної кількості байт на символ. Перехід з utf8 (максимум 3 байти) на utf8mb4 (максимум 4 байти) збільшує пам’ять, виділену для буферів сортування в пам’яті та тимчасових таблиць, приблизно на 33% для операцій з великою кількістю рядків. На Виділеному сервері з достатнім обсягом RAM це несуттєво. У середовищі з обмеженою пам’яттю відстежуйте `sort_buffer_size` та `tmp_table_size` після міграції.

Коли utf8 все ще є прийнятним

Існує вузький набір законних причин для збереження `utf8`:

• Сувора застаріла сумісність: Програма, що використовує непідтримуваний ORM або драйвер бази даних, який не може обробляти 4-байтові символи. Це проблема технічного боргу, а не причина зберігати utf8 безстроково.
• Архівні бази даних лише для читання: Якщо база даних ніколи не отримуватиме нових записів і наявні дані не містять додаткових символів, міграція додає ризик без будь-якої користі.
• Жорсткі обмеження зберігання: У крайніх граничних випадках — вбудовані системи або середовища з жорстким обмеженням ємності — незначна різниця у зберіганні може мати значення. Це не стосується жодного стандартного сценарію веб-хостингу.

У всіх інших випадках utf8mb4 є правильним вибором. Аргумент про те, що utf8 економить місце для зберігання, технічно вірний лише для додаткових символів, які все одно не могли бути представлені в utf8. Ви не економите місце на даних, які не могли зберігати.

Вибір правильного хостингового середовища для MySQL utf8mb4

Правильне налаштування utf8mb4 вимагає доступу до файлу конфігурації сервера MySQL (`my.cnf`). Це виключає більшість середовищ спільного хостингу, де ви не можете змінювати змінні на рівні сервера.

Для повного контролю над кодуванням символів MySQL, зіставленням, налаштуваннями InnoDB та параметрами з’єднання вам потрібен або план VPS Хостингу з root-доступом, або Виділений сервер. Обидва надають прямий доступ до `/etc/mysql/my.cnf`, можливість перезапускати службу MySQL та свободу налаштовувати `innodb_large_prefix`, `ROW_FORMAT` та інші параметри, що впливають на успіх міграції utf8mb4.

Якщо ви керуєте кількома базами даних або сайтами клієнтів, VPS з cPanel надає графічний інтерфейс для управління базами даних, зберігаючи при цьому базовий доступ до сервера, необхідний для налаштування набору символів. Для команд, які надають перевагу гнучкості командного рядка з легкою панеллю, Панелі керування VPS пропонують кілька альтернатив, що підходять для різних операційних робочих процесів.

Для проєктів, які також потребують безпечної передачі даних, поєднання міграції бази даних із правильно налаштованим SSL-сертифікатом гарантує, що дані, закодовані в utf8mb4, захищені під час передачі, а не лише у стані спокою.

Контрольний список технічних рішень

Використовуйте цей контрольний список до та після будь-якої міграції з utf8 на utf8mb4:

До міграції:

• [ ] Повна резервна копія `mysqldump` перевірена та відновлювана
• [ ] Версія MySQL підтверджена (потрібна 5.5.3+ для utf8mb4)
• [ ] Статус `innodb_large_prefix` перевірено (увімкніть, якщо на MySQL 5.6/5.7)
• [ ] Усі стовпці `VARCHAR(255)` з індексами на весь стовпець визначені
• [ ] Код набору символів з’єднання програми переглянуто та оновлено
• [ ] Вікно обслуговування заплановано для виробничих баз даних

Після міграції:

• [ ] `SHOW VARIABLES LIKE 'character_set%'` показує `utf8mb4` на рівні сервера
• [ ] `SHOW CREATE TABLE` підтверджує `utf8mb4` для всіх конвертованих таблиць
• [ ] Запит `information_schema.COLUMNS` підтверджує відсутність залишкових стовпців `utf8`
• [ ] `SET NAMES utf8mb4` на рівні програми або еквівалент підтверджено в коді з’єднання
• [ ] Тест вставки emoji пройдено на репрезентативній таблиці
• [ ] Базовий рівень продуктивності запитів порівняно з показниками до міграції
• [ ] Довжини індексів перевірено — немає мовчазного обрізання довгих індексованих значень

Поширені запитання

Чи призводить міграція з utf8 на utf8mb4 до втрати даних?

Ні. utf8mb4 є строгим надмножиною MySQL utf8. Кожен символ, збережений у стовпці utf8, ідентично представляється в utf8mb4. Міграція є неруйнівною для існуючих даних. Єдиний ризик — помилки довжини індексу для стовпців `VARCHAR(255)` з індексами на весь стовпець, які необхідно вирішити шляхом скорочення префікса індексу.

Чому emoji все ще не вдається вставити після того, як я конвертував свої таблиці на utf8mb4?

Найпоширенішою причиною є набір символів з’єднання програми. Якщо ваш код PHP, Python або Node.js підключається без явного вказання `utf8mb4`, MySQL використовує стандартне значення `character_set_client` сервера для цього сеансу. Додайте `SET NAMES utf8mb4` або еквівалентний параметр набору символів до конфігурації вашого з’єднання.

У чому різниця між utf8mb4_unicode_ci та utf8mb4_0900_ai_ci?

`utf8mb4_unicode_ci` базується на правилах зіставлення Unicode 4.0 і є стандартним вибором для MySQL 5.7. `utf8mb4_0900_ai_ci` базується на Unicode 9.0, є стандартним у MySQL 8.0 і є як швидшим, так і більш лінгвістично точним. Використовуйте `utf8mb4_0900_ai_ci` на MySQL 8.0+ для нових проєктів.

Чи значно збільшить перехід на utf8mb4 розмір моєї бази даних?

На практиці — ні. Символи ASCII та більшість символів BMP використовують однакову кількість байт в обох кодуваннях. Лише додаткові символи (emoji, додаткові символи CJK) використовують 4 байти — і вони не могли бути представлені в utf8 раніше. Накладні витрати пам’яті для буферів сортування збільшуються приблизно на 33% для операцій з великою кількістю рядків, але це є незначним на будь-якому сучасному сервері.

Чи можу я налаштувати utf8mb4 на спільному хостингу?

Частково. Ви можете встановити набір символів на рівні бази даних і таблиці за допомогою операторів SQL `ALTER`, і ви можете вказати набір символів у рядку з’єднання вашої програми. Однак ви не можете змінювати `my.cnf` або перезапускати MySQL на спільному хостингу. Стандартні значення на рівні сервера залишаться незмінними, що означає, що нові бази даних, створені через панель хостингу, можуть за замовчуванням використовувати utf8. Повне налаштування utf8mb4 вимагає VPS або виділеного сервера з root-доступом.