MySQL кодування utf8mb4: підтримка emoji та Unicode

Чому utf8 у MySQL не підтримує emoji

MySQL реалізував кодування utf8 як 3-байтове, що покриває більшість символів, але не emoji (вони займають 4 байти в UTF-8). При спробі зберегти emoji у колонці utf8 MySQL мовчки обрізає рядок до першого проблемного символу або повертає помилку, залежно від режиму sql_mode. utf8mb4 — справжній 4-байтовий UTF-8, що підтримує повний Unicode, включно з emoji та рідкісними ієрогліфами.

Налаштування my.cnf для utf8mb4 за замовчуванням

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
character-set-filesystem = utf8mb4

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

sudo systemctl restart mysql

Перевірка поточного кодування

mysql -u root -p -e "
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';"

Зміна кодування наявної бази даних

-- Змінити charset бази даних
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Змінити charset таблиці
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Змінити колонку
ALTER TABLE mytable MODIFY content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Конвертація всіх таблиць у базі даних

-- Генерація команд для всіх таблиць
SELECT CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` ',
    'CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
) AS migration_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_TYPE = 'BASE TABLE';

Розбір типових помилок під час конвертації

• Incorrect string value: '\xF0\x9F\x98\x80' for column — колонка досі в utf8 чи latin1, а застосунок уже намагається писати emoji; конвертація з розділу вище має пройти на цій таблиці до повторного вставлення.
• Specified key was too long; max key length is 767 bytes — індекс на VARCHAR(255) при utf8mb4 займає до 1020 байт (255×4), що перевищує ліміт старого формату таблиць; рішення — скоротити довжину індексованого поля, перейти на рушій InnoDB з більшими префіксами або увімкнути innodb_large_prefix.
• Illegal mix of collations for operation 'UNION' — при вибірковій конвертації частина таблиць лишилася на utf8_general_ci, а частина вже на utf8mb4_unicode_ci; запити, що об'єднують такі таблиці, падають, поки collation не приведено до одного значення.
• Data too long for column після конвертації — 4-байтові символи займають більше місця, ніж закладалося під час розрахунку довжини VARCHAR; іноді після переходу потрібно збільшити довжину колонки, а не лише змінити charset.

Collation: unicode_ci проти general_ci проти bin

Налаштування в PHP (PDO)

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=mydb;charset=utf8mb4',
    'user', 'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'
    ]
);

Порівняння utf8 і utf8mb4

Коли конвертація не така проста

• У базі вже є дані з emoji, збережені як «обрізані» рядки в utf8, — відновити початковий текст із пошкоджених байтів неможливо, лише заново отримати дані з джерела.
• Таблиця використовує повнотекстовий індекс (FULLTEXT) — під час зміни charset його потрібно пересоздати наново, конвертація «на льоту» не переносить статистику індексу автоматично.
• Застосунок підключається до MySQL через кілька різних драйверів (PHP, Java, cron-скрипти на Python) — charset потрібно синхронно змінити в усіх місцях підключення, інакше частина сервісів продовжить писати биті дані навіть після конвертації таблиць.

Як перевірити, що конвертація пройшла успішно

# Переконатися, що всі таблиці бази в utf8mb4
mysql -u root -p -e "
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='mydb' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';"
# Порожній результат означає, що конвертовано всі таблиці

# Тестове вставлення emoji
mysql -u root -p mydb -e "
CREATE TEMPORARY TABLE emoji_test (t TEXT) CHARACTER SET utf8mb4;
INSERT INTO emoji_test VALUES ('Тест emoji: ');
SELECT * FROM emoji_test;"

Якщо запит до information_schema не знаходить таблиць поза utf8mb4, а тестове вставлення проходить без помилки Incorrect string value — конвертацію завершено коректно на рівні бази даних.

Часті питання

Чи ризикує конвертація бази в utf8mb4 втратою даних у наявних рядках, чи це завжди безпечна операція?

Сама команда конвертації зазвичай безпечна для рядків, які і так зберігали коректний текст, бо розширює набір символів, а не звужує його. Ризик з'являється там, де в колонці вже лежать зіпсовані байти від попередньої невдалої спроби зберегти емодзі — такі обрізані значення конвертація не відновить, їхній початковий вигляд уже втрачено і його можна лише заново отримати з джерела.

Чи працює utf8mb4 на практиці повільніше за utf8 через те, що символ може займати більше байт?

Різниця у більшості звичайних навантажень майже не відчувається: додатковий байт на символ незначно збільшує обсяг даних на диску і в пам'яті, а саме порівняння рядків за обраним collation працює так само. Помітніший вплив проявляється лише там, де індекси на довгих рядкових полях впираються в обмеження довжини ключа і потребують скорочення чи зміни налаштувань рушія.

Якщо в застосунку вже є зіпсовані через старе кодування дані з емодзі, чи можна відновити початковий текст?

Ні, якщо символ був обрізаний на рівні кодування до першого проблемного байта, то частина початкової інформації знищена безповоротно і жодна подальша зміна charset її не поверне. Єдиний надійний шлях — заново отримати такі дані з першоджерела, чи то вихідний експорт, лог застосунку, чи зовнішній сервіс, звідки вони колись потрапили до бази.

Чи потрібно змінювати кодування і в коді застосунку, чи досить поміняти його лише в базі даних?

Змінювати налаштування треба узгоджено в обох місцях: якщо база вже на utf8mb4, а з'єднання із застосунку досі відкривається зі старим кодуванням, сервер СУБД мовчки перетворюватиме символи під старий набір просто на льоту, і ті самі проблеми з обрізанням емодзі повернуться. Параметр підключення і налаштування бази мають вказувати на один і той самий charset одночасно.