mysql import and export data Chinese messy code solution summary

linux system

By default, linux encodes utf8, while windows encodes gbk, so the above garble problem will occur.

Solve the data import and export problem of mysql

The first thing you need to do is to determine the encoding format of the data that you're going to export. When you're using mysqldump, you need to add -- default-character-set =utf8,

For example, the following code:

mysqldump -uroot -p --default-character-set=utf8 dbname tablename > bak.sql

Then import data using -- default-character-set =utf8:

mysql -uroot -p --default-character-set=utf8 dbname < bak.sql

This unified 1 encoding solves the problem of scrambled code in mysql data migration


I used windows as the export data source and imported the mysql library in the freebsd environment

Solutions:

Export data

1. mysql is first used as the export database source on the windows platform. View system variables for character encoding:

 
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:mysqlsharecharsets | 

+ -- -- -- -- � + -- -- -- -- - -+ 

Look at character_set_database, latin1, latin1 doesn't fit into the multi-byte character set

2. Set the system variable to utf8 under windows

mysql>set character_set_database=utf8; ## Set the default character set to utf8

3. Export data

mysql> select * from table into outfile  ' c:table.txt' where + conditions 

At this point, I exported some of the data I wanted and saved it in table.txt as txt.

Import data

Under the freebsd platform

1. Set the system variables of the character encoding as well

 
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 

2. Reprint data

 mysql>load data local infile  ' /home/table.txt' into table `table`; 

So far, the condition of data import and export, and deal with the situation of messy code.
In conclusion, when importing and exporting two mysql servers, 1 must make sure that the parameters of character_set_database are the same on both servers, so as to prevent the situation of some scrambled codes. Of course, we can also use other character sets, such as gbk, to adjust. It depends


Solution 3

Garbled code solution
After importing data, the command line view found Chinese garbled code
Use the following command to view the system character set

show variables like 'char%';

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

 
[mysql] 
default-character-set=gbk 

[mysqld] 
character-set-server=utf8 

Restart the service, re-import the data, and if it's still garbled,

Finally, the rule of thumb is to combine the import and export codes under 11.