Shulou(Shulou.com)06/01 Report--

Editor this time to share with you is to talk about MySQL coding utf8 and utf8mb4 utf8mb4_unicode_ci and utf8mb4_general_ci, the article is rich in content, interested friends can come to know, I hope you can get something after reading this article.

Reference: summary of mysql character set

Utf8mb4 has become the default character set for MySQL 8.0, and utf8mb4_0900_ai_ci is the default collation in MySQL 8.0.1 and later.

The new project only considers utf8mb4

UTF-8 encoding is a variable-length encoding mechanism that can store characters in 1 to 4 bytes.

Because of problems left over from history, the utf8 encoding in MySQL is not a real UTF-8, but a castrated version, with a maximum length of 3 bytes. When you encounter a 4-byte UTF-8 code, such as emoji characters or complex Chinese characters, it will lead to a storage exception.

Starting from 5.5.3, MySQL began to implement the full UTF-8 in utf8mb4 encoding, where mb4 stands for most bytes 4 and takes up to 4 bytes. After 8.0, utf8mb4 will be used as the default character encoding in a certain version.

Set the server default character set to utf8mb4

When creating a database, if you do not specify a character set, the default character set of the server is used. Setting the default character set of the server to utf8mb4 can improve convenience.

Edit the configuration file for MySQL

You only need to care about 5 system variables, and if all of them are changed to utf8mb4, the modification is successful:

Character_set_client

Character_set_connection

Character_set_results

Character_set_server

Character_set_database

My.cnf is the configuration file of MySQL. Remember to back up before you modify it:

Vi / etc/my.cnf

It is inexplicable that the server cannot be started after adding default-character-set=utf8 under [mysqld]. I wish I had changed it like this later (MySQL5.7):

[mysqld] init_connect='SET collation_connection = utf8mb4_unicode_ci' init_connect='SET NAMES utf8mb4' character-set-server=utf8mb4collation-server=utf8mb4_unicode_ci skip-character-set-client-handshak e. [client] default-character-set=utf8mb4

MySQL8.0 is already utf8mb4 by default, so you don't have to change it. If you want to change it, the configuration file is as follows:

[mysqld] datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidcharacter-set-server = UTF8mb4 [client] default-character-set=utf8mb4

Restart and confirm

You can see that the system code, connection code, server code, and client code are all set to UTF-8:

Mysql > show variables like "char%" +-- +-- + | Variable_name | Value | +- -+-+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | / usr/share/mysql-8.0/charsets/ | | validate_password.special_char_count | 1 | +-- | +-- + 9 rows in set (0.00 sec)

Character set related variables in MySQL

Character_set_client: character set of data requested by the client

Character_set_connection: character set that receives data from the client and then transmits it

Character_set_database: the character set of the default database, no matter how the default database is changed; if there is no default database, use the character set specified by character_set_server. This variable is recommended to be managed by the system itself, not artificially defined.

Character_set_filesystem: convert the file name on the operating system to this character set, that is, convert character_set_client to character_set_filesystem. The default binary does not do any conversion.

Character_set_results: character set of the result set

Character_set_server: default character set for database server

Character_set_system: character set for storing system metadata, always utf8, does not need to be set

Specify the character set as utf8mb4 when creating the database

If the default character set for the database is not utf8mb4, you can specify the character set when you create the database:

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Sort character set collation

Characters not only need to be stored, but also need to be sorted or sized. Utf8mb4_unicode_ci is recommended, but there is no problem with using utf8mb4_general_ci.

MySQL 8.0 defaults to utf8mb4_0900_ai_ci, which is one of the utf8mb4_unicode_ci. The specific meaning is as follows:

Uft8mb4 represents the UTF-8 encoding scheme, with a maximum of 4 bytes per character. 0900 refers to the version of the Unicode proofreading algorithm. The Unicode classification algorithm is used to compare two Unicode strings that meet the requirements of the Unicode standard. Ai means insensitive accent. In other words, there is no difference between e, è, é, ê and ë. Ci means case-insensitive. That is, there is no difference between p and P when sorting.

Utf8mb4 has become the default character set, with utf8mb4_0900_ai_ci as the default collation in MySQL 8.0.1 and later. Previously, utf8mb4_general_ci was the default collation. Because the utf8mb4_0900_ai_ci collation is now the default collation, the new table can store characters outside the basic multilingual plane by default. Emoticons can now be stored by default. If you need stress sensitivity and case sensitivity, you can use utf8mb4_0900_as_cs instead.

After reading this article about MySQL coding utf8 and utf8mb4 utf8mb4_unicode_ci and utf8mb4_general_ci, if you think the article is well written, you can share it with more people.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.