How to unify character set on existing database by MySQL

Time:2021-5-12

preface

In the database, part of the data tables and data are Latin1, part of the data tables and data are utf8, and part of the tables, the table structure is utf8 and the data is Latin1.

It’s the first time that I’ve been 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 whose structure and data are utf8, and then import it into the new database directly

3. The data table and data are all Latin1 tables. First export the table structure and data, add — default character set = Latin1, change 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

4.1, first export the table structure and import it into a new database. Export data, add — default character set = utf8, because the table structure is utf8, and the table data is Latin1, which is garbled when entering the database. Import the data into the new library. This scheme has disadvantages, – default character set = utf8, the exported data may be lost, and the exported data may report an error when importing.

4.2, you can use binary character set to 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 export and import, and the speed is relatively fast, 11W pieces of data, about 5-6 seconds

Database design is very important, we must consider it clearly when designing database. If the foundation is not firm, the building will fall down sooner or later.

Recommended Today

Looking for frustration 1.0

I believe you have a basic understanding of trust in yesterday’s article. Today we will give a complete introduction to trust. Why choose rust It’s a language that gives everyone the ability to build reliable and efficient software. You can’t write unsafe code here (unsafe block is not in the scope of discussion). Most of […]