How to set mysql5.7 encoding set to utf8mb4

How to set mysql5.7 encoding set to utf8mb4

I recently encountered a problem. The emoticons or some emojis on mobile terminals are 4 bytes, but UTF-8 is 3 bytes. I searched for many solutions on the Internet and finally provided a solution of my own. If the current dilemma is: your MySQL version is before 5.5.3 (versions before 5.5.3 cannot use utf-8mb4 encoding), you need to reinstall a higher version of MySQL and then obtain the encoding.

1. Check your current database version

Use command: select version();

If the current database version is before 5.5.3, you need to reinstall the database. If it is after this version, skip the second and third steps.

2. Back up the data in the current database

The database visualization software I use here is Navicat. Right-click the corresponding database under Database, then select "Dump SQL File" and select "Data and Structure" to export the database file with the suffix .sql and save it on the hard disk. After installing the new database version, select the database and right-click "Run SQL File" so that the data will not be lost.

3. Completely delete the current database

Deleting a database is very cumbersome and sometimes it is not deleted cleanly. Here are some methods that I have referenced and found to be useful.

3.1 Delete MySQL service

Control Panel -> All Control Panel Items -> Programs and Features, uninstall MySQL server!

3.2 Delete all files in the mysql directory

Delete the my.ini file and all files in the mysql folder. The general installation directory is: C:\Program Files\MySQL

3.3 Deleting registry information

Press "win" + R at the same time and enter regedit to enter the registry manager

delete:

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\Eventlog\Application\MySQL folder

delete:

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\Eventlog\Application\MySQL folder.

delete:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\MySQL folder

If the above folder does not exist or does not exist, just ignore it.

3.4 Delete the hidden mysql folder

It is usually hidden. If you search for this directory in the C drive, you should be able to find C:\ProgramData\MySQL\MySQL Server 5.7

4 Install database mysql5.7

First download mysql5.7. I used the msi version. I won't describe the installation process in detail. In short, I installed it to the default path, which is the C drive. After the installation, in order to call up the small black window, I configured the system environment variables



Select path in the system variables section, and enter the installation path of the database under path. Just go to the path of the bin folder. At this time, call out the small black window to view your encoding format

The statement is: SHOW VARIABLES LIKE 'character_set%';

The displayed characters should all be utf-8, because the default is utf-8.

5 Modify the encoding format

First, we need to modify the configuration in the configuration file (my.ini), but we found that there is no such file in the installation directory.


This is what annoyed me for the whole afternoon. Later, after consulting the information, I found that the file was hidden in a hidden folder in the C drive. I searched for C:\ProgramData\MySQL\MySQL Server 5.7 in the C drive.

The file was found:

Make configuration changes in the my.ini file

[client] 
default-character-set = utf8mb4

[mysql] 
default-character-set = utf8mb4

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

Note that the two bolded items above are in the original file configuration, and their values ​​are both utf-8, so when adding these two items, be sure to replace the original ones, otherwise MySQL will not be able to read the configuration file and the service will not be able to start.

After the modification is completed, save it and restart the database service. The address for restarting the service is as follows


Then type two more character set commands in the small black window to verify

SHOW VARIABLES LIKE 'character_set%';

The character set of the database has been modified


SHOW VARIABLES LIKE 'collation%';

The character set of the data table has also been modified

It is worth noting that utf8mb4 is compatible with utf-8, so there is no need to worry about the original data being garbled.

If you are using MySQL version 5.5.3 or earlier, you can store data in MEDIUMBLOB format to avoid garbled characters and do not want to change the encoding set, but I do not recommend it.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • How to change the encoding of MySQL database to utf8mb4
  • Spring Data Jpa Mysql sample code using utf8mb4 encoding
  • How to deal with errors in MySQL's emoji table storage [change encoding to utf8mb4]
  • MySQL encoding utf8 and utf8mb4 utf8mb4_unicode_ci and utf8mb4_general_ci

<<:  How to set up automatic daily database backup in Linux

>>:  Example of implementing the Graphql interface in Vue

Recommend

Summary of essential knowledge points for MySQL query optimization

Preface Query optimization is not something that ...

Detailed explanation of the principle of js Proxy

Table of contents What is Proxy Mode? Introducing...

Using Vue3 (Part 1) Creating a Vue CLI Project

Table of contents 1. Official Documentation 2. Cr...

4 ways to optimize MySQL queries for millions of data

Table of contents 1. The reason why the limit is ...

Vue shopping cart case study

Table of contents 1. Shopping cart example 2. Cod...

Learn Hyperlink A Tag

ask: I have styled the hyperlink using CSS, but i...

Use docker to build kong cluster operation

It is very simple to build a kong cluster under t...

How to use Webstorm and Chrome to debug Vue projects

Table of contents Preface 1. Create a new Vue pro...

How to manually install MySQL 5.7 on CentOS 7.4

MySQL database is widely used, especially for JAV...

Deep understanding of line-height and vertical-align

Several concepts Line box: A box that wraps an in...

Design theory: people-oriented design concept

<br />When thoughts were divided into East a...

LINUX Checks whether the port is occupied

I have never been able to figure out whether the ...

MySQL sorting feature details

Table of contents 1. Problem scenario 2. Cause An...