Perfect conversion of Mysql's character set resolve to view the garbled problem in the UTF8 source file _mysql

Source: Internet
Author: User
Tags file copy mysql in mysql version
MySQL started with the 4.1 version of the concept of character set, so for MySQL4.0 and the following version, their character set is Latin1, so sometimes need to do a bit of MySQL's character set for conversion, MySQL version of the upgrade, downgrade, especially to upgrade the version of MySQL , in order not to let the program continue to follow the Latin1 character set after discuz! The impact of version upgrades and the installation of Supesite, which requires us to do character set conversion!
I have converted a lot of data, but also used a lot of methods, personal feeling the best use is to use the MySQL command to export the character set to convert the past!
Now I will show you how to convert the MySQL character set, because the character set is more, now the main lifting GBK and Latin1 between the two characters of the conversion!
First I will explain how to convert MySQL from GBK to Latin1 (if it is UTF8 or BIG5 conversion will be GBK replaced by the corresponding character set)! This often happens when MySQL converts from MySQL4.1 and above versions to

MySQL4.1 the following version of the process! Some people figure convenient, especially host permissions, directly to the MySQL data directory under the file copy passed, so the problem is garbled problem! Of course, all I'm saying is asking you to

Must have the host authority, the virtual host user may build the forum in the local, downloads the data to your local, then imports the data to your local database, then carries on the character set conversion!
The current step is to export the MySQL data, at the MySQL command prompt (Linux system for mysql command directory), enter the following export command:
Mysqldump-uroot-p--default-character-set=gbk--set-charset=latin1--compatible=mysql40--skip-opt discuz> Discuz.sql
As shown in Figure 1 (under Windows) and Figure 2 (under Linux)



This is easy to export data from the MySQL database, at this time the exported file in the same directory as the MySQL command, that is, the bin directory, and has been exported to the MySQL character set for the Latin1! Now, the Data guide

Go into the MySQL4.0!
Also at the 4.0 version of the MySQL command prompt (Linux system for mysql command directory), enter the following import command:
Mysql-uroot-p--default-character-set=latin1-f Discuz<discuz.sql
As shown in Figure 3 (under window) and Figure 4 (under Linux)



This will be the character set from GBK to Latin1 at the same time MySQL from more than 4.1 version of the conversion to MySQL4.0!
Now explain how to convert from the Latin1 character set to GBK!
Since only the MySQL4.1 version has the character set concept, if you want to convert the MySQL4.0 character set from Latin1 to the GBK character set, you must import it into the version above MySQL4.1 and then export it! There's more here.

A step to export as a Latin1!
Also at the MySQL4.0 command prompt, enter the following export command:
Mysqldump-uroot-p--default-character-set=latin1--set-charset=latin1--skip-opt discuz>discuz.sql
Then import into MySQL4.1, and enter the following imported command:
Mysql-uroot-p--default-character-set=latin1-f Discuz<discuz.sql
Then convert the character set under MySQL4.1, and enter the following export and import commands:
Mysqldump-uroot-p--default-character-set=latin1--SET-CHARSET=GBK--skip-opt discuz>discuz.sql
Mysql-uroot-p--default-character-set=gbk-f Newdiscuz<discuz.sql
If there is no problem, then the conversion is over! In fact, the whole process is relatively simple, but the process looks more troublesome, but the process is very simple and clear!
Here is a very easy to come up with a problem in the import process!
In the import when the unknown command '/' Such a mistake, in fact, this is due to MySQL in the export, it sometimes to some traditional characters behind the automatic addition of a backslash caused! The solution to this problem is to open

Backup SQL (here for discuz.sql), prompted when the error will be prompted specifically which line has been wrong, to find the line, the backslash to remove the line! In fact, you can use ultraedit these text processing tools, batch to deal with these backslashes

With personal experience, there are a lot of user names to take the more strange, so easy to appear backslashes, so long as correct those user name on the line!
The above is a personal experience in the process of converting data, a lot of places have yet to be improved!
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.