How to change MySQL character set utf8 to utf8mb4

Time:2021-4-18

For MySQL 5.5, if no character set is set, the default character set of MySQL is Latin1 Latin character set;

However, with the further development of various services, in addition to the language characters of each country, some emoticons often appear in applications. Before MySQL 5.5, UTF-8 only supports 1-3 bytes, which supports the Unicode encoding area of BMP. Starting from MySQL 5.5, it can support 4-byte UTF encoding utf8mb4 One character can support more character sets and more emoticons.

Utf8mb4 is compatible with utf8 and can represent more characters than utf8. It is a superset of utf8 character set. So now some new services, such as ISO, will set the character set of MySQL database to utf8mb4.

Today, when dealing with an application requirement, we encounter such a problem:

Of course, the best way to adjust is to change the character set of MySQL database to utf8mb4 on the client side, but the amount of modification is relatively large, and if part of the client side is changed to utf8 and part to utf8mb4, confusion will easily occur.

After several tests, the MySQL database my.cnf The configuration of character set in is changed to the following configuration:


[client] 
default-character-set=utf8mb4 
 
[mysqld] 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci 
init_connect='SET NAMES utf8mb4' 
skip-character-set-client-handshake = true 
 
[mysql] 
default-character-set = utf8mb4 

Suggestions for using MySQL character set

When establishing database / table and operating database, try to explicitly indicate the character set to use instead of relying on the default settings of MySQL, otherwise it may cause great trouble when upgrading MySQL;

When using Latin1 in both database and connection character set, the problem of garbled code can be solved in most cases, but the disadvantage is that SQL operation can’t be carried out by taking characters as the unit. In general, setting both database and connection character set to utf8 is a better choice;

When using MySQL C API, use MySQL immediately after initializing the database handle_ Options setting MySQL_ SET_ CHARSET_ The name attribute is utf8, so that you do not need to explicitly specify the connection character set with the set names statement, and use mysql_ When Ping a disconnected long connection, the connection character set will also be reset to utf8;

For MySQL PHP API, the total running time of general page level PHP programs is relatively short. After connecting to the database, you can explicitly set the connection character set with set names statement. However, when using long connection, please pay attention to keep the connection unobstructed and reset the connection character set with set names statement after disconnecting and reconnecting.

Fully understand the difference between utf8 and utf8mb4 in MySQL https://www.jb51.net/article/90037.htm

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]