MySQL export Chinese Garbled text and phpMyAdmin import Chinese Garbled text

The service provider only provides phpMyAdmin for you to import and export the local GBK-encoded MySQL database to the VM. PhpMyAdmin is never used, and navicat is also used on the local machine. phpMyAdmin is always slow. This is not the case. If there is no independent host, you have to use phpMyAdmin.

Step 1: Export SQL files from local data. I thought this was a breeze for navicat. Right-click the database and choose "Dump SQL" (1). the export is successful in over 10 seconds.

(Figure 1: converting the entire database to SQL under navicat)

I opened it in notepad, And it was dumpfounded. Chinese characters are all garbled characters. What's going on? After searching, I changed the connection attributes. No matter. Try to dump the SQL statement on a single table. Hey, Chinese is normal. But for 82 tables, I am not exhausted by dumping them one by one. No. It seems that I can only discard my beloved navicat. Think of a mysqldump. Try it. Run-c: \ Documents and Settings \ Administrator> mysqldump-uroot-p123 TTG> ttgbk2. SQL. Check whether it is garbled. Not yet. Alas .. Search, change to the following with the specified Character Set

C: \ Documents and Settings \ Administrator> mysqldump-uroot-p123 -- default-character-set = gbk ttg> ttgbk2. SQL. Open it. Hey, you can.

 

Step 2: Open phpMyAdmin provided by the VM. Import the selected ttgbk2. SQL file and click execute. That speed, alas... An error is reported later. The Access Denied error occurs when you execute lock tables tablename write. Originally, I am a VM user and do not have the permission to lock tables. The lock tables option is available when you open the SQL statement. If you do not have the permission, you do not need to use this. I searched the internet and added -- skip-lock-tables. I thought it was good. It should be the "Skip lock table ".

When the-Skip-lock-tables option is added to mysqldump, the command line becomes
C: \ Documents and Settings \ Administrator> mysqldump-uroot-p123 -- default-character-set = GBK -- skip-lock-tables TTG> ttgbk3. SQL.
The results are disappointing, but there is still lock tables.
Next, let's take a look at mysqldump -- help.
Only then can we understand that -- skip-lock-tables is used to prevent reading and writing during Backup. However, if you do not want to export a lock-tables file (because you do not have the permission during import, huh, huh), you should use add-locks = false, which is two concepts. Correct:
C: \ Documents and Settings \ Administrator> mysqldump-uroot-p123 -- default-character-set = gbk ttg -- add-locks = false> ttgttg3. SQL.

My versions are exported in asni format in notepad.

Import it again at phpMyAdmin. The result is an error after three tables are imported. MySQL statement error. Chinese characters are garbled ..... Close to crash.

Find the reason. Change "MySQL connection Verification" to GBK-Chinese-CI, and change "language" to Chinese-Chinese Simplified (2 ). Change "file encoding" during import to "GBK" (the default value is UTF-8. Of course, the corresponding SQL file encoding is opened in notepad by ANSI .) (3 ). try again ....

 

 

(Figure 2: Modify connection verification and language)

 

(Figure 3: Modify the character set of a file to GBK)

 

Finally, all tables are successfully imported. Open a table containing Chinese characters, and the field is displayed normally.

2 experiences:

1. Database encoding: Database encoding. Ensure that the connection verification is consistent with the database encoding.

2. the SQL file encoding is file encoding. Ensure that the file encoding selected during import is the best as the encoding used by the database.

This is Two Encoding Problems.

I got your MySQL server. from knowing that you have this encoding problem till now, you still look like this. This problem is still confusing to many people. When will it be as international as SQL server.