第3章 乱码的前世今生——字符集和比较规则

3.1 字符集和比较规则简介

计算机怎么存储字符串——建立字符与二进制数据的映射关系（界定字符范围&&编码与解码规则）

即字符集和编码规则的概念。

比较规则： 比较两个字符大小的规则。

3.1.1 一些重要的字符集

• ASCII字符集

  128个字符，用1个字节来进行编码，如：

  ‘L’ -> 01001100（十六进制：0x4C，十进制：76）

  ‘M’ -> 01001101（十六进制：0x4D，十进制：77）

• ISO 8859-1字符集

  256个字符，在ASCII字符集的基础上又扩充了128个西欧常用字符，用1个字节来编码。

• GB2312字符集

  

  上述这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式。

• GBK字符集

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

• utf8字符集

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

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

3.2.1 MySQL中的utf8和utf8mb4

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

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

在MySQL中，utf8是utf8mb3的别名。

3.2.2 字符集的查看

MySQL支持很多种字符集，查看当前MySQL中支持的字符集语法如下：

SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

Default collation 列表示这种字符集中一种默认的 比较规则 。最后一列 Maxlen ，它代表该种字符集表示一个字符最多需要几个字节。

3.2.3 比较规则的查看

SHOW COLLATION [LIKE 匹配的模式];

一种字符集可能对应着若干种比较规则，utf8字符集下的比较规则如下：

比较规则的命名规则如下：

• 以与其关联的字符集的名称开头；

• 紧跟着该比较规则主要作用于哪种语言，如utf8_polish_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 。

3.3 字符集和比较规则的应用

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

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

• 服务器级别

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

  系统变量	描述	默认值
  character_set_server	服务器级别的字符集	utf8
  collation_server	服务器级别的比较规则	utf8_general_ci

• 数据库级别

  创建和修改数据库的时候可以指定数据库级别的字符集和比较规则，具体语法如下：

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

  

  查看当前数据库使用的字符集和比较规则

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

注意： 这两个系统变量是只读的，不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。（要修改则使用alter语句）

创建语句若不指定字符集和比较规则，则使用服务器级别的。

• 表级别

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

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

  

  创建和修改语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则。

• 列级别

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

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

  比如修改一下表t中列col的字符集和比较规则：
  

  对某个列来说，若创建和修改语句中没有指定，则将使用该列所在表的字符集和比较规则。

  注意：

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

3.3.2 仅修改字符集或比较规则

字符集和比较规则是有联系的，仅修改其一另一个变化规则如下：

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

对于四种级别的字符集和比较规则都适用。