utf8 vs utf8mb4 в MySQL: Полное техническое руководство

Набор символов utf8 в MySQL — это неправильное название: он не является настоящей реализацией UTF-8. Он кодирует символы, используя только 1–3 байта, что означает, что он молча отбрасывает или отклоняет любую кодовую точку Unicode выше U+FFFF, включая все эмодзи и значительную часть дополнительных символов 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`. Это включает:

• Все стандартные эмодзи (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 vs 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 байта в обоих кодировках.
• Только дополнительные символы (эмодзи, дополнительные символы 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'

});

“`

Отсутствие установки кодировки символов соединения — это единственная наиболее распространённая причина, по которой эмодзи по-прежнему не вставляются после, казалось бы, завершённой миграции.

Особенности 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` на уровне приложения или эквивалент подтверждён в коде соединения
• [ ] Тест вставки эмодзи пройден для репрезентативной таблицы
• [ ] Базовые показатели производительности запросов сравнены с метриками до миграции
• [ ] Длины индексов проверены — нет молчаливого усечения длинных индексированных значений

FAQ

Приводит ли миграция с utf8 на utf8mb4 к потере данных?

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

Почему эмодзи по-прежнему не вставляются после того, как я конвертировал таблицы в 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 используют одинаковое количество байт в обоих кодировках. Только дополнительные символы (эмодзи, дополнительные символы CJK) используют 4 байта — и они были непредставимы в utf8 ранее. Накладные расходы на память для буферов сортировки увеличиваются примерно на 33% для операций с большим количеством строк, но это незначительно на любом современном сервере.

Можно ли настроить utf8mb4 на общем хостинге?

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