浅谈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的字符集变量。
以上就是 浅谈MySQL备份字符集的问题的内容,更多相关内容请关注PHP中文网(www.php.cn)!