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

How to use crontab to add scheduled tasks in Linux

Preface The Linux system is controlled by the sys...

html+css+js to realize the function of photo preview and upload picture

Preface: When we are making web pages, we often n...

Detailed explanation of CSS counter related attributes learning

The CSS counter attribute is supported by almost ...

Mobile development tutorial: Summary of pixel display issues

Preface I believe that in the process of mobile t...

Solution to slow response of Tomcat server

1. Analytical thinking 1. Eliminate the machine&#...

Javascript closure usage scenario principle detailed

Table of contents 1. Closure 2. Closure usage sce...

Example of implementing text wrapping in html (mixed text and images in html)

1. Text around the image If we use the normal one...

MySql index improves query speed common methods code examples

Use indexes to speed up queries 1. Introduction I...

Docker deploys mysql to achieve remote connection sample code

1.docker search mysql查看mysql版本 2. docker pull mys...

CSS3 uses transform to create a moving 2D clock

Now that we have finished the transform course, l...

Two methods to stretch the background image of a web page

There are two solutions: One is CSS, using backgro...

Detailed process of building nfs server using Docker's NFS-Ganesha image

Table of contents 1. Introduction to NFS-Ganesha ...

Analysis of problems caused by MySQL case sensitivity

MYSQL is case sensitive Seeing the words is belie...