In Linux systems
Linux defaults to the UTF8 encoding, and Windows is GBK encoding, so there will be the above garbled problem.
Solve MySQL import and export data garbled problem
The first thing to do is to determine the encoding format of the data that you are exporting, and the need to add--default-character-set=utf8 when using Mysqldump,
For example, the following code:
The code is as follows |
Copy Code |
Mysqldump-uroot-p--default-character-set=utf8 dbname tablename > Bak.sql |
Then you should also use--default-character-set=utf8 when importing data:
The code is as follows |
Copy Code |
Mysql-uroot-p--default-character-set=utf8 dbname < Bak.sql |
This unified coding solves the problem of the garbled data migration in MySQL.
I use Windows as the Export data source and import the MySQL library in the FreeBSD environment
Workaround:
Export data
First, MySQL is used as the export database source under Windows platform. To view the system variables for character encoding:
The code is as follows |
Copy Code |
The code is as follows |
Copy Code |
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
The code is as follows |
Copy Code |
Mysql>set Character_set_database=utf8; # #设置默认的字符集为utf8 |
III. Export of data
The code is as follows |
Copy Code |
Mysql> select * FROM table into outfile ' c:table.txt ' where + condition |
Then I want to export some of the data, and TXT file exists in the Table.txt.
Import data
Under the FreeBSD platform
A system variable that also sets the character encoding
The code is as follows |
Copy Code |
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
The code is as follows |
Copy Code |
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. As the case has been operated
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
The code is as follows |
Copy Code |
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
The code is as follows |
Copy Code |
[MySQL] Default-character-set=gbk [Mysqld] Character-set-server=utf8 |
Restart the service, re-import the data, if it's garbled,
At the end of the experience, as long as the import and export code unified to be no problem.