How to change MySQL character set utf8 to utf8mb4

For MySQL 5.5, if the character set is not set, the default character set of MySQL is latin1 Latin character set;

However, with the further development of various businesses, in addition to the native language characters of various countries, some emoticons often appear in applications. Before MySQL 5.5, UTF-8 encoding only supported 1-3 bytes and supported the Unicode encoding area of ​​​​the BMP part; 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 the utf8 character set. Therefore, some new businesses, such as ISO, will set the character set of the MySQL database to utf8mb4.

Today, when dealing with an application requirement, I encountered such a problem:

Of course, the best way to adjust it is to change the character set of the MySQL database to utf8mb4 on the client side, but this will cause a lot of changes, and if part of the client is changed to utf8 and part to utf8mb4, confusion is likely to occur.

After several tests, the character set configuration in my.cnf of the MySQL database was 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

Recommendations for using MySQL character sets

• When creating a database/table and performing database operations, try to explicitly specify the character set to be used, rather than relying on the MySQL default settings, otherwise it may cause great trouble when upgrading MySQL;

• Although the garbled characters can be solved in most cases when both the database and the connection character set use latin1, the disadvantage is that SQL operations cannot be performed in units of characters. Generally, it is a better choice to set both the database and the connection character set to utf8.

• When using the mysql C API, set the MYSQL_SET_CHARSET_NAME attribute to utf8 with mysql_options immediately after initializing the database handle. This way, you do not need to explicitly specify the connection character set with the SET NAMES statement, and using mysql_ping to reconnect to a disconnected long connection will also reset the connection character set to utf8.

• For MySQL PHP API, the total running time of page-level PHP programs is generally short. After connecting to the database, you can explicitly set the connection character set once using the SET NAMES statement. However, when using long connections, please be sure to keep the connection open and use the SET NAMES statement to explicitly reset the connection character set after disconnecting and reconnecting.

Comprehensive understanding of the difference between utf8 and utf8mb4 in MySQL: https://www.jb51.net/article/90037.htm

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.