Garbled characters in mysql

In fact, it is relatively common for garbled characters to appear in MySQL, which is especially prominent when processing Chinese, Japanese, Korean and other texts. Solving this type of problem requires certain skills and experience. Some common methods will be introduced below.

1. Set the character set

In MySQL, the character set is very important. If the character set is set incorrectly, garbled characters will easily appear. Therefore, when using the MySQL database, you must pay attention to setting the character set.

First, we need to confirm which character sets are supported by the MySQL database. We can execute the following command to view the character set supported by the current MySQL database:

show character set;

Then we need to confirm what the character set of the database is. We can execute the following command to view:

show create database 数据库名;

If the character set is not UTF-8, you can execute the following command to modify the database character set to UTF-8:

alter database 数据库名 character set utf8;

Also required for each table Check and modify the character set:

alter table 表名 convert to character set utf8;

2. Specify the character set when importing data

If the data we import is UTF-8 encoded, and the character set of the MySQL database itself is not UTF-8, it is easy to have garbled characters when importing data. To avoid this situation, we must specify the character set as UTF-8 when importing data. You can use the following command to import:

mysql -h 主机名 -u 用户名 -p 密码 数据库名 < 文件名 --default-character-set=utf8

3. Change the character set of the MySQL client

Keeping the character set of the MySQL client consistent with the character set of the database can also avoid garbled characters. occur. You can enter the command line and execute the following command:

set names utf8;

Or specify the character set when connecting to the MySQL server:

mysql -h 主机名 -u 用户名 -p 密码 --default-character-set=utf8 数据库名

4. Modify the my.cnf configuration file

On the MySQL server, we need to check whether the my.cnf configuration file has correctly set the character set. This configuration file contains many default settings, such as the default character set, etc. If the my.cnf file does not set the correct character set, you will need to modify this file manually.

First you need to find the location of the my.cnf file, enter the following command in the command line:

locate my.cnf

Then find the corresponding my.cnf file, use any text editor to open the my.cnf file And add the following content:

[client]
default-character-set=utf8

[mysqld]
default-character-set=utf8
character-set-server=utf8
skip-character-set-client-handshake

5. Modify table fields

If none of the above methods can solve the garbled problem, it means that the garbled data cannot be recovered in the MySQL database, then you need Consider modifying the character set of table fields. You can use the following SQL statement:

ALTER TABLE 表名 CHANGE 列名 列名 VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

The above SQL statement changes a certain character type column of the table name to the utf8 character set and sets the related collation to utf8_general_ci.

In general, there are many ways to encounter garbled characters in MySQL. You can set the character set, specify the character set when importing data, change the MySQL client character set, and modify the my.cnf configuration. Start with files, modify table fields, etc. I hope the above methods can help you solve the garbled problem in MySQL.

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