MySQL и поддержка Unicode
UTF-8
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 id = 9001; Query 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 id = 9001; +-------------+ | 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 <database> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
2. Изменение кодировки таблицы
ALTER TABLE <database>.<table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3. Изменение кодировки столбцов
ALTER TABLE <database>.<table_name> MODIFY <column_name> 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 придется потратить время и усилия на конвертацию данных и установки кодировок, но это решает много проблем с потерей данных и безопасным хранением данных. Стоит быть очень аккуратным при конвертации таблиц и столбцов, так как запросы полностью блокируют таблицу.
Добавить комментарий