【肝了三天-建议收藏】实战-万字长文-带你刨析MySQL乱码、字符集和比较规则

最后

还有Java核心知识点+全套架构师学习资料和视频+一线大厂面试宝典+面试简历模板可以领取+阿里美团网易腾讯小米爱奇艺快手哔哩哔哩面试题+Spring源码合集+Java架构实战电子书+2021年最新大厂面试题。
在这里插入图片描述

🍅 1.6、变长字节编码方式

⼀个字符在编码需要的字节数可能不同的编码⽅式称为变长编码⽅式,比如GB2312编码方式,因为GB2312字符集兼容了ASCII字符,所以当一个字符串中有的字符属于ASCII字符集中的字符时,它编码时需要占用的字节数为1,如果字符属于ASCII字符集中没有的,则编码时需要占用2个字节。

举例说明: 比如字符串’日b’，其中’日’不属于ASCII字符集中的字符,需要⽤2个字节进行编码，假设编码后的⼗六进制表示为0xCD2，'u’属于ASCII字符集的字符,需要⽤1个字节进⾏编码，假设编码后的⼗六进制表示为0x15，所以拼合起来就是0xCD215。

🐻 二、常见的字符集

🍑 2.1、常见的字符集类别

ASCII、GB2312、Unicode、GBK等

🍒 (一)ASCII字符集和ASCII编码

ASCII字符集: 全称《美国信息交换标准代码》,主要用于显示现代英语和其它西欧语言,主要包括:可显示字符（英文字母、阿拉伯数值、标点符号)、以及控制字符(回车、换行、退格等特殊字符)。

ASCII编码: 使用一个字节编码,美国定制的交换标准,目的是将ASCII字符集包含的字符转换成计算机能够识别的二进制(0和1),它是最通用的信息交换标准,到目前为止总共定义了128个字符。

ASCII编码缺点: 只能显示26个基本拉丁字母、阿拉伯数目字和英式标点符号，因此只能用于显示现代美国英语（而且在处理英语当中的外来词如naïve、café、élite等等时，所有重音符号都不得不去掉，即使这样做会违反拼写规则）,而且对其他的语言支持力度也不大,所以现在苹果也使用Unicode替换ASCII。

🍓 (二) ISO 8859-1字符集

别名latin1,使用一个字节编码,兼容ASCII字符集,是在ASCII字符集的基础上⼜扩充了128 个⻄欧常⽤字符(包括德法两国的字⺟),共收录256个字符。

🥝 (三)GBXXXX字符集

(1) GB2312字符集:

全称:《信息交换用汉字编码字符集》,刚开始ASCII字符集只包含了阿拉伯数字、字母和一些特殊符号,这个编码只适用于美国和西方的一些国家,而不适用于使用汉字的国家,为了使用汉字的国家也能够和计算机进行沟通,中国国家标准总局发布了标准号为:GB2312的编码格式,它适用于汉字处理、汉字通信等系统之间的信息交换,除了中国大陆使用外,新加坡等地也采用此编码(中国雄起！)。
GB2312字符集共收录了6763个简体汉字,它的收录包括了拉丁字母、日文平假名等在内的682个全角字符,GB2312编码可以将GB2312字符集包含的字符转换成计算机能够识别的二进制0和1。
特点: 因为它兼容了ASCII字符集,所以在编码方式上存在多种情况,如果字符属于ASCII字符集中的字符的话,则编码时需要使用1个字节进行编码,如果不属于ASCII字符集,则需要使用2个字节进行编码。

(2)GBK字符集

全称:《汉字内码扩展规范》,因为GB2312字符集编码只支持简体汉字和一些特殊符号,繁体字和一个特殊简体字都没有收录其中,所以微软针对GB2312做了拓展,在GBK字符集中收录了繁体字,并最早在Window95简体中文版使用。
GBK拓展了GB2312字符集,共收录了两万多个文字,GBK编码可以将GBK字符集收录的字符转换成计算机能够识别的二进制0和1。

(3)GB18030字符集

全称: 国家标准GB 18030-2005《信息技术中文编码字符集》，因为GBK是由微软首先制定的,并不属于国家标准,所以国家为了兼容GBK字符集制定了GB18030字符集,它是中华人民共和国现时最新的内码字集,除了兼容GBK字符集外,还支持GB 13000及Unicode字符集的全部统一汉字。
GB 18030字符集共收录汉字七万多个,并且存储方式采用的是可变长字节编码,每个字可以由1个、2个或4个字节组成。

🥥 (四)Unicode字符集

Unicode字符集的出现: 当计算机出现在全球各地时,为了与计算机进行交流,指定了各种各样的标准如GB232/GBK/GB18030/BIG5的编码方案,如果只在符合对应标准的地区使用则完全没有问题,但是如果通过网络与其他的地区进行交流时,因为各地的编码的标准都不一样,就会在转换中出现"乱码"现象,为了解决这个问题,Unicode字符集应运而生。
Unicode也称为: 统一码/万国码/单一码,它是业界的一种标准,通过它计算机可以实现世界上不同地区数十种文字的显示,2005年Unicode就已经收录了超过十万个字符,现在由Unicode组织进行管理运作,它收录地球上能想到的所有字符，而且还在不断扩充。
Unicode编码系统为表达任意语言的任意字符而设计,它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。
Unicode编码标准现在有三种具体实现,分别是:UTF-8、UTF-16、UTF-32。

(1) UTF-8字符编码:

它是实现了Unicode编码方案的一种可变长字符编码(定长码)，也是一种前缀码。
它可以用来表示Unicode标准中的任何字符，且其编码中的第一个字节仍与ASCII兼容，这使得原来处理ASCII字符的软件无须或只须做少部分修改，即可继续使用。
UTF-8已经逐渐成为电子邮件、网页及其他存储或传送文字的应用中，优先采用的编码。
互联网工程工作小组（IETF）要求所有互联网协议都必须支持UTF-8编码。
UTF-8编码使用一至四个字节为每个字符编码(其中ASCII字符集中的128个字符只占1字节,还有附加符文的拉丁文、希腊文等需要2个字节,其他常用的文字占用3个字节,还有极少数的字符占用4个字节)。

(2) UTF-16字符编码:

它是实现了Unicode编码方案的一种可变长字符编码(定长码)
因为Unicode字符集中收录了很多字符,但是常用的一般不会超过65535个以外的字符,所以出现了UTF-16(2字节=16位)。
UTF-16优点: 它在空间效率上比UTF-32高两倍，因为每个字符只需要2个字节来存储（除去65535范围以外的），而不是UTF-32中的4个字节。
UTF-16缺点: 不兼容ASCII。

(3) UTF-32字符编码:

它是实现了Unicode方案的一种定长字符编码。
它使用4个字节的数字来表示每个字母、符号，或者表意文字(ideograph)。
优缺点: 使用4个字节存储每个字符,效率高,处理速度快(因为不用计算需要几个字节进行存储),但是浪费空间。

🥑 (五)字符集知识拓展

更多关于字符集的知识,可以到《全网最全面、全详细的编码、解码知识！！！》查看。


三: 比较规则

🐗 三、比较规则的产生

通过上文我们知道字符集就是包含了多个字符组合的一个集合,既然存在这样一个集合,那它们的是如何比较不同字符的大小呢?这里就需要我们的第二位主角-比较规则大佬登场了

比较规则: 字符集中字符的一个比较大小的规则,一个字符集中可以存在多个比较规则。最常见的比较方式就是字符映射后的二进制比较,英文名也叫binary collation,如:字符a对应的二进制为0000 0001,字符b对应的二进制为0000 0010,则我们可以说在这种规则下字符b大于字符a。


四: MySQL中的字符集和比较规则

🐒 四、MySQL中的字符集和比较规则

通过上面的介绍,大家应该已经对字符集和比较规则的一些概念有了大致的了解,下面我们就开始结合MySQL来进行真正的实战。

🥕 4.1、MySQL支持的字符集

查询命令: show charset或者character set like '字符集的名称’

(一)、通过下图可知,MySQL数据库默认支持41种字符集,下图关键词的含义:

1、Charset列为字符集的名称

2、Default collation列为字符集对应的默认比较规则(因为一种字符集是可以存在多种比较规则的)

3、Maxlen列则表示这种字符集编码(表示)一个字符时最多需要多少个字节。

(二)、常用的字符集一个字符编码需要的最大字节数

在这里插入图片描述

🥔 4.2、MySQL中utf-8和utf-8mb4的区别

在上面的截图中,可能有人会有疑问,为什么mysql会支持两种utf-8(即utf-8和utf-8mb4)类型的编码?它们之间存在什么区别?

我们在上面介绍Unicode字符集时有说到,其实utf-8,utf-32都是属于Unicode字符集的一种编码方案,utf-8编码方案表示一个字符需要1~4个字节,但是,在实际生活中常用的字符实际上只需要3个字节即可表示,在数据库中,表示字符的大小会影响到数据存取的性能,所以MySQL数据库的设计者就定义类utf-8和utf-8mb4两套方案。

1、utf-8也叫utf-8mb3: 代表一个字符只需要1~3个字符。

2、utf-8mb4: 代表一个字符需要使用1-4个字符,也就是我们平常说的真正的utf-8编码。

区别: 在MySQL数据库中,utf-8是utf-8mb3的别名,它是使用1~3个字节来表示一个字符的,如果需要使用4个字节表示一个字符的,如存储emoji表情包,需要使用utf-8mb4编码方案。

🥭 4.3、MySQL支持的比较规则

查询命令: show collation like ‘比较规则名(可以模糊查询)’

(一)、由下图可知,现在MySQL数据库支持222种比较规则,每种字符集可能存在多种比较规则,它们的规律如下:

1、比较规则都是以字符集的名称开头,如下图2中的utf-32比较规则都属于utf-32字符集。

2、下划线的第二个单词表示的是该比较规则是使用于哪种语言,如:utf-32_spanish_ci则表示使用西班牙语的规则比较,utf-32_general_ci则表示是一种通用的比较规则。

3、结尾的单词表示是否区分不同语言中的重音、大小写等策略,常见的结尾词语含义如下:

在这里插入图片描述

每种字符集对应可能存在多种比较规则,所以在不指定具体比较规则的时候,字符集会有自己默认的一个比较规则,使用show collation查询出来的比较规则中,default列值为yes的表示属于该字符集的默认比较规则,如上图中utf-32的比较规则就是utf32_general_ci。


五: MySQL中的字符集和比较规则的应用

🐫 五、MySQL中的字符集和比较规则的应用

MySQL数据库中,支持4个层级的方式来设置数据库的字符集和比较规则,范围从大到小分别是: MySQL服务器级别、数据库级别、表级别、列级别。

🍈 5.1、查看/设置MySQL服务器级别字符集和比较规则

MySQL保留了以下两个关键字来设置服务器级别的字符集类型和比较规则,具体含义如下:

关键字	具体含义
character_set_server	服务器级别的默认字符集类型
collation_server	服务器级别的默认字符集比较规则

我们可以通过下面的两条命令来查询MySQL服务器级别默认的字符集类型和比较规则

1、查询默认的字符集类型: shwo variabkes like ‘character_set_server’;

2、查询默认的比较规则类型: shwo variabkes like’collation_server’;

如果我们想修改默认的服务器级别的字符集类型和比较规则,可以在MySQL启动的配置文件my.ini或者直接通过set对应的变量达到修改的目的。

方式1: 修改MySQL启动配置文件配置(直接到MySQL安装路径下找my.ini)然后添加以下的属性即可

character_set_server=utf-32 
collation_server=utf-32\_chinese_ci

方式2: 连接数据库后直接通过set设置这两个变量的值,但是这个设置只针对本次客户端连接,如果退出了连接则会还原默认的,下一次连接还是系统之前默认的值。

总结: 上面我们说到,每个字符集都有一个默认的比较规则,它们之间是相互存在联系的,无论我们修改它们两个中的哪一个,另外一个也会跟着改变既:只修改字符集，则比较规则将变为修改后的字符集默认的⽐较规则,只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

在这里插入图片描述

🍉 5.2、查看/设置数据库级别字符集和比较规则

数据库中存在的两个变量(character_set_database、collation_database)用于描述数据库的字符集和比较规则,但是它们都是只读的,没法通过修改这两个变量的值来修改数据的字符集和比较规则,想要修改数据库的字符集和比较规则,需要在创建或者修改数据库的时手动指定character set和collation变量的值,如果不指定的话,则默认使用服务器级别的字符集和比较规则。

指定数据库字符集和比较规则的语法:

// 创建数据库时指定字符集和比较规则
create database 数据库名 [[default] character set 字符集名称] [[default] collate ⽐较规则名称]; 

// 修改数据库的字符集和比较规则
alter database 数据库名 [[default] character set 字符集名称]

在这里插入图片描述

查询数据库字符集和比较规则的语法:

关键字	具体含义
character_set_database	数据库的字符集
collation_database	数据库的⽐较规则

在这里插入图片描述

🍈 5.3、查看/设置表级别字符集和比较规则

在创建或者修改数据表的时候,我们可以根据自己需要去修改数据表对应的字符集和比较规则,但是如果不指定的话,则默认使用数据库级别的字符集和比较规则,具体的语法如下:

// 创建数据表时指定字符集和比较规则
create table 表名 (列的信息) [[default] character set 字符集名称] [collate ⽐较规则名称]] 

// 修改数据表的字符集和比较规则
alter table 表名 [[default] character set 字符集名称] [collate ⽐较规则名称]

在这里插入图片描述

🍇 5.4、查看/设置列级别字符集和比较规则

在创建或者修改表中字段的时候,我们可以根据自己需要去指定表中某些列的字符集和比较规则,但是如果不指定的话,则默认使用表级别的字符集和比较规则,具体的语法如下:

// 创建数据表时指定某列的字符集和比较规则
create table 表名( 列名 字段类型 [character set 字符集名称] [collate ⽐较规则名称], ... );

// 创建数据表时指定某列字符集和比较规则
alter table 表名 modify 列名 字段类型 [character set 字符集名称] [collate ⽐较规则名称];

在这里插入图片描述

🍋 5.5、知道字符集后有什么作用

通过上面的学习,我们可以准确的知道某个数据库、某个表、某个字段对应的字符集类型和比较规则,我们就可以预估出存储在数据库中一条数据占用的大小,这样对我们进行优化有一定帮助(因为网络传输的带宽是有限制的,一次如果查询太多数据,查询的性能会影响,通过预估每条数据占用的存储时间,可以帮助我们进一步确定返回数据条数的限制,是否需要分页等问题)。除此之外,出现乱码得时候我们也能根据对应得编码来进行排查

如demo表中name字段使用的字符集是gbk,存储的数据如下,根据gbk字符集存储数据占用的字节数最大为2可知(上文介绍过,可以使用:show charset命令查看数据支持的字符集和它占用的字节数),在demo表中下面这条数据占用的存储空间为6个字节,如果列的字符集为utf-8则存储空间为9个字节。

在这里插入图片描述

🍊 5.6、小结

根据需要我们进行MySQL的服务级别、数据库级别、数据表级别、列级别进行设置字符集和比较规则,如果是直接使用语句进行修改的话则只对本次连接起作用,退出连接后又恢复到默认的字符集和比较规则,如果想要编程默认的,则可以直接在mysql的配置文件添加对应的变量设置即可。

一个字符集可以存在多种比较规则,所以每种字符集都会有一个默认的比较规则(可以使用show charset命令查看),因为字符集和比较规则是相互联系的,所以只修改字符集，则⽐较规则将变为修改后的字符集默认的⽐较规则,只修改⽐较规则，则字符集将变为修改后的⽐较规则对应的字符集。

服务级别、数据库级别、数据表级别、列级别的字符集和比较规则的范围是从大到小的,所以它们之间有以下的规则:

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


六: MySQL中客户端与服务端之间字符集的使用

🍊 六、MySQL中客户端与服务端之间字符集的使用

👦 6.1、乱码的产生

了解了字符集和编码方案知识后,我们知道不同的字符集有不同的编码,不同的编码方案占用的字节数也是不一样的,如果操作不当,就会出现让人无法理解的后果(乱码),总结起来,出现乱码的情况以下两种:

1、编码过程和解码过程使用的编码方式不一致。试想一下,如果你跟棒子交流说的是汉语,棒子却用韩语字典查询翻译,最后棒子能够懂你的意思?很明显,如果编码和解码使用的方式不同,最后得到的结果会是南辕北辙。

在这里插入图片描述

2、编码/解码对应的字符集不存在对应的字符。比如ASCII编码中只包含有128个字符,没有繁体字,如果你使用ASCII编码方案去解码繁軆字,最后能够得到正确结果?很明显,结果得到的也是我们无法理解的一些字符串。

在这里插入图片描述

👶 6.2、字符集转换

一个字符串从一种字符集类型转换成另外一种字符集。如字符串"菜鸡",编码类型属于utf-8,在utf-8编码中对应的二进制为0000 0001,在gbk编码中对应的二进制为0000 1000,要想转换成gbk字符集中的字符则需要进行以下两步操作:

1、先将字符串"菜鸡"按照gbk字符编码方案解码成为对应的二进制既0000 1000。

2、然后再按照gbk的编码方案编码成对应的字符串,此时得到的"菜鸡"字符串就属于gbk字符集中的一个字符串。

在这里插入图片描述

👧 6.3、MySQL中客户端与服务端之间字符集的转换

在MySQL中,客户端与服务端之间的请求响应最终的体现实际上就是字符序列。客户端将发送的内容根据编码方式编码成对应的字节序列,服务端接收后并进行一系列处理,然后将结果根据对应的编码方式编码并返回给客户端。

在开始讲解客户端与服务端字符集转换之前,需要先了解一些关于MySQL服务端编码的概念,如下

关键字	具体含义
character_set_client	客户端使用的编码类型,用于将接收到的字符串解码,既sql语句是使用什么编码的
character_set_connection	服务器运⾏过程中使⽤的字符集
character_set_results	服务器向客户端返回数据时使⽤的字符集

查询命令: show variables like ‘character_set%’

在这里插入图片描述