Переход с utf8 на utf8mb4 в MySQL

В данной статей рассказывается о переход к новому набору символов utf8mb4 в MySQL для поддержки хранения 4-х байтовых символов.

Шаг  1 - резервные копии

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

Шаг 2 - версия MySQL сервера

Набор символов utf8mb4 введён в MySQL начиная с версии 5.5.3, по этому, вам необходимо обновить версию MySQL сервера, если она ниже указанной, либо попросить это сделать своего системного администратора.

Шаг 3 - модификация базы, таблиц, столбцов

Измените набор символов и сравнение для базы данных, таблиц и столбцов на utf8mb4 вместо utf8. Для этого выполните следующие запросы:

Для базы данных:

ALTER DATABASE название_базы CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

Для каждой таблицы:

ALTER TABLE название_таблицы CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Для каждого столбца (не нужно копировать этот пример, для каждого столбца будет свой запрос, здесь указан лишь пример такого запроса для столбца с `VARCHAR`):

ALTER TABLE название_таблицы CHANGE название_столбца название_столбца VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Шаг 4 - проверка максимальной длины столбцов и ключей индексов

Самый трудный шаг на пути к корвертированию MySQL базы данных.

При конвертировании базы данных из utf8 в utf8mb4 максимальная длина столбцов или ключей индексов в байтах остаётся неизменной. Таким образом, она становится меньше в количестве символов, т.к. максимальная длина символа теперь 4 байта, вместо 3.

Например, TINYTEXT столбец может вместить до 255 байт, что коррелирует 85 3-х байтовым или 63 4-х байтовым символам. Допустим у вас есть TINYTEXT столбец, который использует utf8 набор символов, но должен содержать в себе больше 63 символов. Учитывая эти данные, вы не сможете конвертировать этот столбец в utf8mb4, до тех пор, пока не измените тип данных на более вместительный, например TEXT - потому, что, если вы попытаетесь заполнить столбец 4-х байтовыми символами, вы сможете вместить только 63 символа, но не более.

Тоже самое касается ключей индексов. Механизм хранения InnoDB имеет максимальную длину индекса 767 байт, поэтому для utf8 или utf8mb4 столбцов, вы можете инжексировать 255 или 191 символов, соответственно. Если у вас есть utf8 столбцы с индексами, длина которых превышает 191 символ, при использовании utf8mb4 вам нужно будет индексировать меньшее количество символов.

Например:

col1 VARCHAR(500) CHARACTER SET utf8, INDEX (col1(255))

Запрос при использовании utf8mb4 будет выглядеть вот так:

col1 VARCHAR(500) CHARACTER SET utf8mb4, INDEX (col1(191))

Шаг 5 - изменение настроек сервера

В конфигурационном файле MySQL необходимо изменить набор символов на utf8mb4:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = 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)

Видно, что все соответствующие параметры заданы в utf8mb4, кроме character_set_filesystem, который всегда будет двоичным и character_set_system, который всегда в utf8 и не может быть изменён.

Шаг 6 - восстановление и оптимизация всех таблиц

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

REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;

С использованием команды mysqlcheck

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

Итог

Никогда не используйте utf8, если есть возможность выбора utf8mb4. Обновление базы данных и таблиц может занять некоторое время, но оно того стоит.

Копирование статьи без указания прямой ссылки на первоисточник - http://invisionbyte.ru строго запрещено!