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 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 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 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.
Method 2:
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, 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 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 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 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 on 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.