What should I do if mysql cannot use Chinese?

MySQL is a common relational database management system. For many developers, it has become a necessity for development work. However, when using MySQL, many users may encounter the problem of "Cannot use Chinese".

The inability of MySQL to use Chinese is mainly manifested in two aspects: on the one hand, the Chinese garbled characters in the MySQL command line interface, and on the other hand, the problem of Chinese character storage and query in the MySQL database. This article will explore these two aspects of problems in detail and provide corresponding solutions.

Chinese garbled characters in the MySQL command line interface

The MySQL command line interface is a core function of MySQL. Developers can execute various commands in this interface. However, many users will find garbled Chinese characters when using the MySQL command line interface. This is mainly because the default character set of MySQL is "latin1" instead of "utf8".

The following describes how to solve the Chinese garbled problem in the MySQL command line interface.

Method 1: Specify a username that supports Chinese character sets by adding the "-u" parameter when using the "mysql" command line tool.

For example, use the following command to specify the user name "root" and the password "password":

mysql -u root -ppassword

Method 2: Use the following command to view the current MySQL default character set:

mysql> show variables like '%character%';

This command will display the current character set settings, in which the values ​​of the three parameters "character_set_client", "character_set_connection" and "character_set_results" are all "latin1".

In order to solve the problem of Chinese garbled characters in the command line interface, you need to change the values ​​of these three parameters to "utf8". The purpose can be achieved through the following command:

mysql> set character_set_client=utf8;
mysql> set character_set_connection=utf8;
mysql> set character_set_results=utf8;

Method 3: Modify the MySQL configuration file "my.cnf".

In the MySQL configuration file, find the following three lines of code:

character-set-client-handshake = FALSE 
character-set-server = utf8mb4 
collation-server = utf8mb4_unicode_ci

Remove the comments of these three lines of code and change the value to "utf8", as shown below:

character-set-client-handshake = FALSE 
character-set-server = utf8 
collation-server = utf8_general_ci

Chinese character storage and query problems in the MySQL database

In addition to the Chinese garbled problem in the command line interface, the MySQL database itself may also have Chinese character storage and query problems.

For example, when we insert some data containing Chinese characters into the MySQL database, we may encounter the following error:

Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1

This is because the default character set of MySQL is "latin1", rather than "utf8". If you want to support creating tables and storing data in Chinese, you need to set the character set of the table structure to "utf8".

The following describes how to solve the problem of Chinese character storage and query in the MySQL database.

Method 1: Solve the problem by specifying the character set as "utf8" when creating the table.

For example, use the following command to create a table named "person" and set the character set of the "name" field in the table to "utf8":

CREATE TABLE person (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (id)
) DEFAULT CHARSET=utf8;

Method 2: In MySQL Set the default character set to "utf8" in the configuration file so that all tables use this character set by default.

In the MySQL configuration file, find the following two lines of code:

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

Remove the comments of these two lines of code and change the value to "utf8", as shown below:

character-set-server=utf8
collation-server=utf8_general_ci

For existing tables, you can modify the character set of the table structure through the following command:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Conclusion

Chinese characters exist both in the command line interface and in the MySQL database. set problem. When you encounter this kind of problem, please solve it according to the methods provided in this article. By adjusting the character set, you will be able to easily query and store Chinese data and avoid various problems encountered in Chinese character processing.

The above is the detailed content of What should I do if mysql cannot use Chinese?. For more information, please follow other related articles on the PHP Chinese website!