Правильное изменение кодировки БД на utf8mb4, для корректной работы эмодзи и т.п.

Всем привет, тема давно «избитая» но всегда актуальная… Наверняка каждый, наблюдал ситуацию с неправильной работой сайта при вставке эмодзи в тексте. Дело в том, что стандартная кодировка utf8 в MySQL не поддерживает хранение таких символов. Для этого необходимо использовать кодировку utf8mb4, которая позволяет хранить символы, занимающие до 4 байт…

В этой статье мы рассмотрим, как правильно изменить кодировку базы данных на utf8mb4 для корректной работы смайликов и других символов на сайте.

Почему utf8mb4?

Кодировка utf8mb4 является расширением стандартной utf8 и поддерживает хранение символов, которые занимают до 4 байт. Это включает в себя эмодзи, символы из редких языков, а также другие специальные символы. Если ваш сайт использует стандартную кодировку utf8, то при попытке сохранить эмодзи в базе данных вы можете столкнуться с ошибками или искажением данных.

Почему general_ci, а не unicode_ci?

general_ci: Используется для сравнения символов в рамках одного языка.
Алгоритмы сравнения проще и оптимизированы для работы с однобайтовыми символами.
Сравнение выполняется быстрее, так как не требуется учитывать сложные правила Unicode.

unicode_ci: Поддерживает многобайтовые символы и правила сравнения для множества языков и символов Unicode.
Алгоритмы сложнее, так как должны учитывать особенности разных языков, регистров и комбинированных символов (например, акценты, диакритические знаки).
Это делает сравнение медленнее по сравнению с general_ci.

Итог: unicode_ci используем для мультиязычных сайтов.

Перед тем как приступить к изменению кодировки базы данных, обязательно делаем резервную копию БД!

Для изменения кодировки базы данных на utf8mb4 нужно использовать phpMyAdmin где мы должны будем выполнить SQL-запросы вручную.

1. Изменение кодировки базы данных.
В phpMyAdmin выберите вашу базу данных, перейдите на вкладку «Операции» (Operations) и измените кодировку базы данных на utf8mb4_general_ci.

2. Изменение кодировки таблиц.
Для изменения кодировки всех таблиц в базе данных можно использовать следующий SQL-запрос:

SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;') as sqlcode
   FROM `information_schema`.`TABLES` t
   WHERE 1
   AND t.`TABLE_SCHEMA` = 'имя_базы'
   ORDER BY 1;

Замените имя_базы на название вашей базы данных. Этот запрос сгенерирует список SQL-запросов, которые необходимо выполнить для изменения кодировки всех таблиц.

3. Сгенерированные запросы.
После выполнения SQL запроса в предыдущем шаге, мы должны получить список ALTER TABLE… по каждой таблице, но отображаются они не все.
Необходимо изменить «Количество строк» на 100 или больше, а так же нажать «Параметры» и отметить «Полные тексты» и нажать кнопку «Вперёд».



Потом выделить и скопировать получившиеся строки. Эти строки выполнить SQL запросом в нашу БД. Имейте в виду, бывает, если много таблиц, между строк может оказаться надпись sqlcode, её мы конечно же удаляем.



Ошибки выполнения

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



Но иногда наоборот, бывает слишком большая длина для некоторых полей. В таких случаях надо ставить значение 191.

Пример ошибки, когда слишком большое значение длины:

#3886 - Could not change column 'query' of table 'modx_mse2_queries'. The resulting size of index 'PRIMARY' would exceed the max key length of 1000 bytes.

Настройка MODX

После изменения кодировки базы данных необходимо убедиться, что сайт корректно работает с новой кодировкой:

1. Проверка настроек подключения к базе данных.
Убедитесь, что в конфигурационном файле MODX (core/config/config.inc.php) указана правильная кодировка подключения к базе данных:

$database_charset = 'utf8mb4';

2. Проверка отображения данных.
После внесения изменений проверьте, что данные на сайте отображаются корректно, включая эмодзи и другие специальные символы.

Вот и всё! Спасибо за внимание!