Shulou(Shulou.com)06/01 Report--

Preface

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

This is the first time I have encountered this strange situation after working for more than ten years. Let's talk about how to unify the character set into utf8

1, create an empty database

2, export the table structure and data that are utf8, and then import them directly into the new database

3. The data table and data are all latin1 tables. Export the table structure and data first, add-- default-character-set=latin1, change the latin1 to utf8 in the export file, and import it into the new database.

4. The table structure is utf8, and the table data is latin1. There are two ways:

4.1, export the table structure and import it into the new database. Export the data, plus-- default-character-set=utf8, because the table structure is utf8, the table data is latin1, and it is garbled when it is entered into the library. Before importing the data into the new library. This solution has disadvantages-- default-character-set=utf8, the exported data may be lost, and the exported data may report errors during import.

4.2.It can be transferred through the binary character set. This method is recommended.

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

This method saves the trouble of export and import, and the speed is relatively fast, 11w pieces of data, almost 5-6 seconds.

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.