How to solve the Chinese garbled characters in mysql

MySQL database is a common relational database, widely used in web applications, data management and big data storage and other fields. However, in the daily application of MySQL, we often encounter the problem of Chinese garbled characters, which will affect the correctness and readability of the data. Therefore, effectively solving the MySQL Chinese garbled problem is very important for database management and application.

This article will introduce the causes of MySQL Chinese garbled characters and how to effectively solve the problem of MySQL Chinese garbled characters.

1. Reasons for MySQL Chinese garbled characters

The default character set of the MySQL database is Latin2 (ISO 8859-2), which only supports some letters, numbers and a few special characters.

When we use other character sets such as UTF-8 to write Chinese characters, MySQL will mistake these characters as characters of the Latin2 character set, resulting in confusion in character encoding, which is called "garbled code".

2. Solution to MySQL Chinese garbled characters

1. Modify the character set of MySQL

When installing MySQL, we can choose to install different character sets , such as UTF-8, GBK, gb2312, etc.

If we have installed MySQL, we can specify the character set by modifying the configuration file (my.cnf). Open my.cnf and add the following code:

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
character-set-server=utf8mb4

collation-server=utf8mb4_unicode_ci

skip-character-set-client-handshake

Note: The above is the configuration of UTF-8. If you use GBK or GB2312, directly replace it with the corresponding character set.

Note: After the modification is completed, the MySQL service needs to be restarted to take effect.

2. Modify the character set through the command line

We can also modify the MySQL character set through the command line. After logging in to the MySQL command line, enter the following command:

Set the database character set to utf8mb4

ALTER DATABASE yourdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Set the table's character set Set to utf8mb4

ALTER TABLE yourtable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Set the field character set in the table to utf8mb4

ALTER TABLE yourtable MODIFY COLUMN yourcolumn VARCHAR( 255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Editor settings character set

During the development process, we can specify the character set through the editor settings. If you use the Sublime Text editor, click Preferences -> Settings and add the following code:

"default_encoding": "utf8"

This will ensure that when writing code, the encoding is specified. UTF-8 avoids the problem of Chinese garbled characters.

4. Specify the character set in the application

In the web application, we need to manually specify the character set in the code to ensure that it is consistent with the character set in the MySQL database consistent.

PHP code example:

//Set the encoding format to UTF-8
header("Content-type:text/html;charset=utf-8");

// Connect to MySQL
$conn=mysqli_connect("localhost","username","password","dbname");

// Set the MySQL connection character set to UTF-8
mysqli_query($conn,"set names utf8mb4");

5. Use database connection pool

The connection pool in MySQL can improve the efficiency of database connections and can Set the character set for the connection.

Java code example:

// Create a connection pool
DataSource ds = DruidDataSourceFactory.createDataSource(properties);

// Get the connection
Connection conn = ds.getConnection();

//Set the connection character set
conn.createStatement().execute("set names utf8mb4");

6. Specify characters when importing data Set

In the process of importing data, we need to specify the character set. For example, if you use the MySQL command line to import data, execute the following command:

Specify the encoding as UTF- when importing data. 8

mysql -h yourhost -u youruser -p --default-character-set=utf8mb4 yourdb < yourdata.sql

7. Convert Chinese character set

If there is already garbled data in the MySQL database, you can convert it with the following command:

Convert the character set in the database

ALTER DATABASE yourdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Convert the character set in the table

ALTER TABLE yourtable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Convert the character set of the field

ALTER TABLE yourtable MODIFY COLUMN yourcolumn VARCHAR(255) CHAR SET utf8mb4 COLLATE utf8mb4_unicode_ci;

The above is the solution to the MySQL Chinese garbled problem. By correctly setting the character set of MySQL, ensuring that the character set between the application and the MySQL database is consistent, and specifying the character set when importing and converting data, we can effectively solve the Chinese garbled problem of MySQL. At the same time, choose an appropriate method according to the actual situation to ensure the accuracy and readability of the data.

The above is the detailed content of How to solve the Chinese garbled characters in mysql. For more information, please follow other related articles on the PHP Chinese website!