MySQL concat + outfile的bug原因分析

linesky

项目中碰到一个bug，需要将MySQL表中的数据导出，字段中间用逗号隔开

1、复现

步骤：

版本 5.1.48

a) 准备数据

1. CREATE TABLE `test` (   `id` int(11) DEFAULT NULL,
   
2. 
   
3.      `data` char(10) DEFAULT NULL
   
4. 
   
5.      ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
   
6. 
   
7. insert into tad2 values (1,’丁\\奇’);
   

复制代码

b) select concat(id, data) from test into outfile ‘/tmp/a’;

现象：

在生成的/tmp/a中，发现”丁”字乱码了，实际上是”丁”的第二个字节前面多了个 ‘\’

2、相关现象

a)                  select concat(id, data) from test; –结果正常(否则早报了)

b)                  select id, data from test into outfile ‘/tmp/a’ –结果正常

c)                   select concat(data, data) from test into outfile ‘/tmp/a’;结果正常

d)                  MySQL官方 5.5版本; select concat(id, data) from test into outfile ‘/tmp/a’; 结果正常。

3、源码分析

先看出问题的代码位置select_export::send_data (sql/sql_class.cc),这个函数是将要输出到外部文件(outfile)的字符串作处理，比如将’\’转成’\\’. 这解释了上面的相关现象a), 不需要输出到外部文件，不调用这个函数。

字符转换代码逻辑如下

1. for (start=pos=(char*) res->ptr(),end=pos+used_length ;             pos != end ;
   
2. 
   
3.              pos++)
   
4. 
   
5.         {
   
6. 
   
7. if (use_mb(res_charset))
   
8. 
   
9.           {   
   
10. 
    
11.             int l;
    
12. 
    
13.             if ((l=my_ismbchar(res_charset, pos, end)))
    
14. 
    
15.             {   
    
16. 
    
17.               pos += l-1;
    
18. 
    
19.               continue;
    
20. 
    
21.             }   
    
22. 
    
23.           }
    
24. 
    
25.             if ((NEED_ESCAPING(*pos) ||
    
26. 
    
27.                (check_second_byte &&
    
28. 
    
29.                 my_mbcharlen(character_set_client, (uchar) *pos) == 2 &&
    
30. 
    
31.                 pos + 1 < end &&
    
32. 
    
33.                 NEED_ESCAPING(pos[1]))) &&
    
34. 
    
35.               (enclosed || !is_ambiguous_field_term ||
    
36. 
    
37.                (int) (uchar) *pos != field_term_char))
    
38. 
    
39.             {当前字符前加入 ‘\’}
    
40. 
    
41.         }
    

复制代码

说明：

a)            宏和注释删掉了。简单说来就是一个循环，判断需要转译的字符，前面加上’\’。

b)            use_mb(res_charset)判断当前字符串所使用的字符集是否定义了ismbchar。若定义，则通过my_ismbchar判断每个单字的长度l，并直接跳过l个字节。

c)             对于不能跳的，进入if的判断，如果是需要转译的字符，则前面加上’\’.

    这代码看上去似乎没什么问题，尤其是对于多字节字符还通过use_mb作了保护。

    不过这个if判断需要细细看一下。逻辑简单描述是这样的“若当前字符需要转译，则加’\’; 若客户端使用字符为2字节，且下一个字节需要转译，则当前字符也转译。“

    于是这儿就有问题了，客户端使用gbk. 当我们使用concat(id, data)时，整个合并的字符串被当成什么字符来处理呢――binary。于是use_mb的多字符串保护都无效了。在处理“丁”的第二个字符时，它的下一个字符是’\’， 因此原本应该是转译成”B6 A1”的这个字，被转译成”B6 5C A1”, 在输出文本上就看到了乱码。之所以会变成binary，是因为bigint类型的id字段和gbk作合并判断的结果。

    于是我们知道相关现象c)， 由于是两个gbk合并，结果还是gbk，字符保护生效。

    同时相关现象b)， 由于没有concat，因此不需要类型合并，按照两个字符串来处理，data字段还是gbk，同样不会有问题。

从代码中可以发现，所有的数字相关字段与字符串字段作concat并outfile之后，都会有这个现象。

4、MySQL 5.5修复了？

在5.5版本中，数字字段不再是my_charset_binary, 而是my_charset_numeric, 而这个my_charset_numeric，其实就是my_charset_latin1. latin1与gbk合并的结果是gbk. 所以相关现象d),并不是处理了这个问题，而是刚好绕过。

完成上面的分析，要再复现并不难。把表中的id字段改成blob字段，5.5中“丁”字还是乱码了。

5、反思，这个算“bug”吗

    我们回顾一下，发现之所以会出现文章开头说到的乱码，是因为concat(id, data)的合并结果字符串是binary，但是客户端使用的是gbk造成的。

    如果在导出之前，先set names binary, 结果自然正常了。因为即使字符保护被跳过，在判断到“丁”字的第二个字符的时候，由于客户端也使用binary，就不会在这里加’\’， 而整个串中只有’\’会被转译成 ‘\\’。这个是我们要的结果。

    似乎只是使用的错误？其实细想一下就知道了，为什么源码中要加第二个字符的判断？而且把gbk认定为是“需要检查下一个字符，以确定当前字符是否需要转译”的字符集？原因就是编码上，有些汉字的第二个字符就是需要转译的，如果按字符判断，问题就出现了。

    一个例子就是” 盶”(B1 5C)，在gbk下，需要转译成什么呢，直接输出”B1 5C”显然是不行的， 这个5C会把它后面的字符给转译了；转成B1 5C 5C也不行，会被理解成B1+”5C5C”。 实际上MySQL希望转成” 5C B1 5C 5C”, 所以代码就成了我们看到的这样。

  所以我们的结论还是认为它是个bug，看下面这行数据

1. CREATE TABLE `test` (   `name`blob,
   
2. 
   
3.      `data` char(10) DEFAULT NULL
   
4. 
   
5.      ) ENGINE=InnoDB DEFAULT CHARSET=gbk;
   
6. 
   
7. insert into tad2 values (‘盶’,'丁\\奇’);
   

复制代码

还是那个需求，把表数据输出，中间用逗号隔开，此时客户端要使用什么字符集呢。实际上不论使用binary还是gbk，都会造成乱码。

6、简单修改

    这个问题还挺复杂的，也许binary和gbk字段就不应该允许concat，也许即使是concat，也应该按照原来的字段类型先转译好了再直接拼接，这样就不会有用A字符集的规则解释B字符集的矛盾了。

    简单验证一下上面的阐述，把5.1换成跟5.5相似的现象吧.（虽然只是把错100步改成错90步）

a)      在sql/mysql_priv.h的enum Derivation定义中增加DERIVATION_NUMERIC=5 (当然DERIVATION_IGNORABLE修改为=6)

b)      在sql/field.h的class Field_num声明中增加三行

  enum Derivation derivation(void) const { return DERIVATION_NUMERIC; }

  uint repertoire(void) const { return MY_REPERTOIRE_ASCII; }

  CHARSET_INFO *charset(void) const { return &my_charset_latin1; }

c)      在sql/field.h的class Field声明中增加一行

virtual uint repertoire(void) const {  }

d)      修改sql/item.cc的Item_field::set_field(Field *field_par)函数
collation.set(field_par->charset(), field_par->derivation(), field_par->repertoire());

好吧，这样所有的数字类型字段都用latin1编码了。

7、小结

    对于支持多字符集的软件，字符串处理还是很复杂的。当然我们知道5.5还是有问题的，反馈一下，期待官方的方案吧。

    目前碰到有这个需求的,还是先直接导出,在生成文件中自己作文本处理吧。