MySQL字符集——MySql系列（一）

灵魂猎手
156 阅读9分钟

一、字符编码基础知识

1.1 字符集 & 字符编码

计算机中存储的是二进制，那如何存储文字呢，这就需要把二进制与文字做一个映射，或者说把数字与文字做一个关联。做映射需要搞定两个事情

  • 字符集（charset）:字符的集合，比如中文字符集、西欧字符集、Unicode字符集；
  • 编码规则（encoding）:把字符映射为二进制数据的过程叫做编码，反之为解码；

一个字符集可能有多种编码，一个编码规则表示一种字符集；

其实不用太纠结这两个概念，多数的编码，是不区分字符集和编码规则的，比如ASCII、GB2312、GBK等，这些字符集只有一种编码方式，自然也就不需要区分字符集和编码方式。而且这两个单词，也不用那么严格的区分，比如HTML中指定编码方式：

<meta charset="utf-8">

1.2 Unicode & UTF

在Unicode出现之前，一直有个问题，就是一种编码无法表示多种语言，比如latin无法表示中文，GBK也无法表示阿拉伯文。而Unicode就是为了解决这个问题，Unicode（unify code），也就是统一编码，就是为了使用一种编码，可以表示世界上绝大多数的语言，也就是给绝大多数语言的字符，都映射到一个数字上。具体的映射规则，可以看维基百科Unicode字符平面映射。

如果使用Unicode直接编码，每个字符需要4个字节，有个显而易见的问题，占用空间过大，尤其是在英文环境下，所以就需要一个变长的编码方式对Unicode进行编码，也就是UTF（Unicode Transformation Format）编码，根据以多少位为基准进行编码，又分为UTF8/UTF16/UTF32。其中常用的就是UTF8。

1.2.1 UTF8编码方式

UTF8是一种典型的变长编码方式，他和Unicode 之间的转换关系表 ( x 字符表示码点占据的位 )如下

U+ 0000 ~ U+  007F: 0XXXXXXX
U+ 0080 ~ U+  07FF: 110XXXXX 10XXXXXX
U+ 0800 ~ U+  FFFF: 1110XXXX 10XXXXXX 10XXXXXX
U+10000 ~ U+10FFFF: 11110XXX 10XXXXXX 10XXXXXX 10XXXXXX

简单的概括下，就是第一个字节的前几位，标识该字符需要几个字节标识，如果是0标识1个，否则开头有多少连续的1，标识需要几个，则其余字节前两位必须是10，只有后六位是有效位。

为什么后面的字节必须是以10开头呢？第一个字节不就已经标识了该字符使用几个字节了么。

简单来说，就是即使倒过来，也是可以区分的；

知乎 - utf-8编码规则中多字节情况里，为什么后面的字节要以10开头?

PS:理论上，UTF8可以使用1-6个字节，但实际上没用到那么多，最新的规范，UTF8最多使用4个字节。

PS:一个汉字一般UTF8编码下一般是3个字节，特别特别特别的需要4个字节。

1.2.2 UTF16编码方式

顾名思义，UTF16就是以2字节为单位进行编码，一个Unicode字符使用2字节或者4字节来标识。开始我以为它和UTF8的编码方式差不多，会使用一位来标识该字符是使用2字节，还是4字节。然而，这样太浪费空间了，UTF16使用D800-DFFF这个范围，来专门给4字节的Unicode字符编码使用。D800-DBFF标识UTF-16的高半区，DC00-DFFF标识低半区，其余的自然是2个字节的编码。可以参考维基百科Unicode字符平面映射，搜索D800。

utf16.png

简单的概括一下UTF16的编码方式：

  • 从U+0000至U+D7FF以及从U+E000至U+FFFF的码位，也就是传说中最常用的编码范围BMP(Basic Multilingual Plane),直接使用对应的两字节编码；
  • 从U+10000到U+10FFFF的码位，减去0x10000，共计FFFFF个，也就是20位，前10位加上0xD800，映射到D800-DBFF，后10位加上0xDC00，映射到DC00-DFFF，就可以了；

Java中，String使用什么编码方式存储字符串？

没错，就是UTF16，起码Java8是这样的。

char类型只有2字节，有些Unicode是不是无法表示？

没错，确实某些字符无法表示，但都是不常用的。同理String.charAt方法也无法处理4字节的UTF16字符，String的length方法，返回的是char的长度，也就是某些字符的长度会被认为是2。

如果真有4字节的UTF16字符该如何处理？

可以使用codePointAt方法代替charAt，codePointCount方法代替length。

1.2.3 UTF32编码方式

与Unicode一一对应。

二、MySQL的字符集

MySQL当然也有自己的字符集，不过并不区分字符集和编码方式，直接使用编码方式，标识字符集。除了字符集之外，mysql还有一个比较规则，就是比较两个字符串是否相等，以及在排序中，顺序的先后。

2.1 字符集 & 比较规则

使用以下命令，可以分别查看mysql支持的字符集（CHARSET）和排序规则（COLLATION）。

SHOW CHARSET [LIKE 匹配的模式]
SHOW COLLATION [LIKE 匹配的模式]

在数据库上执行以下，可以看到我们常见的字符集，包括ascii、gbk、utf8mb4等，每种字符集，会有一个默认的排序规则。其实每个字符集，都对应多种比较规则，而每种比较规则，只能处理一种字符集。

比较规则的名字一般比较长，类似utf8_general_ci，不过也有一定规则：

  • 前缀就是这个比较规则关联的字符集；
  • 然后是比较的算法，比如utf8_polish_ci标识波兰语的比较规则，utf8_spanish_ci则是西班牙语，utf8_general_ci 是一种通用的比较规则
  • 后缀标识一些特性
    • ai : accent insensitive，不区分重音
    • as : accent sensitive |区分重
    • ci : case insensitive |不区分大小写
    • cs : case sensitive |区分大小写
    • bin : binary 以二进制方式比较

2.2 如何设置

字符集和比较规则，都直接支持到列级别，一个表，每个字符串类型的列，都可以不同的编码和比较的规则：

CREATE TABLE 表名(
列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
其他列...
);

也支持表级别的，只要不指定列的编码和字符集，就默认使用列的：

CREATE TABLE 表名 (列的信息)
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]]
ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称]

还支持数据库级别的，如果表和列没有特殊指定，就使用数据库的：

SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

CREATE DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];

也有服务器级别的

SHOW VARIABLES LIKE 'character_set_server'
SHOW VARIABLES LIKE 'collation_server';

想要修改，可以使用set语句修改，或者直接在配置文件中修改后重启；

2.3 连接编码

数据库的查询请求，也有需要对应的编码，有这样几个变量控制，这个是默认的编码规则

  • character_set_client 服务器解码请求时使用的字符集
  • character_set_connection 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
  • character_set_results 服务器向客户端返回数据时使用的字符集

也就是说，正常一个请求，mysql先使用character_set_client识别请求的编码，然后转为character_set_connection的编码继续处理，如果与操作的列不一致，还会把继续转换为表或列的编码。最后查询结果，再转为character_set_results返回

如果这些参数不一致，编码会转来转去，而且，如果被转换的字符集，无法包含转换前的字符集，会出现问题，所以，通常最好是一致的。

另外，这几个参数也可以直接在连接的url中指定，比如jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&characterSetResults=UTF-8

  • characterEncoding=UTF-8指定了character_set_client、character_set_connection的值。
  • characterSetResults=UTF-8则指定了character_set_results的值。与方法一的思路是同样的。

2.4 utf8与utf8mb4的区别

MySQL字符编码集中有两套UTF-8编码实现：“utf8”和“utf8mb4”，其中“utf8”是一个字最多占据3字节空间的编码实现；而“utf8mb4”则是一个字最多占据4字节空间的编码实现，也就是UTF-8的完整实现。

这是由于MySQL在4.1版本开始支持UTF-8编码（当时参考UTF-8草案版本为RFC 2279）时，为2003年，并且在同年9月限制了其实现的UTF-8编码的空间占用最多为3字节，而UTF-8正式形成标准化文档（RFC 3629）是其之后。限制UTF-8编码实现的编码空间占用一般被认为是考虑到数据库文件设计的兼容性和读取最优化，但实际上并没有达到目的，而且在UTF-8编码开始出现需要存入非基本多文种平面的Unicode字符（例如emoji字符）时导致无法存入（由于3字节的实现只能存入基本多文种平面内的字符）。直到2010年在5.5版本推出“utf8mb4”来代替、“utf8”重命名为“utf8mb3”并调整“utf8”为“utf8mb3”的别名，并不建议使用旧“utf8”编码，以此修正遗留问题。

2.5 MySQL中文如何排序

select * from table_name
order by convert(col_name using gbk) COLLATE gbk_chinese_ci asc;

这个显然有点复杂，还需要转换，其实可以直接把这列设置为GBK的，并设置比较规则为gbk_chinese_ci

avatar
文章
阅读
粉丝