How to set UTF-8 encoding in MySQL

MySQL is an open source relational database management system that is widely used in various websites and applications. Encoding issues are critical to the correctness and data integrity of the database. This article will introduce how to set UTF-8 encoding in MySQL.

1. Understanding UTF-8 encoding

1.1 Introduction to UTF-8

UTF-8 is a Unicode character encoding format that can represent all characters in the Unicode standard , so it is widely used in internationalization and multi-language support websites and applications. Compared with other character encoding formats, UTF-8 uses more compact storage space and is suitable for various data storage and transmission occasions.

1.2 UTF-8 encoding principle

UTF-8 encoding adopts a variable length encoding method. Generally, 1-4 bytes are used to represent a character, of which the first character Sections are used to represent the total number of bytes used by characters, and the following bytes are used to store specific character content. The specific encoding rules are as follows:

Range                                                                                                                                                                                          

0000 0080-0000 07FF##0000 0800-0000 FFFF1110xxxx 10xxxxxx 10xxxxxx11110xxx 10xxxxxx 10xxxxxx 10xxxxxx Among them, x represents a binary bit. The encoding length of UTF-8 varies according to the number of bytes occupied by the character. The maximum can represent 4 Bytes of characters, namely the "high surrogate segment" and "low surrogate segment" in Unicode. 2. Set the character encoding of MySQL2.1 Modify the my.cnf configuration fileIn the Linux environment, the MySQL configuration file is /etc/my.cnf. You can Add the following configuration items to set the character encoding of the database: [mysqld]

0000 0000-0000 007F	0xxxxxxx
110xxxxx 10xxxxxx
	##0001 0000-0010 FFFF

character-set-server=utf8

collation-server=utf8_general_ci

Among them, character- set-server is used to set the character set used by MySQL to create tables by default, while collation-server sets the collation rules used by MySQL by default. Here they are all set to UTF-8 encoding to ensure the correctness and compatibility of various characters in the database. After the modification is completed, restart the MySQL service to reload the my.cnf configuration file:

$ service mysql restart

2.2 Directly modify the database

If you want to modify the existing database To modify the character encoding of a table or field in a database, you can use the following SQL command:

ALTER DATABASE database name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Among them, utf8mb4 is the UTF-stored in MySQL. 8 encoding method that can represent all Unicode character encodings. At the same time, it should be noted that different MySQL versions may support different character encoding methods, so you need to refer to the corresponding documents when modifying the character encoding.

2.3 Modify the connection encoding

In programming languages ​​such as PHP, connecting to MySQL also requires setting the character encoding to ensure the correctness of the data. In the MySQLi connection, you can use the following code to set it:

$mysqli = new mysqli("localhost", "username", "password", "dbname");

mysqli_set_charset($mysqli," utf8");

In PDO connection, you can use the following code:

$dsn = "mysql:host=localhost;dbname=dbname;charset=utf8";

$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

$pdo = new PDO($dsn, "username", "password", $options);

3. Summary

As a relational database widely used in websites and applications, MySQL is crucial to set the correct character encoding. Through the introduction of this article, we understand the principle of UTF-8 encoding and how to set character encoding in MySQL. In actual development, MySQL's character encoding needs to be set appropriately according to different needs and scenarios to ensure the correctness and integrity of the data.

The above is the detailed content of How to set UTF-8 encoding in MySQL. For more information, please follow other related articles on the PHP Chinese website!