Как перейти с utf8 на utf8mb4 в MySQL?

Question

[Максим]

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

(1366, "Incorrect string value: '\\xF0\\x9F\\x92\\xB0<b...' for column 'description' at row 1")

Версия mysql 5.7

Настройки mysql

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

init-connect='SET NAMES utf8'


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# For the special characters
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     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.01 sec)

Часть настроек не изменяется, как решить данный вопрос?
Бд чистая.

Comments

[OCTAGRAM]

Кстати, лучше utf8mb4_unicode_520_ci

Answer 1

[Довольный Айтишникъ]

При такой конфигурации

[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

имеем

+--------------------------+--------------------+
| 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_general_ci |
| collation_database          | utf8mb4_unicode_ci |
| collation_server               | utf8mb4_unicode_ci |
+-----------------------------+------------------------+
10 rows in set (0.01 sec)

Comments

[Довольный Айтишникъ]

Для percona 5.7 конфиг немного другой:

[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

[client]
default_character-set = utf8mb4

[Igor Deyashkin]

Хочу поделиться своими комментариями по поводу этих настроек. В основном информация подчерпнута из этого поста.

[mysqld]
# нам нужно изменить используемую по умолчанию collation_connection с utf8_general_ci на utf8_unicode_ci
# для пользователя с привелегией SUPER init_connet срабатывать не будет, надо это иметь ввиду. Стоит бы создать отдельного пользователя для бд и работать из-под него.
init_connect=‘SET collation_connection = utf8_unicode_ci’

# устанавливаем настройки для сервера - они и будут использоваться по умолчанию для всего остального (бд, таблицы, колонки)
character-set-server = utf8
collation-server = utf8_unicode_ci

# внешние клиенты (php) должны быть сами корректно настроены

# можно использовать эту опцию для того, чтобы игнорировать их настройки, но я думаю, не стоит без необходимости.
# Правда, если ее указать, то и под рутовым пользователем будет collation_connection = utf8_unicode_ci
# skip-character-set-client-handshake

# для клиентов, таких как mysql, mysqldump и т.п.
[client]
default-character-set = utf8

Answer 2

[Rsa97]

По минимуму достаточно сменить charset одного поля в таблице

ALTER TABLE `table` 
  MODIFY `description` VARCHAR(xxx) CHARACTER SET utf8mb4;

Ну или сразу у всех текстовых полей в таблице

ALTER TABLE `table` CONVERT TO CHARACTER SET utf8mb4;