How to change the encoding of MySQL database to utf8mb4

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!

You may also be interested in:
  • Steps to change mysql character set to UTF8 under Linux system
  • Example of utf8mb4 collation in MySQL
  • MySQL encoding utf8 and utf8mb4 utf8mb4_unicode_ci and utf8mb4_general_ci
  • mysql charset=utf8 do you really understand what it means
  • How to change MySQL character set utf8 to utf8mb4
  • mysql garbled characters latin1 characters converted to UTF8 details

<<:  Example of making XML online editor using js

>>:  VMware's detailed tutorial on how to create a Linux virtual machine and set up a virtual machine network

Recommend

An audio-visual Linux distribution that appeals to audiophiles

I recently stumbled upon the Audiovisual Linux Pr...

Example of how to build a Mysql cluster with docker

Docker basic instructions: Update Packages yum -y...

Detailed explanation of creating stored procedures and functions in mysql

Table of contents 1. Stored Procedure 1.1. Basic ...

Implementation code of html floating prompt box function

General form prompts always occupy the form space...

Detailed explanation of the principle and usage of MySQL stored procedures

This article uses examples to explain the princip...

MySQL full-text fuzzy search MATCH AGAINST method example

MySQL 4.x and above provide full-text search supp...

How to install PHP7 Redis extension on CentOS7

Introduction In the previous article, we installe...

InnoDB engine redo file maintenance method

If you want to adjust the size and number of Inno...

Some thoughts and experience sharing on web page (website) design and production

First, before posting! Thanks again to I Want to S...

How to completely uninstall iis7 web and ftp services in win7

After I set up the PHP development environment on...

How to build your own Angular component library with DevUI

Table of contents Preface Creating a component li...

CentOS 6.4 MySQL 5.7.18 installation and configuration method graphic tutorial

The specific steps of installing mysql5.7.18 unde...

How to optimize MySQL query speed

In the previous chapters, we introduced how to ch...