这阵子在做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使用一套相同的字符编码.