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:Copy codeThe Code is as follows: mysqldump-uroot-p -- default-character-set = utf8 dbname tablename> bak. SQL
When importing data, use -- default-character-set = utf8:
Copy codeThe 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:
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: 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
Copy codeThe Code is as follows: mysql> set character_set_database = utf8; # set the default character set to utf8
Iii. Export data
Copy codeThe 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
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; # set the default character set to utf8
Ii. Reprinting data
Copy codeThe 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
Copy codeThe 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.
Copy codeThe 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.