红联Linux门户
Linux帮助

MySQL字符集GBK转换到UTF8

发布时间:2015-04-22 10:05:10来源:linux网站作者:cchust

在生产环境中,MySQL数据库字符集因为各种原因需要升级,比如为了支持汉字,从latin1字符集升级到GBK,后面为了支持多个语言文字,需要将GBK升级到UTF8等。迁移过程网上有很多,我今天主要想讲下字符集转换后,可能对业务产生的影响,我以GBK转换到UTF8为例说明。


主要有两点:

1.汉字在GBK编码中占2个字节,在UTF8编码中占3个字节,而mysql的索引要求总长度不超过767个字节,因此索引字符数会被缩短(383->255),特别的,对于唯一索引,要求索引字段长度小于256个字符。

2.编码转换后,导致字段排序发生变化。

这篇文章主要为了说明编码转换后,字段排序如何受影响,会结合mysql源代码给出原因和分析。首先看测试用例,假设cmp_t(GBK编码)和cmp_t2(UTF8编码)分别是迁移前后的表。


测试用例:
 

操作

cmp_t(GBK)

cmp_t2(UTF8)

1

 

GBK表:

select c1,hex(c1) from cmp_t;

 

UTF8表:

select c1,hex(c1) from cmp_t2;

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

| c1   | hex(c1) |

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

| 一  | D2BB   

| 二  | B6FE

| 三  | C8FD   

| a    | 61     

| 1    | 31

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

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

| c1   | hex(c1) |

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

| 一  | E4B880 

| 二  | E4BA8C 

| 三  | E4B889 

| a    | 61     

| 1    | 31     

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

2

GBK表:

select c1,hex(c1) from cmp_t where c1>’a’ order by c1;

 

UTF8表:

select c1,hex(c1) from cmp_t2 where c1>’a’ order by c1;

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

| c1   | hex(c1) |

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

  | B6FE    |

  | C8FD   

  | D2BB   

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

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

| c1   | hex(c1) |

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

  | E4B880 

  | E4B889 

  | E4BA8C 

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

从上面操作返回的结果我们可以得到以下几点信息:

汉字在GBK编码中占2个字节,在UTF8编码中占3个字节
数字和英文字符在GBK和UTF8编码中都只占一个字节
汉字在UTF8编码和GBK编码不同,排序后顺序变化了。


原理分析:

Mysql利用sortcmp函数对字符串进行比较,对于GBK的字符串和UTF8的字符串分别采用接口my_strnncollsp_gbk和my_strnncollsp_utf8比较,这两个函数分别在ctype-gbk.c和ctype-utf8.c中实现,两个函数实现逻辑类似,只是各有自己一套比较大小的规则,下面我主要描述下my_strnncollsp_utf8的比较逻辑和比较大小的规则。


比较逻辑:

获取字符串的第一个字节
根据UTF8的编码规则(附1),确定字符由几个字节组成
根据一定的算法算出字符的加权值(附2),比较大小;若不符合UTF8编码规范,认为是乱码,采用二进制比较(附3)
跳过步骤2返回的字节数,比较下一个字符。


附1:【接口: my_utf8_uni】

根据UTF8编码规则,符合编码规范的字符占用1-6个字节。

取字符串第一个字节 s

if s<0x80

表示字符占1个字节

elif s<0xe0

表示字符占2个字节

elif s<0xf0

表示字符占3个字节

else s<0xf8

表示字符占4个字节

elif s<0xfc

表示字符占5个字节

elif s<0xfe

表示字符占6个字节

英文字符和数字字符编码兼容ASCII,编码值小于0x80,因此都只占1个字节;汉字的utf8编码的首字节都在[0xe0,0xf0]之间,所以占3个字节。


附2:utf8编码比较大小规则

value = ((s[0] & 0x0f) << 12)| ((s[1] ^ 0x80) << 6) | (s[2] ^ 0x80)

s[0],s[1],s[2]表示组成汉字的三个字节,对参与比较的汉子字符进行计算得到value1和value2,通过比较value1和value2的大小,判断字符大小。


附3:二进制比较【接口: bincmp】

memcmp函数比较,即逐字节比较。

因此,如果业务上面需要依赖汉字比较的场景,需要考虑字符集升级(GBK->UTF8)的风险,主要是索引或主键中包含字符串字段需要特别关注,如果字符串中确定只包含有数字和字符,则不会存在问题。