Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to correctly replace the MySQL database character set, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

As a senior DBA programmer, will you encounter more such situations at work?

Due to the lack of early planning, the character set of the original database can not meet the needs of the business with the development of the business. For example, the original business system uses the utf8 character set, and later there is a need to store emoticons, the uft8 character set can not meet the business needs at this time. The utf8mb4 character set is required.

Database migration, the character sets of the source and target databases are inconsistent and need to be converted before migration.

When changing the database character set, it is obvious that the jian is true (dan), and there will always be a variety of problems, resulting in garbled data in the database after the change!

Today, the editor will sort out with you how to correctly change the character set of the database. The following article will briefly explain the conversion process of different character sets of the database. Step transformation, put an end to garbled code!

Common character set

GBK is a standard compatible with GB2312 after expansion based on the national standard GB2312. The text coding of GBK is represented by double bytes, that is, both Chinese and English characters are represented by double bytes. In order to distinguish between Chinese characters, the * * bit is set to 1. GBK, which contains all Chinese characters, is a national code, and its versatility is worse than UTF8, but UTF8 occupies a larger database than GBK. Support simplified Chinese and traditional Chinese.

Utf8 character set: a UTF-8-encoded Unicode character set that takes up 1 to 3 bytes per character. UTF-8 contains characters needed by all countries in the world. It is an international code with strong versatility.

Utf8mb4 character set: a UTF-8-encoded Unicode character set that takes up 1 to 4 bytes per character. Characters and supplementary characters within the BMP range can be overridden. The character encoding in the BMP range is exactly the same as that in the utf8 character set, and the length is exactly the same, so the utf8mb4 character set is compatible with the utf8 character set.

GB2312 is a subset of GBK and GBK is a subset of GB18030.

Big5 supports traditional Chinese

Transformation process

The following simulates the process of changing the database of the latin1 character set to GBK. Other character sets have a similar conversion process. It is important to note that the target character set to be converted must be the super of the source character set or the range of the target character set contains the range of the source character set.

1. Guide table structure

Mysqldump-uroot-p-- default-character-set=gbk-d databasename > createtb.sql

Where-default-character-set=gbk indicates what character set to connect with, and-d indicates that only the table structure is exported and no data is exported.

two。 Manually modify the character set in the table structure definition in createtb.sql to the new character set.

3. Make sure the records are no longer updated and export all records

Mysqldump-root-p-quick-no-create-info-extended-insert-default-character-set=latin1 databasename > data.sql

Quick: this option is used to dump large tables. It forces mysqldump to retrieve rows instead of all rows in the table from the server one at a time and buffer it into memory before outputting it.

Extended-insert: use a multiline insert syntax that includes several values lists. This makes the dump file smaller and can be inserted faster when the file is reloaded.

No-create-info: create table statements for each dump table are not exported.

Default-character-set=latin1: export all data according to the original character set. In the file exported in this way, all Chinese characters are visible and will not be saved as garbled.

4. Open data.sql and change set names latin1 to set names bgk.

5. Create a new database with a new character set.

Create database databasename default charset bgk

6. Create tables and execute createtab.sql

Mysql-root-p databasename

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.