mysql 字符集比较_你所不知道的MySQL之 - 字符集和比较规则

前言什么是字符集

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

你要把哪些字符映射成二进制数据？即：界定字符范围。

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

其实字符集是人们抽象出来的一个概念，用来描述某个字符范围的编码规则。例如我们定义一个名为xuexi的字符集：

包含字符'a'、'b'、'A'、'B'

编码规则 - 采用1个字节编码一个字符的形式，字符和字节的映射关系如下：'a' -> 00000001 (十六进制：0x01)'b' -> 00000010 (十六进制：0x02)'A' -> 00000011 (十六进制：0x03)'B' -> 00000100 (十六进制：0x04)

有了xuexi字符集，我们就可以用二进制形式表示一些字符串了，下边是一些字符串用xuexi字符集编码后的二进制表示：

'bA' -> 0000001000000011 (十六进制：0x0203)'baB' -> 000000100000000100000100 (十六进制：0x020104)'cd' -> 无法表示，字符集xuexi不包含字符'c'和'd'什么是比较规则

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

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

将两个大小写不同的字符全都转为大写或者小写。

再比较这两个字符对应的二进制数据。

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

一些重要的字符集ASCII字符集

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

'L' -> 01001100(十六进制：0x4C，十进制：76)'M' -> 01001101(十六进制：0x4D，十进制：77)ISO 8859-1字符集

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1。

GB2312字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集，所以在编码方式上显得有些奇怪：

如果该字符在ASCII字符集中，则采用1字节编码。

否则采用2字节编码。

这种表示一个字符需要的字节数可能不同的编码方式称为变长编码方式。比方说字符串'爱u'，其中'爱'需要用2个字节进行编码，编码后的十六进制表示为0xB0AE，'u'需要用1个字节进行编码，编码后的十六进制表示为0x75，所以拼合起来就是0xB0AE75。

我们怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？别忘了`ASCII`字符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。GBK字符集

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

utf8字符集

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

'L' -> 01001100(十六进制：0x4C)'啊' -> 111001011001010110001010(十六进制：0xE5958A)准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。更详细的Unicode和其编码方案的知识不是本书的重点，大家上网查查哈～ MySQL中并不区分字符集和编码方案的概念，所以后边唠叨的时候把utf8、utf16、utf32都当作一种字符集对待。MySQL中支持的字符集和排序规则 MySQL中的utf8和utf8mb4

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

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

utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，若需要使用4字节编码一个字符的情况，比如存储一些emoji表情啥的，就需要制定为utf8mb4。

字符集的查看

查看MySQL中的字符集，使用如下命令：

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

其中CHARACTER SET和CHARSET是同义词，用任意一个都可以

比较规则的查看

查看MySQL中的比较规则，使用如下命令：

SHOW COLLATION [LIKE 匹配的模式];

1、比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。

2、后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。

3、名称后缀意味着该比较规则是否区分语言中的重音、大小

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

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

服务器级别

数据库级别

表级别

列级别服务器级别

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

character_set_server(服务器级别的字符集)

collation_server(服务器级别的比较规则)

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值：

[server]character_set_server=gbkcollation_server=gbk_chinese_ci数据库级别

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则：

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

数据库的创建语句中也可以不指定字符集和比较规则：

CREATE DATABASE 数据库名;

这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则：

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

如果没有指定表的字符集和比较规则，则表t的字符集和比较规则将继承所在数据库charset_demo_db的字符集和比较规则，也就是gb2312和gb2312_chinese_ci。

CREATE TABLE t( col VARCHAR(10));则表t的字符集和比较规则将继承所在数据库charset_demo_db的字符集和比较规则，也就是gb2312和gb2312_chinese_ci。列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则：

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

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

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

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

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

只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。客户端和服务器通信中的字符集 编码和解码使用的字符集不一致的后果

我们知道字符'我'在utf8字符集编码下的字节串长这样：0xE68891，如果一个程序把这个字节串发送到另一个程序里，另一个程序用不同的字符集去解码这个字节串，假设使用的是gbk字符集来解释这串字节，解码过程就是这样的：

首先看第一个字节0xE6，它的值大于0x7F(十进制：127)，说明是两字节编码，继续读一字节后是0xE688，然后从gbk编码表中查找字节为0xE688对应的字符，发现是字符'鎴'

继续读一个字节0x91，它的值也大于0x7F，再往后读一个字节发现木有了，所以这是半个字符。

所以0xE68891被gbk字符集解释成一个字符'鎴'和半个字符。

假设用iso-8859-1，也就是latin1字符集去解释这串字节，解码过程如下：

先读第一个字节0xE6，它对应的latin1字符为。

再读第二个字节0x88，它对应的latin1字符为。

再读第三个字节0x91，它对应的latin1字符为‘。

所以整串字节0xE68891被latin1字符集解释后的字符串就是'‘'

可见，如果对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果，作为人类的我们看上去就像是产生了乱码一样。

上面的这些过程称为字符集的转换，也就是字符串'我'从utf8字符集转换为gbk字符集。

MySQL中字符集的转换

MySQL字符集转换过程

这里有几点需要注意下：

服务器认为客户端发送过来的请求是用character_set_client编码的。假设你的客户端采用的字符集和 character_set_client 不一样的话，就会出现意想不到的结果。

服务器将把得到的结果集使用character_set_results编码后发送给客户端。假设你的客户端采用的字符集和 character_set_results 不一样的话，这就可能会出现客户端无法解码结果集的情况，即看到的是乱码。

character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。比较规则的应用

比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中，所以有时候也称为排序规则。在默认的比较规则gbk_chinese_ci中是不区分大小写的；而gbk_bin是直接比较字符的编码，所以是区分大小写的。所以如果以后大家在对字符串做比较或者对某个字符串列做排序操作时没有得到想象中的结果，需要思考一下是不是比较规则的问题～

总结

文章介绍了字符集即比较规则，并且详细介绍了MySQL的4个级别字符集和比较规则的联系如下：

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

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

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

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。

文章最后还给大家介绍了从发送请求到接收结果过程中发生的字符集转换，了解了乱码的发生原因，以及在实际排序中如果结果不是你想要的，需要先考虑下是不是比较规则设置的有问题，希望这些对大家今后的使用和分析问题有所帮助~

欢迎关注留言，一起学习进步哟~