To sum up the problem of MYSQL encoding conversion latin1 to utf8_MySQL

To sum up the problem of MYSQL encoding conversion latin1 to utf8 bitsCN.com

Black noodle

Yesterday I met a PHP website where the database was MYSQL5.1. after The phpmyadmin management of the website, I found that all Chinese characters in the table were garbled. after export, the local import was garbled, you cannot view the required information. garbled characters are generally known as encoding problems. you can check that the target Library is encoded as latin1. it is estimated that the website program is GBK... Direct backup download, download to local need to convert the encoding, but unfortunately did not do it before, search for a bunch of materials online to test... I have summarized these types of information to my friends who need it, so I don't need to search around.

I:

1. back up the database

Mysqldump -- default-character-set = latin1 -- create-options = false -- set-charset = false-u root-p database name> E: ack. SQL

2. create a new database

Create database name character set utf8 COLLATE utf8_general_ci;

3. import data

Mysql-u root-p -- default-character-set = gbk database name <E: ack. SQL

II:

Step 1 run the command: mysqldump -- opt-hlocalhost-uroot-p *** -- default-character-set = lantin1 dbname>/usr/local/dbname. SQL

Step 2 change CHARSET = latin1 of the create table statement in the dbname. SQL file to CHARSET = utf8

Step 3 add a set names utf8 before the insert statement in the dbname. SQL file;

Step 4. set dbname. SQL transcoding is in UTF-8 format, and UltraEditor is recommended, which can be used directly to convert-> ASCII to UTF-8 (Unicode Edit), or to save the file as UTF-8 (without BOM) format

Step 5 run the following command: mysql-hlocalhost-uroot-p *** -- default-character-set = utf8 new_dbname </usr/local/dbname. SQL

Conclusion: This method is fatal when there are a large number of Chinese characters and other special characters in the data, it is likely that in [step 5], an error will be reported, resulting in the failure to import data normally, if the database is large, you can perform the preceding steps for each table.

III:

In order to solve the problem mentioned in the summary in the first method, I had to search for information on the Internet for a day to work out a safer solution.

Step 1: export the table structure of the data table to be exported (you can use Phpmyadmin, mysqldump, and so on. if you don't need to talk about it, change CHARSET = latin1 of the create table statement to CHARSET = utf8, execute the create table statement in the newdbname of the target database to create the table structure, and then start exporting-importing data

Step 2: go to the mysql command line and choose mysql-hlocalhost-uroot-p *** dbname

Step 3: execute SQL select * from tbname into outfile/usr/local/tbname. SQL;

Step 4. set tbname. SQL transcoding is in UTF-8 format, and UltraEditor is recommended, which can be used directly to convert-> ASCII to UTF-8 (Unicode Edit), or to save the file as UTF-8 (without BOM) format

Step 5: execute the statement set character_set_database = utf8 in the mysql command line. note: set the mysql environment variable so that mysql will explain the file content in the form of utf8 when reading the SQL file in the next step.

Step 6 run the load data infile tbname. SQL into table newdbname. tbname statement in the mysql command line;

Note: Do not forget step 4.

In the second method, all data is imported normally, and the format conversion is successful without garbled characters.

IV:

Mysql data conversion code
[Root @ piglet home] # mysqldump-u root-p -- default-character-set = latin1 -- set-charset = utf8 -- skip-opt aiyomama_cn>/home/sites/savemoney. SQL
Enter password:

V:

The following is a latin1 garbled file. Therefore, the local machine also sets up the Mysql5.0 environment.
2. create an empty database named UTF8 and use the SQL file to import the database.
3. use phpmyadmin to export the code to UTF8.
4. replace all default charset = latin1 in the exported SQL file with DEFAULT CHARSET = utf8
5. delete the original latin1-encoded database and import the UTF8-encoded SQL file.
Completed :)

Important points:
Run PHPMYADMIN to open the database.
All the tables and fields in the table are still LATIN1. it doesn't matter. continue to step 1.
After completing Step 1, you can use a text editor to open the SQL file and view it in UTF8 format.
At this time, the table and field content is UTF8!

I have tried all the above five methods. it may be because the database is too large. some methods are not good at all. some methods can be converted to only half of them, and errors can be stopped, finally, I found a small PHP script file for encoding and conversion through unremitting efforts. after using the PHP script file, all the libraries were converted normally. it seems that it was still convenient to use the tool. it was not solved after I had been busy for half a day... However, no matter what the problem is, search engines can usually help you solve the problem, as long as you are willing to spend time solving the problem yourself.

BitsCN.com