About MySQL backup Character Set

1 Guide

Selecting character sets during MySQL backup is a problem, especially for businesses with variable character sets. Utf8 is used by default for mysqldump, and utf8 is also recommended officially. However, for Chinese characters, a considerable part of gbk encoding characters do not have the corresponding unicode encoding. That is to say, using utf8 backup will cause data loss. Is there a solution?

Of course, the most direct method is to add the encoding ing. However, this part of the number of character sets is not a minority, and, even worse, it seems that the authoritative ing standards of this part of the character set are not found. So, are there other methods?

In fact, if binary is used for backup, there will be no character set conversion process and the above problems will not exist. So, does binary solve all gbk problems? The answer is NO.

2 binary Problems

Before talking about binary. Two problems need to be clarified. For MySQL backup, there are two parts: schema information and actual data. All Schema information is UTF-8 encoded, except for default values. This is the source of the problem.

 

2.1 utf8 backup

(1) The file. frm stores the schema information of the table and stores the default values of each field through an actual record. The Schema information (including comment) is stored using utf8, but the default value is stored using the character set specified by table.

(2) When the show create table statement is executed, mysqld converts the default value in frm from the table encoding to utf8 encoding.

(3) When mysqld executes the create table statement, the default value is converted from utf8 to the character set specified by table.

 

2.2 binary backup

If binary is specified for backup. During the import, although character_set_client is specified as utf8 before the table is created, collation_connection is still binary. Therefore, when the default value is stored, the conversion from utf8 to the character set specified by table is not performed. If the table is gbk encoded, the import will inevitably fail.

Example:

Create table 't1 '( 'Inetbarid' int (11) not null default '0 ', 'Iuin' bigint (20) not null default '0 ', 'Vnetbarname' varchar (80) not null default '"-"', Primary key ('inetbarid ') ) ENGINE = InnoDB default charset = gbk;   Insert into t1 values (, 'xxxxx ');

As you can see, a 1067 Invalid default value error occurs when the table is exported normally.

3. Solution

Before executing the create table statement, add the character_set_connection setting for mysqldump.

/*! 40101 SET character_set_connection = utf8 */

This is also a MySQL bug. Since the schema information uses utf8 from start to end, you should set the connected character set variables to utf8 before executing create table, instead of setting only the character set variables of the client.