How to unify the character set on an existing mysql database

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.

You may also be interested in:
  • MySQL character set viewing and modification tutorial
  • How to modify the MySQL character set
  • Causes and solutions to the garbled character set problem in MySQL database
  • How to change MySQL character set utf8 to utf8mb4
  • MySQL character set garbled characters and solutions
  • Detailed explanation of JDBC's processing of Mysql utf8mb4 character set
  • Solution to index failure in MySQL due to different field character sets
  • How to change the default character set of MySQL to utf8 on MAC
  • How to set the character set for mysql under Docker
  • How to solve the problem of MySQL query character set mismatch
  • Detailed explanation of character sets and validation rules in MySQL

<<:  The pitfall record of the rubber rebound effect of iOS WeChat H5 page

>>:  Detailed example of deploying Nginx+Apache dynamic and static separation

Recommend

Quickly solve the problem that CentOS cannot access the Internet in VMware

Yesterday I installed CentOS7 under VMware. I wan...

Application examples of WeChat applet virtual list

Table of contents Preface What is a virtual list?...

JavaScript static scope and dynamic scope explained with examples

Table of contents Preface Static scope vs. dynami...

React realizes secondary linkage effect (staircase effect)

This article shares the specific code of React to...

Implementation of CSS circular hollowing (coupon background image)

This article mainly introduces CSS circular hollo...

How to set password for mysql version 5.6 on mac

MySQL can be set when it is installed, but it see...

Detailed explanation of triangle drawing and clever application examples in CSS

lead Some common triangles on web pages can be dr...

Solution to the blank page after vue.js packaged project

I believe that many partners who have just come i...

A performance bug about MySQL partition tables

Table of contents 2. Stack analysis using pt-pmap...

MySQL Series 8 MySQL Server Variables

Tutorial Series MySQL series: Basic concepts of M...

Detailed explanation of CSS float property

1. What is floating? Floating, as the name sugges...

Detailed tutorial on installing mysql-8.0.20 under Linux

** Install mysql-8.0.20 under Linux ** Environmen...