How to set utf8mb4 on MySQL 5.7 (windows)

Question

I have an installation of MySQL 5.7 on a Windows 7 machine. I need to change the character set of the database in order to persist emoji.

The configuration into my.ini:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
default-character-set = utf8mb4
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Into the windows services I saw that the configuration file path that is loaded is correct.

Looking into database properties with the query:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

I got the following results:

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    utf8
character_set_system    utf8
collation_connection    utf8_general_ci
collation_database  utf8mb4_general_ci
collation_server    utf8_general_ci

So, the values of collation_server, character_set_system, character_set_server, character_set_results, character_set_connection, character_set_client are wrong.

How can I fix them? Thanks.

Rick James Rick James · Accepted Answer · 2016-02-13T19:37:03

After connecting to MySQL, perform SET NAMES utf8mb4. That will establish that your client is using the full 4-byte encoding for reading/writing.

You can do this in my.cnf/my.ini:

init_connect = 'SET NAMES utf8mb4'

but keep in mind that when connecting as root (or any SUPER user), init_connect is ignored.

Also, the tables/columns must be CHARACTER SET utf8mb4.

How to set utf8mb4 on MySQL 5.7 (windows)

1 Answers