How to unify the character set on an existing mysql database

Preface

In the database, some data tables and data are in latin1, some data tables and data are in UTF8, and some tables have utf8 structure and latin1 data.

This is the first time I have encountered such a strange situation in my more than ten years of work. Let's talk about how to unify the character set into utf8

1. Create an empty database

2. Export the table structure and data in utf8 format, and then import it directly into the new database

3. The data table and data are both in latin1. First export the table structure and data, add --default-character-set=latin1, change latin1 to utf8 in the exported file, and then import it into the new database.

4. If the table structure is utf8 and the table data is latin1, there are two methods:

4.1, first export the table structure and import it into the new database. When exporting data, add --default-character-set=utf8, because the table structure is utf8 and the table data is latin1, which is garbled when it is stored in the database. Import the data into the new library. This solution has disadvantages. If --default-character-set=utf8 is used, the exported data may be lost, and the exported data may give an error when imported.

4.2, you can use binary character set for transfer, this method is recommended

UPDATE user SET user_name = CONVERT(CONVERT(CONVERT(user_name USING latin1) USING binary) USING UTF8);

This method saves the trouble of exporting and importing, and is relatively fast. It takes about 5-6 seconds to complete 110,000 records.

Database design is very important and must be considered carefully when designing the database. If the foundation is not solid, the building will collapse sooner or later.