出现无效字符_mysql基础篇02 字符集&校对集

一、字符集

1、字符集：charset或者character set，是各种文字和符号在计算机中的总称。

2、常见字符集

* ASCII：美国信息交换标准码，一般英文符号，一个字节存储

* latin1：拉丁文字符集，一个字节存储，ISO-8859-1的别名 ，能够向下兼容ASCII

* GB2312：信息交换用汉字编码字符集 ，是中国1981年的一套国标规范，2个字节存储

* GBK：汉字内码扩展规范（1995年），两个字节表示表示（汉字很多超过5000个）

* Unicode：万国码（统一码），使用统一的编码方式来解决传统的局限，1994年出现

* UTF-8：8-bit Unicode Transformation Format（万国码） ，8字节万国码，兼容GBK和ASCII；针对Unicode的可变长度字符编码，采用1-6个字节编码Unicode字符（目前通用编码规则）。建议使用UTF-8字符集进行数据存储（MySQL8中建议使用utf8mb4）

3、字符集是指定字符的存储和读取的规范

* 指定的字符集存储需要使用对应的字符集读取

* 错误的字符集存储或者读取都会产生乱码

4、客户端存储数据原理

A(客户端录入数据<br>数据自带字符集)-->B[发送服务端]

A-->|提前告知服务端客户端数据字符集<br>如不告知-服务器使用默认设置|C

B-->C

C[服务端接收数据]-->D[执行存储操作]

D-->E[转码为目标字符集]

E-->F((存储))

5、客户端读取数据原理

A(客户端录入获取指令)-->B[发送服务端]

A-->|提前告知服务端客户端数据字符集<br>如不告知-服务器使用默认设置|C

B-->C[服务器接收请求]

C-->D[执行获取指令<br>按照数据存储本身字符集获取]

D-->E[转成客户端字符集数据]

E-->F[返回客户端]

F-->G((客户端解析<br>只能按照自己的字符集解析))

二、MySQL字符集

*MySQL内部对于数据实际存储的字符集（服务器端）

*查看MySQL支持的所有字符集；show charset;

1、MySQL服务端数据存储的字符集依赖各个对象设置

* DBMS：设置最广，一旦设置所有对象都可以依赖，但是优先级最低

* DB：针对数据库内的所有表，优先级高于DBMS，可以继承DBMS（一般在数据库层设置）

* Table：针对当前表的设置，优先级高于DB，可以继承DB

* Field：针对当前字段设置，优先级高于Table，可以继承Table，优先级最高

2、通常字符集的设置都是围绕数据表（现在都在数据库层），不会到具体字段

3、建议使用UTF8字符集存储数据（MySQL8以后建议使用UTF8MB4）

4、MySQL服务端支持各种字符集，并且能够进行各种字符集转换

5、客户端存储数据到服务端原理

* 客户端告知服务端客户端的字符集

* 服务端按照客户端指定的字符集接收数据（如果没有指定，使用默认，可能出现乱码）

* 服务端按照实际存储表对应的字符集进行转换

* 服务端存储数据

6、客户端读取服务端数据原理

* 客户端告知服务端客户端的字符集

* 服务端按照客户指定的指令从数据库读取原始字符集数据

* 服务端按照客户端的需求将数据进行字符转换

* 服务端发送目标数据给客户端

* 客户端按照自己的字符集进行解析

三、乱码问题解决

*乱码：指数据不能按照正确的字符集进行存储或者解析

* 乱码原因1：数据在存储的时候已经变成乱码

* 客户端字符集与服务端解析字符集不一致

* 读取时想转成其他字符集均会错误

* 乱码原因2：数据存储时正确，但是读取时解析成错误字符集

* 客户端能解析的字符集与服务器提供的字符集不一致

* 乱码解决方案：不论存储还是读取，都提前告知服务器当前客户端的字符集

1、乱码的本质原因就是客户端与服务端的字符集不一致导致

* 客户端存储数据的时候服务端没有正确理解（服务端按照默认的存储，存储的就是乱码）

* 客户端读取的时候没有正确告知服务端（服务端按照默认的提供）

2、解决乱码问题的方案：保证服务端正确理解客户端的字符集

* set names 客户端字符集；set names gbk;适用所有客户端，与客户端支持的字符集一致。

* 在任何数据操作之前（尤其是写数据，包括结构）

四、字符集设置原理

**字符集设置原理**：服务器端正确保障对客户端的数据识别

* MySQL服务端提供了变量来记录客户端的字符集

* MySQL对应的存储字符集的变量可以修改

*MySQL字符集控制是在服务端内部通过变量连接（针对每个独立的客户端）

* `set names 字符集`就是对变量的修改，总共有三个

* character_set_client：客户端提供的数据的字符集

* character_set_results：客户端需要服务端提供的数据的字符集

* character_set_connection：连接使用的字符集，部数据操作

1、查看系统内部存储这些记录字符集的信息

show variables like 'character_set%'; #%表示通配符，匹配后续所有不确定的数据

2、修改客户端字符集变量，保证数据正常存进服务端

set character_set_client = gbk;

3、修改客户端解析字符集变量，保证数据正常被客户端查看

set character_set_results = gbk;

4、使用set names 字符集批量修改，本质有三个变量被修改，保证客户端被服务端正确理解，同时客户端也能正确解析

set names gbk;

五、校对集

**校对集**：collate/collation，即数据比较时对应的规则。

* 校对集是数据比较的规则，校对集依赖字符集，每个字符集有多种校对规则

**查询有多少校对集：show collation;

* 校对集的校对方式分为三种

* 大小写不敏感：_ci，case insensitive（不区分大小写）；A 与 a 是相同的，不存在谁大谁小（系统会转换成一种）

* 大小写敏感：_cs，case sensitive（区分大小写）；A 与 a 有大小关系，所以不同（存储数值）

* 二进制比较：_bin，binary（区分大小写）A的二进制是01000001，a的二进制是01100001，二进制按位比较，所以不同。

* 校对集是在进行数据比较的时候触发。

六、校对集设置

**校对集设置**：在创建数据表的时候创建校对规则

* 校对规则可以在MySQL四层对象设计

* DBMS：系统配置

* DB：数据库指定（库选项；常见；create database db_4 charset utf8mb4 collate utf8mb4_bin;

* Table：表指定（表选项）；create table t_4(id int, name varchar(10))charset utf8mb4 collate utf8mb4_bin;

* Field：字段指定（字段选项，一般不用）；create table t_5(id int,name varchar(10) collate utf8mb4_bin)charset utf8mb4;

* 校对集从Field到DBMS继承；优先级Field最高

* 每个校对集都有字符集对应的默认规则，校对集依赖字符集，且每个字符集都有默认的校对集（一般情况不需要设置）

七、校对集应用

**校对集应用**：触发校对规则的使用。

* 校对集的应用通常是通过**数据比较**触发：`order by 字段`。

* 数据表中数据一旦产生，校对集的修改就无效

*校对集通常使用字符集默认校对集，如果需要进行额外的比较应用（通常是区分大小写），那么需要在建表的时候设定好目标校对规则。

1、创建校对规则数据表并插入数据

# 创建默认校对规则表（不区分大小写）

create table t_4(

name varchar(1)

)charset utf8mb4;

insert into t_4 values('B');

insert into t_4 values('A');

insert into t_4 values('b');

insert into t_4 values('a');

# 创建二进制校对规则（区分大小写）

create table t_5(

name varchar(1)

)charset utf8mb4 collate utf8mb4_bin;

insert into t_5 values('B');

insert into t_5 values('A');

insert into t_5 values('b');

insert into t_5 values('a');

2、触发校对：排序 order by

select * from t_4 order by name; # 升序

select * from t_5 order by name;

3、数据已经存在的表重新修改校对规则无效

alter table t_5 collate utf8mb4_general_ci;