[MySQL] 字符集

这阵子在做MySQL字符集改造(gbk->utf8)的一些事情, 以及业务上乱码数据的清理工作, 借此机会梳理了下字符集相关的概念, 做个总结.

1 字符集基础概念

1.1 常见字符集

• Latin-1, 全称ISO 8859-1, Latin-1对ASCII的拉丁语扩展, 向下兼容ASCII, 其编码范围是0x00-0xFF, 0x00-0x7F之间完全和ASCII一致, 0x80-0x9F之间是控制字符, 0xA0-0xFF之间是文字符号.

• ASCII, 0x00-0x7f.

• Unicode表.

• UTF8, Unicode表的一种实现.

  Unicode都是2字节, 用来存储ASCII的效率很低, 要比ASCII编码多处一倍的空间.

  UTF8的范围是1-6个字节

  GBK->UTF8: 需要Unicode转换

  GBK查表 -> Unicode转换 -> UTF8

  在java中一切以Unicode为基准, Char a = '诺' 和 Char b = 'N'都是可以的, 一个Char两个字节就是可以表示的Unicode. 如果要表示复杂的文字, 那就需要Char数组.

  Unicode->UTF8:

  1. 确定UTF8编码的字节数
  2. 用Unicode编码从地位到高位依次按规则填入空位, 不足的高位以0补充

MySQL编码

1. MySQL字符参数

+--------------------------+-----------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /u01/my3406/share/charsets/ |
+--------------------------+-----------------------------+

• character_set_client 客户端来源数据字符集

• character_set_connection 连阶层字符集

  MySQL使用character_set_connection字符集讲client端的字符串转换为character_set_connection表示的字符集

• character_set_results 查询结果数据字符集

  MySQL将数据存储的字符集转换为character_set_results字符集返回给前端

• character_set_database 当前数据库的字符集

• character_set_server 数据库server字符集

• character_set_system 系统字符集

  SET NAMES xxx;
  等价于
  set character_set_client = xxx;
  set character_set_connection = xxx;
  set character_set_results = xxx;

2. MySQL字符集转换过程

大致过程如下:

• MySQL Server收到请求时, 将请求数据从character_set_client转换成character_set_connection;

• 进行Server内部操作(譬如: 存储)时, 将请求数据从character_set_connection转换成内部字符集;

  MySQL内部字符集是有3层继承关系的:

  • 使用每个表数据字段的character set设定值;
  • 如没上述设定值, 则使用表的default character set设定值;
  • 如没上述设定值, 则使用库的character set设定值;

• 将操作结果从内部字符集转换为character_set_results返回;

3. MySQL乱码

• hex定位column的16进制字符;

  select hex(column) from table_a;

• 如果hex值包含一串3F, 那就说明彻底乱码了, 无法转换回去了;

• 通过set names xxx; 以及调整终端字符集等手段转码;

4. 几种乱码case

• latin1->latin1->utf8

  • 向utf8的表里插入latin1的源数据

    set names latin1;
    即
    set character_set_client = latin1;
    set character_set_connection = latin1;
    set character_set_results = latin1;

  • 读取

    set names utf8;
    select name, hex(name) from renotest;
    +---------------+----------------------------+
    | name | hex(name) |
    +---------------+----------------------------+
    | 雷诺 | C3A9E280BAC2B7C3A8C2AFC2BA |
    +---------------+----------------------------+

    set names latin1;
    select name, hex(name) from renotest;
    +--------+----------------------------+
    | name | hex(name) |
    +--------+----------------------------+
    | 雷诺 | C3A9E280BAC2B7C3A8C2AFC2BA |
    +--------+----------------------------+

  • 乱码

    插入时, 汉字从原始的3个字节变成6个字节保存.

    读取时, utf8->utf8的字符集转换过程, 将保存的6字节原封不动的返回, 产生乱码.

• utf8->utf8->latin1

  • 向latin1的表里插入utf8的源数据

    set names utf8;
    即
    set character_set_client = utf8;
    set character_set_connection = utf8;
    set character_set_results = utf8;

  • 读取

    set names utf8;
    select name, hex(name) from renotest2;
    +------+-----------+
    | name | hex(name) |
    +------+-----------+
    | ?? | 3F3F |
    +------+-----------+

    set names latin1;
    select name, hex(name) from renotest2;
    +------+-----------+
    | name | hex(name) |
    +------+-----------+
    | ?? | 3F3F |
    +------+-----------+

  • 乱码

    插入时, utf8->utf8->latin1的字符集转换，如果原始数据中含有u0000~u00ff范围以外的Unicode字符, 会因为无法在latin1字符集中表示而被转换为"?"(0x3F)符号.

    读取时, 无论怎么转换0x3F都没用, 彻底乱码了.

5. 建议

建议业务使用统一的字符集, 保证MySQL Server内部从server到database, 再到table, 再到column使用一套相同的字符编码.