Two methods to convert Mysql Data Encoding-Latin1 to utf8

Background: The dnname of a MySQL database in a system adopts the default Latin1 character set. During system upgrade, all data must be converted to UTF-8 format and the target database is newdbname (utf8 is used for database creation)

Method 1:

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

Step 2Change charset = Latin1 of the create table statement in the dbname. SQL file to charset = utf8

Step 3Add 'set names utf8; 'before the insert statement in the dbname. SQL file ;'

Step 4Set dbname. SQL transcoding is in UTF-8 format and we recommend that you use ultraeditor to either directly use the 'convert-> ASCII to UTF-8 (UNICODE edit)' of the editor or save the file as a UTF-8 (without BOM) Format

Step 5Command Line execution: 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.

Method 2 (recommended ):

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 1Export the table structure of the data table to be exported (you can use phpMyAdmin, mysqldump, and so on. It is easy not to mention), and then change charset = Latin1 of the exported 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 2Command Line: Enter the MySQL command line, MySQL-hlocalhost-uroot-P *** dbname

Step 3Execute SQL select * From tbname into OUTFILE '/usr/local/tbname. SQL ';

Step 4Set tbname. SQL transcoding is in UTF-8 format and we recommend that you use ultraeditor to either directly use the 'convert-> ASCII to UTF-8 (UNICODE edit)' of the editor or save the file as a UTF-8 (without BOM) Format

Step 5Run the set character_set_database = utf8 statement 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 6Execute the statement load data infile 'tbname. SQL 'In the MySQL command line to table newdbname. tbname;

Note: Do not forget step 4.

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

The younger brother was original for the first time. I 'd like to advise you more about the platform, ^. ^