How to change the encoding of MySQL database to utf8mb4

The utf8mb4 encoding is a superset of the utf8 encoding, is compatible with utf8, and can store 4-byte emoticon characters.

The advantage of using utf8mb4 encoding is that you no longer need to consider the encoding and decoding of emoticon characters when storing and retrieving data.

Change the database encoding to utf8mb4:

1. MySQL version

The minimum MySQL version that supports utf8mb4 is 5.5.3+. If not, please upgrade to a newer version.

2. MySQL Driver

5.1.34 is available, the minimum cannot be lower than 5.1.13

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%'; 

3. Modify the MySQL configuration file

Modify the MySQL configuration file my.cnf

my.cnf is usually located in etc/mysql/my.cnf. Once found, please add the following content in the following three parts:

[client] 
default-character-set = utf8mb4 
[mysql] 
default-character-set = utf8mb4

Note the destination

[mysqld] 
character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect = 'SET NAMES utf8mb4'

4. Restart the database and check the variables

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

It doesn't matter what collation_connection, collation_database, collation_server are.

But it must be guaranteed

System Variable Description
character_set_client (character set used by client source data)
character_set_connection (Connection Level Character Set)
character_set_database (the default character set for the currently selected database)
character_set_results (Query Result Character Set)
character_set_server (default character set for internal operations)

These variables must be utf8mb4.

5. Database connection configuration

In the database connection parameters:

characterEncoding=utf8 will be automatically recognized as utf8mb4. You can also omit this parameter and it will be automatically detected.
And autoReconnect=true is a must.

6. Convert the database and the created tables to utf8mb4
Change the database encoding: ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Change the table encoding: ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_general_ci;
If necessary, you can also change the encoding of the column

Summarize

The above is what I introduced to you on how to change the encoding of MySQL database to utf8mb4. I hope it will be helpful to you!