mysql 不同字符集的数据 转换_(转)MySQL多字节字符集造成主从数据不一致问题

问题产生线上一直有个历史遗留问题，最近DBA提了出来，所以跟了下代码，作了下简单分析，问题描述如下：在master-slave的环境下，对master上的某个表中的数据插入，会导致master-slave数据不一致的情况，通过反复试验，确定出现该情况的条件如下：

master上设置了character_set_server=gbk

应用中采用了prepared statement并且设置了useCursorFetch=true

对于master上某个表中的int字段，采用了字符的形式进行插入

比如，master上有张表，结构如下：

create table t(idintauto_increment primay key,countint)engine=innodb;

进行的操作如下：

conn=DriverManager.getConnection("jdbc:mysql://192.168.0.1:3307/test?useCursorFetch=true&user=root");

pstmt=conn.prepareStatement("insert into test(count) values(?)");

pstmt.setString(1,"1");

pstmt.execute();

通过上面的操作，我们发现，在master上，插入后的结果如下：

mysql>select*fromt;

+----+-------+

|id|count|

+----+-------+

|1|1|

+----+-------+

而在slave上，却变成了另外一个结果：

mysql>select*fromt;

+----+-------+

|id|count|

+----+-------+

|1|49|

+----+-------+

从上面的信息可以看出，master-slave上的数据出现了不一致，查看master上的binlog，我们会发现如下信息：

insertintot(count)values(0x31)

这里的binlog中的值'1'被转化成了16进制0x31。

问题分析binlog在某种情况下会被转化成16进制存储，这个可能很多人没有注意，转化成16进制有又造成了master-slave上数据不一致，这个就让人比较难以接受了，以下我们的分析就从这两方面入手：

MySQL为什么要把Binlog转化成16进制，在那些条件下会转成16进制？

转化成16进制后，数据为什么会出现不一致？

首先，我们来分析第一个问题，通过上面的条件进行跟踪测试，我们很快发现，MySQL把Binlog中的字符串转化成16进制存储的条件有两个：

客户端使用了prepared statement

客户端传过来的编码是多字节符集编码

客户端在使用prepared statement的时候，在执行前，需要先解析出内部给定的参数，由于涉及到转义的问题，需要把参数内部的如\0，\n等字符进行相应的替换(\0会被替换成'\\0')，而对于多字节字符集编码的字符串(如：gbk，gb2312)，转义字符'\'可能出现在字符的第二个字节，例如字符串“?\0”，可能实际的编码是两个字符'?\'和‘0’,如果按照替换的原则替换，很有可能会破坏原有的字符串的内容，所以MySQL对于这种转义符'\'可能出现在第二个字节的字符集，都给了一个标识进行说明(escape_with_backslash_is_dangerous=1)，在处理时，遇到这种字符集，直接把字符串转成16进制进行处理，这样就可以避免转义出现的问题。

具体的代码可以查看log_event.cc文件中的append_query_string函数，关键代码如下：

if(csinfo->escape_with_backslash_is_dangerous)

ptr=str_to_hex(ptr,from->ptr(),from->length());

else

{

*ptr++='\'';

ptr+=escape_string_for_mysql(csinfo,ptr,0,from->ptr(),from->length());

*ptr++='\'';

}

整个字符串转成16进制的过程在Prepared statement处理时进行，所以在没有使用prepared statement或者客户端字符集不会出现转义符'\'出现在第二字节的情况下，不会产生该问题。

转义符'\'会出现在第二字节的字符集如下：

my_charset_big5_chinese_ci

my_charset_big5_bin

my_charset_cp932_japanese_ci

my_charset_cp932_bin

my_charset_gbk_chinese_ci

my_charset_gbk_bin

my_charset_sjis_japanese_ci

my_charset_sjis_bin

分析了第一个问题，再来看第二个问题，binlog被转成16进制后，为什么主从上的数据会出现不一致？MySQL中，参数类型的确定在SQL语句的解析中进行，由于prepare statement采用先给出SQL后设置值得形式，所以在解析SQL时，MySQL还不知道具体的值，构造了Item_param对象，然后通过设置，把后面的参数'1'设置给Item_param的str_value成员，而对于salve，binlog传递没有prepared statement的信息，所以在slave上，还是按照一般的方式执行，slave发现value的值为0x31，所以构造一个Item_hex_string对象来保存，所以我们只需要查看下两个Item的save_in_field方法，就可以查明具体的原因，先看master上的处理：

field->store(str_value.ptr(), str_value.length(),str_value.charset());

最终的处理方式为

get_int(cs, from, len, &rnd, UINT_MAX32, INT_MIN32, INT_MAX32);

我们的字段类型为int，而实际给定的值是string，在处理时，需要进行转换，转换的关键代码如下：

for(ul=0;str

{

ul=ul*10+ch;

}

从上面的代码我们可以知道，master这种转化方式类似于C中的atoi函数，例如字符串‘1234'会被转换为1234，如果中间出现非数字字符，后面部分会被截断，所以，在master上的数据就是字符串'1'转换过来的值1

现在看下slave上的处理：

slave上处理转换的方法主要在下面的代码：

nr= (ulonglong) val_int();

函数val_int的处理过程如下：

先检查字段类型，如果是string则按照string的方式处理，如果不是string，则按照下面的方式处理

char*end=(char*)str_value.ptr()+str_value.length(),

*ptr=end-min(str_value.length(),sizeof(longlong));

ulonglong value=0;

for(;ptr!=end;ptr++)

value=(value<<8)+(ulonglong)(uchar)*ptr;

slave上通过把value中的每个字节强制转化得到，所以如果value为‘1234’，通过强制转换过来的值将是:

(uchar)'1'<<24+(uchar)'2'<<16+(uchar)'3'<<8+(uchar)'4'

=31<<24+32<<16+33<<8+34

这样就造成了master-slave上的数据不一致

总结

该问题主要的原因在于MySQL的两种不同的Item在处理字符串转整型的方法不一致，Item_param通过类似于atoi的形式，直接把字符中的数字通过-'0'转换到整型，而Item_hex_string则通过强制内存转化所得，这两种方式都合理，但是两边没有统一，造成replication出错，MySQL从5.1版本后到目前MySQL5.5的版本中都存在该问题(MySQL5.6没有测试过，应该也存在该问题)。解决方法：

服务端使用utf8字符集编码(由于前面时gbk,改成utf8会出现乱码等很多问题)

更改应用不对int字段进行非int数据的插入

更改应用不使用prepare statement

binlog的format设置成row格式

注：该问题已经上报给MariaDB，并且被确认为一个Bug，将在后续的版本中进行修复。