MySQL Import and export data Chinese garbled solution summary

in the Inux system

Linux defaults to UTF8 encoding, and Windows is GBK encoded, so the above garbled problem occurs.

Troubleshoot MySQL import and export data garbled problem

The first thing to do is to determine the encoding format of your exported data, you need to add--default-character-set=utf8 when using Mysqldump,

For example, the following code:

Copy CodeThe code is as follows: Mysqldump-uroot-p--default-character-set=utf8 dbname tablename > Bak.sql

You should also use--default-character-set=utf8 when importing data:

Copy CodeThe code is as follows: Mysql-uroot-p--default-character-set=utf8 dbname < Bak.sql

This unified coding solves the MySQL data migration garbled problem


I use Windows as the Export data source and import the MySQL library under the FreeBSD environment

Workaround:

Exporting data

First, MySQL is used as the export database source under the Windows platform. To view the system variables for character encoding:

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

+ ———————— –+ —————————-+

See Character_set_database, this is latin1,latin1 is not loaded with multibyte character sets

Second, set the system variable to UTF8 under Windows

Copy CodeThe code is as follows: Mysql>set Character_set_database=utf8; # #设置默认的字符集为utf8

Third, export data

Copy CodeThe code is as follows:mysql> select * FROM table to outfile ' c:table.txt ' where + condition

At this time I exported some of the data I want, and the TXT file exists in the Table.txt.

Import data

Under the FreeBSD platform

One, the same set of character encoding system variables

Copy CodeThe 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; # #设置默认的字符集为utf8

second, reproduced data

Copy CodeThe code is as follows: Mysql>load data local infile '/home/table.txt ' into table ' table ';

At this point, the conditional data import and export, and processing the garbled situation.
In short, when two MySQL servers import and export, make sure that the character_set_database parameters of the two servers are the same, so as to prevent some garbled situation. Of course we can also use other character sets, such as GBK, to make adjustments. It operates as appropriate


Workaround three

Garbled resolution method
After importing the data, look at the command line to find the Chinese garbled
Use the following command to view the system character set

Copy CodeThe code is as follows: Show variables like ' char% ';

If it is not GBK or UTF8, stop the MySQL service and then modify the My.ini file to the installation directory,
Change the following variables in the file to the following, and if not, add the variables

Copy CodeThe code is as follows:
[MySQL]
Default-character-set=gbk

[Mysqld]
Character-set-server=utf8

Restart the service, re-import the data, if it is still garbled,

At the end of the experience, you just have to unify the import and export code.

MySQL Import and export data Chinese garbled solution summary