浅谈MySQL备份字符集的问题

1 引子

MySQL备份时选择字符集是一个难题，特别是字符集不定的业务。mysqldump默认使用utf8，而官方也推荐使用utf8。但实际上，对于中文，部分相当一部分gbk编码字符没有对应的unicode编码，也就是说这部分字符集使用utf8备份会导致数据丢失。那么有没有解决方法呢？

当然，最直接的方法是将这部分编码的映射加上。但是，这部分的字符集数量并不是少数，而且，更蛋疼的是，似乎找不到这部分字符集权威的映射标准。那么，还有其它方法吗？

实际上，如果使用binary进行备份，就不会存在字符集的转换过程，也就不会存在上述问题。那么，使用binary是否就解决了gbk所有的问题呢？答案是NO。

2 binary的问题

在讲binary的问题之前。需要弄清2个问题。对于MySQL备份，分两部分：schema信息和实际数据。而Schema信息一律使用utf8编码，但是，default value除外。这正是问题的来源。

 

2.1 utf8备份

(1)文件.frm会存储table的schema信息，并通过一个实际的记录来存储各个field的默认值。Schema对应的信息(包括comment)使用utf8存储，但是default value使用table指定的字符集进行存储。

(2)当执行show create table语句时，mysqld会将frm中的默认值从table指定的编码转成utf8编码。

(3)当mysqld执行create table语句，会将default value从utf8转成table指定的字符集。

 

2.2 binary备份

如果指定binary进行备份。在导入时，在创建table之前，虽然将character_set_client指定为utf8，但collation_connection还是binary。所以，存储默认值时不会进行utf8到table指定的字符集的转换。如果table指定为gbk编码，导入必然失败。

示例：

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(1,1,'xxxx');

可以看到，正常导出的表，导入却出现1067 Invalid default value的错误。

3 解决方法

mysqldump时，在执行create table语句之前，增加对character_set_connection 的设置。

/*!40101 SET character_set_connection = utf8 */

这也算是MySQL一个bug，既然schema信息从头到尾都使用utf8，在执行create table之前，就应该将连接的字符集变量设置成utf8，而不是只设置client的字符集变量。

 

作者：YY哥
出处：http://www.cnblogs.com/hustcat/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。