How to solve garbled characters in mysql Chinese query

The reason why MySQL Chinese query is garbled

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 to 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 the 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 garbled characters when reading data. 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 encoding format of the MySQL server to UTF-8 through the following settings:

[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 when reading data No garbled characters will appear.

4. Set the MySQL field encoding to UTF-8

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

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 all the encoding formats of the database and table to UTF-8 to ensure that when reading data No garbled characters will appear.

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.

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!