[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使用一套相同的字符编码.