Using utf8mb4 in MySQL

Question

To use the 4-byte utf8mb4 in MySQL (5.6.11), I set the following variables in the my.ini file ( my.cnf not found). This file is located in a hidden folder called Application Data ( C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.6 ) in Windows XP. It is not available in the installation directory.

 [client] port=3306 default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4 [mysqld] init-connect='SET NAMES utf8mb4' collation_server=utf8mb4_unicode_ci character_set_server=utf8mb4 

And then issuing the following command

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

The following list is still displayed.

From the image itself, several variables are still using 3-byte utf8 .

---

Before doing this, the following command has already been issued to make the appropriate changes to the database.

 ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

And the following command was also issued for each table in the specified database.

 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

---

However, what is the reason that some variables are not yet set for the specified character set, as well as for sorting? What is missing?

The system itself (operating system) was restarted after each task set above.

Answer 1

The client usually sets these values ​​when connecting. The settings in my.ini are simply the default values ​​that apply when the client does not explicitly specify a connection encoding. Since they are unreliable, each client must specify a connection encoding. Since you have some fancy screenshot, I assume that you are connecting to some GUI utility that explicitly explicitly sets up some connection encodings.

PHP example of setting the encoding of the connection:

 new PDO('mysql:host=localhost;charset=utf8mb4') 

Answer 2

If you show your global variables, you can see that all of your settings are actually correct.

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

This may be due to this bug. In the past, I encountered the same problem. I changed the DB encoding from utf8 to utf8mb4. Querying directly from the mysql command line was fine, but I was having trouble pasting emojis with Workbench. I finished the setup manually by running

SET NAMES 'utf8mb4'

every time I open a connection to my database using Workbench.

Using Sequel Pro as an alternative was also great.

Answer 3

I think you are connecting as root, so init-connect='SET NAMES utf8mb4' not running.

It is unwise to use root (or SUPER) for any application code; just for administrative action.

Answer 4

In win7

• use "win + R" and enter "services.msc"

• find mysql service

• check the path to the file. He will tell you where my.ini

• open and add some properties:

  [client] default-character-set = utf8mb4

  [MySQL] default-character-set = utf8mb4

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

• restart mysql service