字符集简介

在计算机中只能存储二进制数据，那该怎么存储字符串呢？
就是建立字符与二进制数据的映射关系了，建立这个关系最起码要搞清楚两件事儿：

  1. 你要把哪些字符映射成二进制数据？

也就是界定清楚字符范围

  1. 怎么映射？

将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码

  1. 'a' -> 00000001 (十六进制：0x01)
  2. 'b' -> 00000010 (十六进制：0x02)
  3. 'A' -> 00000011 (十六进制：0x03)
  4. 'B' -> 00000100 (十六进制：0x04)

比较规则简介

在确定了abc字符集表示字符的范围以及编码规则后，怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小，比方说字符’a’的编码为0x01，字符’b’的编码为0x02，所以’a’小于’b’，这种简单的比较规则也可以被称为二进制比较规则，英文名为binary collation。

二进制比较规则是简单，但有时候并不符合现实需求，比如在很多场合对于英文字符都是不区分大小写的，也就是说’a’和’A’是相等的，在这种场合下就不能简单粗暴的使用二进制比较规则了，这时候可以这样指定比较规则：

  1. 将两个大小写不同的字符全都转为大写或者小写。
  2. 再比较这两个字符对应的二进制数据。

这是一种稍微复杂一点点的比较规则，但是实际生活中的字符不止英文字符一种，比如汉字有几万之多，对于某一种字符集来说，比较两个字符大小的规则可以制定出很多种，也就是说同一种字符集可以有多种比较规则，稍后就要介绍各种现实生活中用的字符集以及它们的一些比较规则。

一些重要的字符集

不幸的是，这个世界太大了，不同的人制定出了好多种字符集，它们表示的字符范围和用到的编码规则可能都不一样。看一下一些常用字符集的情况：

  • ASCII字符集共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码
  • GBK字符集

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312

  • utf8字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节，比方说这样：

  1. 'L' ->  01001100（十六进制：0x4C）
  2. '啊' ->  111001011001010110001010（十六进制：0xE5958A）

其实准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。

打个比方mysql的utf8是这样
image.png
一个utf8数字占1个字节
一个utf8英文字母占1个字节
少数是汉字每个占用3个字节，多数占用4个字节

对于同一个字符，不同字符集也可能有不同的编码方式。比如对于汉字’我’来说，ASCII字符集中根本没有收录这个字符，utf8和gb2312字符集对汉字我的编码方式如下：

  1. utf8编码：111001101000100010010001 (3个字节，十六进制表示是：0xE68891)
  2. gb2312编码：1100111011010010 (2个字节，十六进制表示是：0xCED2)

MySQL中支持的字符集和排序规则

MySQL中的utf8和utf8mb4

上边说utf8字符集表示一个字符需要使用1～4个字节，但是常用的一些字符使用1～3个字节就可以表示了。而在MySQL中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL的偷偷的定义了两个概念：

  • utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
  • utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

有一点需要大家十分的注意，在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，那请使用utf8mb4。

字符集的查看

MySQL支持好多好多种字符集，查看当前MySQL中支持的字符集可以用下边这个语句：

  1. SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
  1. mysql> SHOW CHARSET;
  2. +----------+---------------------------------+---------------------+--------+
  3. | Charset  | Description                     | Default collation   | Maxlen |
  4. +----------+---------------------------------+---------------------+--------+
  5. | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
  6. ...
  7. | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
  8. | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
  9. ...
  10. | ascii    | US ASCII                        | ascii_general_ci    |      1 |
  11. ...
  12. | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
  13. ...
  14. | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
  15. | latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
  16. ...
  17. | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
  18. | ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
  19. ...
  20. | latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
  21. | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
  22. | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
  23. | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
  24. ...
  25. | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
  26. | binary   | Binary pseudo charset           | binary              |      1 |
  27. ...
  28. | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
  29. +----------+---------------------------------+---------------------+--------+
  30. 41 rows in set (0.01 sec)

可以看到，这个MySQL版本一共支持41种字符集，其中的Default collation列表示这种字符集中一种默认的比较规则。注意返回结果中的最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节。

几个常用到的字符集的Maxlen

utf8 3
utf8mb4 4

比较规则的查看

查看MySQL中支持的比较规则的命令如下：

  1. SHOW COLLATION [LIKE 匹配的模式];
  1. mysql> SHOW COLLATION LIKE 'utf8\_%';
  2. +--------------------------+---------+-----+---------+----------+---------+
  3. | Collation                | Charset | Id  | Default | Compiled | Sortlen |
  4. +--------------------------+---------+-----+---------+----------+---------+
  5. | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
  6. | utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
  7. | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
  8. | utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
  9. | utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
  10. | utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
  11. | utf8_slovenian_ci        | utf8    | 196 |         | Yes      |       8 |
  12. | utf8_polish_ci           | utf8    | 197 |         | Yes      |       8 |
  13. | utf8_estonian_ci         | utf8    | 198 |         | Yes      |       8 |
  14. | utf8_spanish_ci          | utf8    | 199 |         | Yes      |       8 |
  15. | utf8_swedish_ci          | utf8    | 200 |         | Yes      |       8 |
  16. | utf8_turkish_ci          | utf8    | 201 |         | Yes      |       8 |
  17. | utf8_czech_ci            | utf8    | 202 |         | Yes      |       8 |
  18. | utf8_danish_ci           | utf8    | 203 |         | Yes      |       8 |
  19. | utf8_lithuanian_ci       | utf8    | 204 |         | Yes      |       8 |
  20. | utf8_slovak_ci           | utf8    | 205 |         | Yes      |       8 |
  21. | utf8_spanish2_ci         | utf8    | 206 |         | Yes      |       8 |
  22. | utf8_roman_ci            | utf8    | 207 |         | Yes      |       8 |
  23. | utf8_persian_ci          | utf8    | 208 |         | Yes      |       8 |
  24. | utf8_esperanto_ci        | utf8    | 209 |         | Yes      |       8 |
  25. | utf8_hungarian_ci        | utf8    | 210 |         | Yes      |       8 |
  26. | utf8_sinhala_ci          | utf8    | 211 |         | Yes      |       8 |
  27. | utf8_german2_ci          | utf8    | 212 |         | Yes      |       8 |
  28. | utf8_croatian_ci         | utf8    | 213 |         | Yes      |       8 |
  29. | utf8_unicode_520_ci      | utf8    | 214 |         | Yes      |       8 |
  30. | utf8_vietnamese_ci       | utf8    | 215 |         | Yes      |       8 |
  31. | utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 |
  32. +--------------------------+---------+-----+---------+----------+---------+
  33. 27 rows in set (0.00 sec)
  • 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。
  • 后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。
  • 名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的： | 后缀 | 英文释义 | 描述 | | —- | —- | —- | | _ai | accent insensitive | 不区分重音 | | _as | accent sensitive | 区分重音 | | _ci | case insensitive | 不区分大小写 | | _cs | case sensitive | 区分大小写 | | _bin | binary | 以二进制方式比较 |

比如utf8_general_ci这个比较规则是以ci结尾的，说明不区分大小写。

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，SHOW COLLATION的返回结果中的Default列的值为YES的就是该字符集的默认比较规则，比方说utf8字符集默认的比较规则就是utf8_general_ci。

utf8mb4_unicode_ci和utf8mb4_general_ci区别
主要从排序准确性和性能两方面看：

  • 准确性
    utf8mb4_unicode_ci 是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
    utf8mb4_general_ci 没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。
    但是在绝大多数情况下，这种特殊字符的顺序一定要那么精确吗。比如Unicode把ß、Œ当成ss和OE来看；而general会把它们当成s、e，再如ÀÁÅåāă各自都与 A 相等。
  • 性能
    utf8mb4_general_ci 在比较和排序的时候更快
    utf8mb4_unicode_ci 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。
    但是在绝大多数情况下，不会发生此类复杂比较。general理论上比Unicode可能快些，但相比现在的CPU来说，它远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。 我个人推荐是 utf8mb4_unicode_ci，将来 8.0 里也极有可能使用变为默认的规则。相比选择哪一种collation，使用者应该更关心字符集与排序规则在db里要统一就好。

字符集和比较规则的应用

各级别的字符集和比较规则

MySQL有4个级别的字符集和比较规则，分别是：

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

接下来仔细看一下怎么设置和查看这几个级别的字符集和比较规则。

服务器级别

MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则：

系统变量 描述
character_set_server 服务器级别的字符集 (比如, utf8)
collation_server 服务器级别的比较规则 (比如, utf8_general_ci)

数据库级别

  1. CREATE DATABASE 数据库名
  2.     [[DEFAULT] CHARACTER SET 字符集名称]
  3.     [[DEFAULT] COLLATE 比较规则名称];
  5. ALTER DATABASE 数据库名
  6.     [[DEFAULT] CHARACTER SET 字符集名称]
  7.     [[DEFAULT] COLLATE 比较规则名称];
  1. mysql> CREATE DATABASE charset_demo_db
  2.     -> CHARACTER SET gb2312
  3.     -> COLLATE gb2312_chinese_ci;
  4. Query OK, 1 row affected (0.01 sec)

如果想查看当前数据库使用的字符集和比较规则，可以查看下面两个系统变量的值（前提是使用USE语句选择当前默认数据库，如果没有默认数据库，则变量与相应的服务器级系统变量具有相同的值）：

系统变量 描述
character_set_database 当前数据库的字符集
collation_database 当前数据库的比较规则

**character_set_database** 和 **collation_database** 这两个系统变量是只读的，不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则
数据库的创建语句中也可以不指定字符集和比较规则，这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

  1. CREATE TABLE 表名 (列的信息)
  2.     [[DEFAULT] CHARACTER SET 字符集名称]
  3.     [COLLATE 比较规则名称]]
  5. ALTER TABLE 表名
  6.     [[DEFAULT] CHARACTER SET 字符集名称]
  7.     [COLLATE 比较规则名称]
  1. mysql> CREATE TABLE t(
  2.     ->     col VARCHAR(10)
  3.     -> ) CHARACTER SET utf8 COLLATE utf8_general_ci;
  4. Query OK, 0 rows affected (0.03 sec)

如果建表语句中并没有明确指定字符集和比较规则，则表的字符集和比较规则将继承所在数据库的字符集和比较规则

列级别

需要注意的是，对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

  1. CREATE TABLE 表名(
  2.     列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
  3.     其他列...
  4. );
  6. ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
  1. mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;
  2. Query OK, 0 rows affected (0.04 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

修改字符集和仅修改比较规则

由于字符集和比较规则是互相有联系的，如果只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：

  • 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
  • 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

不论哪个级别的字符集和比较规则，这两条规则都适用，以服务器级别的字符集和比较规则为例来看一下详细过程：

  • 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则 ```scala mysql> SET character_set_server = gb2312; Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘character_set_server’; +———————————+————+ | Variable_name | Value | +———————————+————+ | character_set_server | gb2312 | +———————————+————+ 1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘collation_server’; +—————————+—————————-+ | Variable_name | Value | +—————————+—————————-+ | collation_server | gb2312_chinese_ci | +—————————+—————————-+ 1 row in set (0.00 sec)

  1. 只修改了character_set_server的值为gb2312，collation_server的值自动变为了gb2312_chinese_ci。
  3. - 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。
  4. ```scala
  5. mysql> SET collation_server = utf8_general_ci;
  6. Query OK, 0 rows affected (0.00 sec)
  8. mysql> SHOW VARIABLES LIKE 'character_set_server';
  9. +----------------------+-------+
  10. | Variable_name        | Value |
  11. +----------------------+-------+
  12. | character_set_server | utf8  |
  13. +----------------------+-------+
  14. 1 row in set (0.00 sec)
  16. mysql> SHOW VARIABLES LIKE 'collation_server';
  17. +------------------+-----------------+
  18. | Variable_name    | Value           |
  19. +------------------+-----------------+
  20. | collation_server | utf8_general_ci |
  21. +------------------+-----------------+
  22. 1 row in set (0.00 sec)
  24. mysql>

只修改了collation_server的值为utf8_general_ci，character_set_server的值自动变为了utf8

各级别字符集和比较规则小结

我们介绍的这4个级别字符集和比较规则的联系如下：

  • 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
  • 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
  • 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

    客户端和服务器通信中的字符集

    字符集转换的概念

    如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又把它按照gbk字符集进行编码，最后编码后的字节串就是0xCED2，我们把这个过程称为字符集的转换，也就是字符串’我’从utf8字符集转换为gbk字符集。

    MySQL中字符集的转换

    从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。

这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量

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

不同操作系统的默认值可能不同

如果你使用的是可视化工具，比如navicat之类的，这些工具可能会使用自定义的字符集来编码发送到服务器的字符串，而不采用操作系统默认的字符集

分析例子

字符在这个过程中字符集的转换
image.png

  1. 服务器接收到客户端发送来的请求其实是一串二进制的字节，它会认为这串字节采用的字符集是character_set_client，然后把这串字节转换为character_set_connection字符集编码的字符。由于我的计算机上character_set_client的值是utf8，对字节串进行解码，得到的字符串，然后按照character_set_connection代表的字符集编码，得到的结果
  2. 因为表的列采用的字符集，与character_set_connection一致，所以直接到列中找字节值为0xABC的记录，最后找到了一条记录。

    如果某个列使用的字符集和character_set_connection代表的字符集不一致的话，还需要进行一次字符集转换。

通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换

  1. SET NAMES 字符集名;
  1. SET character_set_client = 字符集名;
  2. SET character_set_connection = 字符集名;
  3. SET character_set_results = 字符集名;

另外，如果你想在启动客户端的时候就把character_set_client、character_set_connection、character_set_results这三个系统变量的值设置成一样的，那我们可以在启动客户端的时候指定一个叫default-character-set的启动选项，比如在配置文件里可以这么写：

  1. [client]
  2. default-character-set=utf8

它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8

各个变量的含义

image.png

JDBC的字符集

client没设置就用server的, jdbc会读取这个作为client
对于 Connector/J 8.0.25 及更早版本：characterEncoding在连接时自动检测客户端 和 服务器之间的字符编码（前提 connectionCollation 是未设置 Connector/J 连接属性）。服务器上的编码是使用系统变量指定的 character_set_server, 驱动程序会自动使用该编码

https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charsets.html
image.png

mysql交互, 使用字符集相关文档
https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html
image.png