Что такое utf8mb4?
utf8mb4 — набор символов, используемый для хранения 4 байта в MySQL, внедрён в 2010 году начиная с версии 5.5.3. Главное отличие utf8mb4 от utf8 в том, что utf8mb4 задействует более полные возможности кодировки UTF8, позволяя поддерживать все языки и специальные символы, не поддерживающие utf8 (например японский язык или смайлики из ios — emoji).
Однако, как можно догадаться, если utf8mb4 использует для хранения 1 символа 4 байта, то база данных может увеличиться в размере, если сравнивать с точно такой же базой данных в utf8. В наше время немного увеличенный размер базы данных не является существенно проблему, по этому, если вы стоите перед выбором использовать utf8 или utf8mb4 набор символов — используйте utf8mb4.
О том, как перекодировать существующую базы данных в utf8mb4, рассказано в соответствующей статье — Переход с utf8 на utf8mb4 в MySQL
Поделиться
Обратная связь
Присоединяйтесь к обсуждению
Вы можете написать сейчас и зарегистрироваться позже. Если у вас есть аккаунт, авторизуйтесь, чтобы опубликовать от имени своего аккаунта.
Примечание: Ваш пост будет проверен модератором, прежде чем станет видимым.
utf8 vs utf8mb4, что использовать?
При поддержке сайтов мне периодически попадается на глаза устаревшая кодировка utf8 в MySql. Об этой теме сказано уже много, но utf8 продолжает встречаться и в новых проектах, хотя пора переходить на utf8mb4.
Не буду тянуть кота за хвост и сразу отвечу на вопрос в заголовке, используйте utf8mb4 для MySql. Это избавит вас от проблем при работе с 4-х байтными символами. Кодировка utf8 является псевдонимом 3-х байтовой кодировки utf8mb3, поэтому MySql выдает ошибки когда встречает непонятные для себя символы в строке.
Incorrect string VALUE: '\xF6\x3F\x98\x41. ' FOR COLUMN 'data' at ROW 1
Через некоторое время разработчики MySql сделают utf8 псевдонимом utf8mb4. Так будет удобнее пользователям этой СУБД, но на данный момент необходимо указывать значение utf8mb4 вручную.
Пример создания таблицы с utf8mb4:
CREATE TABLE `example_table` ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, example_field VARCHAR(255) NOT NULL ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
Настройка кодировки всей базы данных:
ALTER DATABASE your_database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Обновление кодировки для одной таблицы:
ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Отображение подробной информации о таблица, столбцах и кодировках:
SELECT TABLE_SCHEMA, TABLE_NAME, CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET, COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME FROM information_schema.TABLES AS T JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME) JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME) WHERE TABLE_SCHEMA = SCHEMA() ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;
Полезные ссылки
- stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is
- dev.mysql.com/doc/refman/8.0/en/charset-database.html
- stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql
- eversql.com/mysql-utf8-vs-utf8mb4-whats-the-difference-between-utf8-and-utf8mb4
- profiphp.ru/useful/utf8mb4.html
- andreyex.ru/bazy-dannyx/baza-dannyx-mysql/mysql-utf8-protiv-utf8mb4-v-chem-raznitsa-mezhdu-utf8-i-utf8mb4
- dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html
- dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb4.html
Возможно, вам будет интересно:
- Часто используемые порты для веб-разработчика
- Работа с MySQL через командную строку
- Проблемы с кодировкой (utf8) при импорте данных в MySql — кракозябры
Какую кодировку выбрать в MySQL — utf8 или utf8mb4 (utf8mb4_general_ci, utf8mb4_unicode_ci или utf8mb4_0900_ai_ci). Чем они отличаются, как расшифровываются и возможные ошибки
02.07.21 ИТ / Базы данных 23585
При настройке подключения к базе данных (БД) может возникнуть затруднение при выборе кодировки БД. Обычно предлагается целый список кодировок, а точнее сопоставлений (сравнений или наборов символов) и в каждой версии СУБД предлагаемая кодировка может отличаться.
Например, ранее по умолчанию предлагался набор utf8_general_ci. Пользователь может не знать, какая кодировка используется, так как выбор может происходить автоматически при установки готовых веб-приложений. Кодировка может применяться по умолчанию при создании базы данных вручную при помощи, например, phpMyAdmin. Выбранная кодировка распространяется на все таблицы БД и это влияет на то, как будут обрабатываться данные при запросах. Например, может обнаружиться, что при выборке данных не учитывается регистр или, не сохраняются некоторые символы из других языков и прочие объекты (смайлы и т.д.).
Какую кодировку выбрать для БД и таблиц? Для большинства проектов рекомендуется выбирать из подмножества кодировок, относящихся к utf8. Но здесь есть отличия в названиях сопоставлений. Сопоставления utf8 являются 3-ех байтными, для простоты у них не указывается mb3. Обычная utf8 имеет специфичные ограничения MySQL, которые не позволяют использовать символы выше 0xFFFD.
Для старых приложений возможно стоит использовать utf8_general_ci, для новых – utf8mb4_general_ci, utf8mb4_unicode_ci или utf8mb4_0900_ai_ci. Предпочтительным вариантом является не general, а unicode. Отличаются они тем, что utf8mb4_general_ci немного быстрее при выполнении сортировки, но могут возникать проблемы с сортировкой для некоторых языков, в то время как utf8mb4_unicode_ci не имеет подобного недостатка.
Как расшифровываются названия кодировок? Рассмотрим на примере utf8mb4_0900_ai_ci. Здесь:
– utf8 обозначает кодировку;
– mb4 обозначает версию или сколько байт используется в обработке данных для одного символа. Если не указано, то обычно подразумевается mb3;
– 0900 обозначает версию алгоритма сопоставления Unicode (UCA), на которой базируется сопоставление. Если не указано, то обычно подразумевается версия 4.0.0;
– ai обозначает нечувствительность к диакритическим знакам (например, древнегреческие ᾱ, ᾰ). Если не указано, подразумевается ai или as в зависимости от следующей части в имени сравнения, то есть ai для ci и as для cs;
– ci обозначает нечувствительность к регистру, означает, что не будет разницы между строчными и заглавными символами в запросах к БД. Существуют также версии cs, которые являются чувствительными к регистру.
Различные ошибки и предупреждения можно увидеть в отчете используемой системы. Там обычно предлагается перейти на использования современных кодировок из комплекта utf8mb4. Например, в Drupal в отчете состояния можно увидеть строку:
Database 4 byte UTF-8 support – Отключено. 4 byte UTF-8 for mysql is disabled. See the documentation on adding 4 byte UTF-8 support for more information.
Это означает, что система рекомендует использовать новые 4-ех байтные кодировки взамен старых из коллекции utf8.
При написании программного кода может возникать ошибка вида: Unknown collation: ‘utf8mb4_0900_ai_ci’ (Неизвестное сопоставление: ‘utf8mb4_0900_ai_ci’). Это в большинстве случае означает отсутствие требуемой кодировки на сервере баз данных. Например, utf8mb4_0900_ai_ci – это новое сопоставление, доступное только начиная с MySQL 8.0. Также ошибка может появиться в случае применения кодировки, предназначенной для MySQL в другой СУБД, например, в MariaDB. Наборы кодировок различаются от версии к версии, а также для разных СУБД.
MySQL и поддержка Unicode
UTF-8 (от англ. Unicode Transformation Format, 8-bit — «формат преобразования Юникода, 8-битный») — одна из общепринятых и стандартизированных кодировок текста, которая позволяет хранить символы Юникода, используя переменное количество байт (от 1 до 6).
Кодировка UTF-8 может представлять каждый символ в наборе символов Unicode, который варьируется от U+000000 до U+10FFFF. Это 1,114,112 возможных символов. Еще не все коды связаны с символами.
Благодаря тому что UTF-8 кодировка с переменной длиной, это позволяет оптимально кодировать символы для низких диапазонов, тратя на это меньше байтов на символ.
UTF-8 в MySQL
В MySQL кодировка utf8 использует максимум 3 байта на символ и включает только BMP символы. Начиная с версии MySQL 5.5.3 появилась кодировка utf8mb4, которая использует максимум 4 байта на символ и поддерживает дополнительные символы:
- — BMP символов utf8 и utf8mb4 имеют одинаковые характеристики хранения: одинаковый код, одинаковое кодирование, одинаковую длину.
- — uft8 не может хранить дополнительных символов, тогда как utf8mb4 использует 4 байта для сохранения этого символа. Поскольку utf8 не может хранить все символы то при переходе на более позднюю версию можно не беспокоится о потере данных в столбцах.
Из этого следует что в MySQL до версии 5.5.3 была только частичная поддержка UTF-8. При сохранении информации символы которые не входили в MySQL кодировку utf8 удалялись, что вело к потери информации.
mysql> SET NAMES utf8; # just to emphasize that the connection charset is set to `utf8` Query OK, 0 rows affected (0.00 sec) mysql> UPDATE database_name.table_name SET column_name = 'foo��bar' WHERE OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> SELECT column_name FROM database_name.table_name WHERE column_name | +-------------+ | foo | +-------------+ 1 row in set (0.00 sec)
Сам MySQL возвращает предупреждение:
mysql> SHOW WARNINGS; +---------+------+------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xF0\x9D\x8C\x86' for column 'column_name' at row 1 | +---------+------+------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Для того что бы корректно работать с символами UTF8 в MYSQL следует использовать utf8mb4 кодировку.
Для перехода с utf8 на utf8mb4 потребуется изменить кодировки на разных уровнях и перекодировать саму информацию.
1. Изменение кодировки БД
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2. Изменение кодировки таблицы
ALTER TABLE . CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3. Изменение кодировки столбцов
ALTER TABLE . MODIFY TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
4. Максимальная длина в байтах для столбца и индексов:
При конвертации из utf8 в utf8mb4 максимальная длина в байтах для столбца или индекса не изменяется. Но utf8mb4 использует теперь 4 байта на символ, вместо 3 как было в uft8.
Для примера, тип столбца TINYTEXT может хранить до 255 байт, что соответствует 85 трехбайтным или 63 четырехбайтным символам. Из этого следует что вы не сможете записать в это поле больше 63 символов после конвертации. Если вам нужно хранить больше 63 символов, то для этого достаточно изменить тип поля на TEXT.
То же самое для индексов. В InnoDB максимальная длинна индекса 767 байт. Для utf8 это максимум 255 символов, для utf8mb4 соответственно 191 символ. Если в utf8 вы индексировали столбец длиннее чем с 191 символов, то при использовании uft8mb4 нужно изменить максимальное значение длины столбца. Например, VARCHAR(255) на VARCHAR(191).
5. Кодировки клиента, сервера и соединения
Для этого нужно внести правки в файл /etc/mysql/my.cnf и перезапустить MySQL.
[client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET collation_connection = utf8mb4_unicode_ci'
Детальней о init_connect:
Если к базе коннектится mysql клиент с пользователем с привилегией SUPER:
— срабатывает опция в конфигурационном файле default_character_set = utf8mb4
— надо выполнить вручную команду init_connect=’SET collation_connection = utf8mb4_unicode_ci’
Если к базе коннектится mysql клиент с пользователем без привилегии SUPER:
— срабатывает опция в конфигурационном файле default_character_set = utf8mb4
— срабатывает команда в конфигурационном файле init_connect=’SET collation_connection = utf8mb4_unicode_ci’
Если к базе коннектится внешний клиент:
— надо выполнить вручную команду
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci
Проверить что установились верные кодировки можно командой:
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_unicode_ci | | collation_database | utf8mb4_unicode_ci | | collation_server | utf8mb4_unicode_ci | +--------------------------+--------------------+ 10 rows in set (0.00 sec)
Никогда не используйте utf8 в MySQL если есть возможность использовать uft8mb4. Для полной поддержки Unicode придется потратить время и усилия на конвертацию данных и установки кодировок, но это решает много проблем с потерей данных и безопасным хранением данных. Стоит быть очень аккуратным при конвертации таблиц и столбцов, так как запросы полностью блокируют таблицу.