Solve the Problem of garbled characters exported from Linux database to Windows

Some time ago, when the company's server migrated the platform, it was necessary to export the MySQL database under Linux to a Windows server. Naturally, the mysqldump command is used to export the SQL file, and then the MySQL import operation is performed in Windows. After the import is complete, check the number of tables and so on, so it is natural that the import is completed smoothly. What is the result?

Some time ago, when the company's server migrated the platform, it was necessary to export the MySQL database under Linux to a Windows server. Naturally, the mysqldump command is used to export the SQL file, and then the MySQL import operation is performed in Windows. After the import is complete, check the number of tables and so on, so it is natural that the import is completed smoothly. What is the result?

Some time ago, when the company's server migrated the platform, it was necessary to export the MySQL database under Linux to a Windows server. Naturally, the mysqldump command is used to export the SQL file, and then the MySQL import operation is performed in Windows. After the import is complete, check the number of tables and so on, so it is natural that the import is completed smoothly.

The result shows that garbled characters are detected later. After multiple mysqldump export attempts, the data cannot be imported. As a result, this is only caused by encoding problems.

Linux installation of MySQL, the default language is latin1; and Windows MySQL in the graphical interface installation process has set the default encoding for the UTF-8. It seems that this is the case. The encoding problem has killed people and people are often confused.

So I searched for a circle on the Internet and solved the problem easily. The steps for recording the solution are as follows:

1. Export data using the default MySQL encoding in Linux:

# Mysqldump-u USERNAME-pPASSWORD-default-character-set = latin1 [DBname]> [filepath/filename. SQL]

Here, USERNAME is the database user name, generally root. PASSWORD is the PASSWORD, or you can leave it blank, with only the-p Mark. The most critical part is "-default-character-set = latin1", which must be consistent with the encoding settings in Linux. Then DBName is the database name, followed by the exported file path.

2. In Windows, open the exported SQL file in a text editor supporting multi-encoding formats such as NotePad ++ and UltraEdit to see if there are any garbled characters. If no garbled characters exist, perform the following operations.

Find:

Create table 'table '(
... ...
) ENGINE = MyISAM default charset =Latin1AUTO_INCREMENT = 1;

Change it:

Create table 'table '(
... ...
) ENGINE = MyISAM default charset =Utf8AUTO_INCREMENT = 1;

3. Save the modified file in UTF8 format. Do not add BOM information so that you can get an SQL UTF8 document!

4. Run the MySQL import command:

Mysql-u USERNAME-pPASSWORD [DBname] <[filepath/filename. SQL]

5. Use phpMyAdmin to check whether the problem is correct!

Reference: http://blog.roga.tw/2008/11/05/1127

Original article address: solves the problem of garbled characters exported from the Linux database to Windows. Thank you for sharing it with the original author.