How to solve garbled characters in mysql Chinese query

When using the MySQL database, we sometimes encounter garbled query results. The reason for garbled characters is often caused by the inconsistency between the data encoding format stored in the database and our MySQL connection encoding format. One of the more common situations is that Chinese queries are garbled. This article will introduce readers to how to avoid the problem of Chinese query garbled characters in MySQL.

1. The causes of garbled characters in MySQL Chinese queries

The default character set of MySQL is LATIN1, and UTF-8 is a more widely used encoding format. If we use UTF-8 encoding to store Chinese data, but use LATIN1 encoding when querying, then we will have garbled query results.

In MySQL, we can solve the problem of garbled Chinese queries in the following ways:

1. Check the database encoding and table encoding

We need First check whether the encoding of the database and table is correct and ensure that they are consistent with the encoding format when the data is stored. We can query the encoding format of the database and table through the following SQL statement:

SHOW CREATE TABLE table_name;
SHOW VARIABLES LIKE 'character_set_database';

In the query results, we can see the encoding format of the database and table, as well as the character set of the server and client, and the characters of the current connection set matches.

2. Set MySQL connection encoding

You can set the encoding format of the MySQL client connection to UTF-8 through the MySQL CONFIG setting statement to avoid reading data. Garbled characters appear. We can use the following command when connecting to the MySQL server:

mysql --default-character-set=utf8 -u username -p

This command will automatically set the client encoding format to UTF-8 when establishing a connection with the MySQL server to ensure that it will not appear when reading data. Garbled characters.

3. Modify the MySQL configuration file

If we are using MySQL 5.5 or higher, in the configuration file my.cnf, we can set the MySQL server to The encoding format is set to UTF-8:

[client]
default-character-set=utf8

[mysqld]
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci

In this configuration file, we can set the default encoding format of the client and server to UTF-8 to ensure that no garbled characters appear when reading data.

4. Set the MySQL field encoding to UTF-8

If there is already some data in our database that uses incorrect encoding format, we can use the following command Set the encoding format of all fields in the database to UTF-8:

ALTER DATABASE database_name CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

The above command will modify the encoding format of the database and table to UTF-8 to ensure that no garbled characters appear when reading data.

In actual use, we can choose different solutions according to specific situations. When solving the problem of Chinese query garbled characters, we need to pay attention to the fact that conversion between various character sets requires efficiency, so we need to make a trade-off between efficiency and correctness, and base it on our actual business needs. Choose the most appropriate solution.

Summary

To avoid the problem of Chinese query garbled characters in the MySQL database, we need to comprehensively check many aspects such as establishing connections, checking database and table encodings, modifying MySQL configuration files, and setting field encodings. control. When using MySQL database, readers should carefully study each solution and finally choose the most appropriate method based on actual usage conditions. Only in this way can we effectively avoid the problem of Chinese query garbled characters and ensure the correctness and integrity of the data.

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