MySQL и поддержка Unicode

/
2017-08-21 11:48
Просмотры: 10618

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 придется потратить время и усилия на конвертацию данных и установки кодировок, но это решает много проблем с потерей данных и безопасным хранением данных. Стоит быть очень аккуратным при конвертации таблиц и столбцов, так как запросы полностью блокируют таблицу.

Добавить комментарий

comments powered by Disqus