Actual combat MySQL export Chinese garbled and phpmyadmin to import Chinese garbled solution _php skills

Always do not use this phpmyadmin, in this machine is also used navicat, the total sense of phpmyadmin speed is slow. This time, there is no independent host, had to use someone else to the phpmyadmin.

The first step: Local data export SQL files. I thought it was a trivial matter for navicat. Right-click on the database to "Dump SQL" (Figure 1), Hua Hua, more than 10 seconds of time to export success.

( figure 1:navicat the entire database to SQL)

With Notepad open a look, dumbfounded. Chinese are all garbled. What happened? Search for a bit, change what connection attributes what. No use. Try to dump SQL on a single sheet, hey, Chinese is normal. But 82 watches, I dump each one I'm not exhausted. No way. It seems that I can only discard my beloved navicat. Think of a mysqldump, so try it. Run-c:\documents and settings\administrator>mysqldump-uroot-p123 Ttg>ttgbk2.sql. Open a look, or garbled. Not yet. Alas.. Search, change to the following plus the specified character set

C:\Documents and settings\administrator>mysqldump-uroot-p123--DEFAULT-CHARACTER-SET=GBK ttg>ttgbk2.sql. Open the look. Hey, it's okay.

Step Two: Open the phpMyAdmin provided by the virtual host. Import selection file ttgbk2.sql. Dot execution. That speed, alas ... An error was made in a moment. An access denied error occurred while performing lock tables TableName write, so I am a virtual host user with no permissions to lock tables. Open SQL One look at the lock tables option. Without permission, that's not necessary. To a search on the Internet to add--skip-lock-tables, thought good, it should be this "skip the lock table"

When you add the-skip-lock-tables option to the mysqldump, the command line becomes
C:\Documents and settings\administrator>mysqldump-uroot-p123--DEFAULT-CHARACTER-SET=GBK--skip-lock-tables TTG >ttgbk3.sql.
The result was disappointing, and there was lock tables.
Then I took a look at mysqldump--help
Just understand--skip-lock-tables is used in the backup time does not allow reading and writing. But if you do not want to export the band lock-tables (because you do not have permission to import, hehe) should be the use of Add-locks=false, which is 2 concepts. The following are correct
C:\Documents and settings\administrator>mysqldump-uroot-p123--DEFAULT-CHARACTER-SET=GBK TTG--add-locks=false >ttgttg3.sql.

My version of the exported in Notepad opens in ASNI format.

Import again at phpMyAdmin. The result is an error after importing 3 tables. The MySQL statement complains. A look at Chinese also garbled ..... Close to collapse.

and find out why. Change the "MySQL connection proofing" to gbk-chinese-ci, language to Chinese-chinese simplified (Figure 2). Then change the import "file encoding" to "GBK" (the default is Utf-8, of course, the corresponding SQL file encoding with Notepad to open is ANSI.) (figure III). Try again ....

( figure II: Modifying connection proofing and language)

( Figure Three: Modify the file's character set to GBK)

Finally, all tables were imported successfully. Open a table containing Chinese, the field appears normal.

2 Points of experience:

1, the database code to the database code. Ensure connection proofing is consistent with database coding.

2, the SQL file encoding to file encoding. Ensure that the file encoding chosen for the import is consistent with the encoding used for the database.

This is a 2 coding problem.

Took you to MySQL. From knowing that you have this coding problem until now, you still look like this. This problem is still confusing to many people. It's good to be internationalized like SQL Server.