In the database, some tables and data are latin 1, some tables and data are utf8, and some tables have UTF8 table structure and latin 1 data.
This wonderful situation, I have been working for more than ten years, is the first time I have encountered it. Let's talk about how to unify the character set into utf8.
1, create an empty database.
2. Export the table whose structure and data are utf8, and then directly import it into the new database.
3. Datasheets and data are tables of latin 1. First, export the table structure and data, add-default-character-set = Latin 1 to the export file, change Latin1to utf8, and then import it into the new database.
4. The table structure is utf8, and the table data is latin 1. There are two ways:
4. 1, export the table structure first and import the new database. Export data, plus-default-character-set = utf8-set = utf8, because the table structure is utf8 and the table data is latin 1, which has been garbled when it is put into storage. Now import the data into the new library. This scheme has some disadvantages, such as-default-character-set = utf8-set = utf8, the exported data may be lost, and an error may be reported when importing.
4.2, you can use the binary character set for transit, which is recommended.
Update user set user _ name = convert (convert (user _ name uses latin 1) uses binary) uses utf8);
This method saves the trouble of exporting and importing, and the speed is relatively fast, 1 1w data, almost 5-6 seconds.
Database design is very important, so you must consider it clearly when designing the database. If the foundation is not firm, the building will collapse sooner or later.
Articles you may be interested in: Analysis of the problem of mysql character set causing errors in restoring database. The method of mysql modifying database coding (database character set) and character coding of tables. Methods of modifying mysql character set and database engine. When sharing mysql database in DW, the solution to the garbled Chinese characters in the established character set.