MySQL Import Export Data garbled solution

In the MySQL import Export data often appear the problem of Chinese garbled, mostly because the class import export encoding settings inconsistent. This paper introduces the method of coding conversion under different platforms for reference.

In Linux, the default is UTF8 encoding, and Windows is GBK encoding, and if you import data without a specified encoding between these two systems, garbled code will appear.

First, determine the encoding format of the exported data, and use mysqldump to add--default-character-set=utf8,

For example:

Mysqldump-uroot-p--default-character-set=utf8 dbname tablename > Bak.sql

You also use--default-character-set=utf8 when you import data:

Mysql-uroot-p--default-character-set=utf8 dbname < Bak.sql

After unified coding, the garbled problem of MySQL data migration is solved.

Use Windows as an export data source, and import the MySQL library under the FreeBSD environment, the solution is detailed.

Export data

First, in the Windows platform MySQL is used as an export database source.

To view the system variables for character encoding:

code example:

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 multibyte character set.

Second, set the system variable to UTF8 under Windows

Mysql>set Character_set_database=utf8; # #设置默认的字符集为utf8

III. Export of data

Mysql> select * FROM table into outfile ' c:table.txt ' where + condition

At this point, some data are exported, and the TXT file exists in Table.txt.

Import data

Under the FreeBSD platform

A system variable that also sets the character encoding

code example:

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

Ii. reprint of data

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

At this point, the conditional data import and export, and processing of garbled cases.

In short, two MySQL server import export, be sure to ensure 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 adjust.

Workaround Three

Garbled resolution method

After importing data, view found in Chinese garbled at command line

Use the following command to view the system character set

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 variable in the file to the following, and add these variables if you do not have the variable

[MySQL]

Default-character-set=gbk

[Mysqld]

Character-set-server=utf8

Restart the service, re-import the data, if also garbled, as long as the import and export coding unified to solve the problem.