Summary of Solutions to Chinese garbled characters in mysql import and export data

This article summarizes how to solve Chinese garbled characters in mysql import and export data. Chinese garbled characters are generally caused by encoding settings during import. We only need to adjust the encoding consistency to solve this problem, the following is a summary of some methods found by Baidu.

This article summarizes how to solve Chinese garbled characters in mysql import and export data. Chinese garbled characters are generally caused by encoding settings during import. We only need to adjust the encoding consistency to solve this problem, the following is a summary of some methods found by Baidu.

In linux

Linux uses utf8 encoding by default, while windows uses gbk encoding, so the above garbled characters may occur.

Solve the Problem of garbled data during mysql Import and Export

The first thing to do is to determine the encoding format of the exported data. When using mysqldump, add -- default-character-set = utf8,

For example, the following code:

The Code is as follows:
Mysqldump-uroot-p -- default-character-set = utf8 dbname tablename> bak. SQL

When importing data, use -- default-character-set = utf8:

The Code is as follows:
Mysql-uroot-p -- default-character-set = utf8 dbname <bak. SQL

In this way, the unified encoding solves the garbled characters in mysql data migration.

I use windows as the data source for export and import the mysql database in the freebsd Environment

Solution:

Export data

1. Use mysql as the source for exporting databases on windows. View the system variables of the character encoding:

The Code is as follows:

The Code is as follows: Mysql> show variables like '% char % '; + --------- + ---------- + | Variable_name | Value | + --------- + ---------- + | Character_set_client | latin1 | | Character_set_connection | latin1 | | Character_set_database | latin1 | | Character_set_filesystem | binary | | Character_set_results | latin1 | | Character_set_server | gbk | | Character_set_system | utf8 | | Character_sets_dir | D: mysql1_charsets | + --------- + ---------- + Check character_set_database. latin1 is used here. latin1 cannot contain multi-byte character sets.

2. Set the system variable to utf8 in windows

The Code is as follows:
Mysql> set character_set_database = utf8; # set the default character set to utf8

Iii. Export data

The Code is as follows:
Mysql> select * from table into outfile 'C: table.txt 'where + condition

At this time, I exported some of the data I want and saved it in table.txt as a txt file.

Import Data

On freebsd Platform

1. System variables with the same character encoding

The Code is as follows:

Mysql> show variables like '% char % '; + --------- + ---------- + | Variable_name | Value | + --------- + ---------- + | Character_set_client | latin1 | | Character_set_connection | latin1 | | Character_set_database | latin1 | | Character_set_filesystem | binary | | Character_set_results | latin1 | | Character_set_server | gbk | | Character_set_system | utf8 | | + --------- + ---------- + Mysql> set character_set_database = utf8; # set the default character set to utf8

Ii. Reprinting data

The Code is as follows:
Mysql> load data local infile '/home/table.txt' into table 'table ';

Now, the condition data is completely imported and exported, and garbled characters are processed.
In short, when importing and exporting two mysql servers, make sure that the character_set_database parameters of the two servers are the same so as to prevent garbled characters. Of course we can also use other character sets, such as gbk for adjustment. Operation as needed

Solution 3

Garbled Solution
After the data is imported, the command line displays Chinese garbled characters.
Run the following command to view the system Character Set

The Code is as follows:
Show variables like 'Char % ';

If it is not GBK or UTF8, stop the mysql service and modify the my. ini file in the installation directory,
Change the variables in the file to the following. If this variable is not available, add these variables.

The Code is as follows:
[Mysql] Default-character-set = gbk [Mysqld] Character-set-server = utf8

Restart the service and re-import the data. If garbled characters exist,

In the end, we only need to unify the import and export codes.