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.