一、字符集
思考：计算机是只能==存储和识别二进制==的，但是人能够更好识别的内容是各种符号（文字、图片、表情等），那么计算机是如何实现这机制的呢？
引入：计算机的目标是为了帮助人来解决复杂的运算问题，同时可以帮助存储海量的数据。电脑存储二进制和人喜欢看符号之间的矛盾，其实可以使用一套对应的规则来实现转换，这种规则就是字符集。
1. 字符集概念
定义：字符(Character)是各种文字和符号的总称 ，字符集(Character set)是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同 。
- 字符集的产生：计算机只能存储和识别二进制，但是人对自己的语言文字是比较熟悉的，这个时候就产生了一组矛盾：怎么能够让计算机存储的是二进制，而人看的时候是自己的文字符号呢？这个时候就需要定义一个二进制与符号之间的规范，把对应的字符集全部对应上二进制之后就行成了一套字符集编码规范。
- 各个国家都有自己的文字符号（尤其是亚洲），然后每个不同的符号出现就要设定一套新的字符集。常见的字符集如下：
- ASCII：美国信息交换标准代码，也是基于拉丁字母的一套电脑编码系统，使用单字节编码，即一个字节表示字符（一个字节最大值是256，足够表示所有拉丁字符，ASCII实际采用7位128个字符）
- latin1：拉丁文字符集，ISO-8859-1的别名 ，能够向下兼容ASCII
- GB2312：信息交换用汉字编码字符集 ，是中国1981年的一套国标规范
- GBK：汉字内码扩展规范（1995年），使用双字节编码方案，即两个字节表示一个字符（汉字很多超过5000个）
- 随着互联网的出现，软件需要满足全球的需求，也就需要满足各种符号的管理，因此国家标准就显得不够用了，这时就需要一个统一的字符集来将所有的字符集纳入其中
- Unicode：万国码（统一码），使用统一的编码方式来解决传统的局限，1994年出现
- UTF-8：8-bit Unicode Transformation Format（万国码） ，针对Unicode的可变长度字符编码，采用1-6个字节编码Unicode字符（目前通用编码规则）
总结
- 字符集是一套字符在计算机中编码规范的集合，能够完美解决计算机与人对于符号的识别方式的不同
- 随着各种符号的产生和全球化的进程，衍生了更复杂但是通用的字符集Unicode和utf-8
- 因为utf-8可以在保证所有字符集都可以正常显示的情况下还实现了有效的资源利用，所以utf-8也就成为了现在软件使用字符集的标准。
思考：MySQL是一种C/S结构的软件，指令是在客户端编辑然后发送到服务端执行。是如何保证客户端与服务端之间的通信是彼此能正确理解的呢？
引入：任何客户端和服务端的交互都存在数据传输问题，而数据传输和最终解析只需要共同遵循一个明确的编码规则即可。因此，MySQL的客户端与服务端要正确通信，只要保证二者采用统一的字符集即可。
2. MySQL字符集详解
定义：MySQL在服务端内置了大量的字符集规范，以保证各种客户端传输的数据都能够被正确的解析和存储。
- 查看MySQL服务端所支持的字符集规范：show character set;
mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.48 sec)
#字段说明
#Charset：字符集名字
#Description：字符集描述
#Default collation：默认校对集（比较的方式）
#Maxlen：所占用的存储最大字节数
- 服务器端支持大量字符集格式，客户端只需要告知服务端对应的数据字符集即可。但是客户端的数据字符集是不确定的，服务器怎么能够知道呢？在服务器端有一个规范，是系统去进行数据操作的规则，这些规范是内部变量控制。这种规范的默认值是在安装MySQL的时候确定的，可以通过以下命令查看：show variables like 'character%';
mysql> show variables like 'character%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | gbk | #默认客户端字符集
| character_set_connection | gbk | #默认连接转换字符集
| character_set_database | latin1 | #当前所在数据库字符集
| character_set_filesystem | binary | #默认文件系统字符集
| character_set_results | gbk | #默认服务器提供数据的字符集
| character_set_server | latin1 | #默认服务器端的字符集
| character_set_system | utf8 | #默认DBMS系统字符集
| character_sets_dir | D:\server\mysql\share\charsets\ | #字符集路径
+--------------------------+---------------------------------+
8 rows in set, 1 warning (0.60 sec)
- 以上字符集控制中，与客户端打交道的有三个：
- character_set_client：客户端提供给服务器端的数据字符集
- character_set_connection：为低层字符集和校对集使用（非表操作）
- character_set_results：服务端提供给客户端的数据字符集
- 如何修改这些数据，从而让服务器能够与客户端正确通信呢？
- set character_set_client = 客户端字符集; ：能够让服务端正确接收客户端数据
- set character_set_results = 客户端字符集;：能够让服务端给客户端提供正确数据
- set character_set_connection = 客户端字符集;：能够允许客户端发送其他非表操作指令时服务器端正确准备数据
mysql> show variables like 'character%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\server\mysql\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> set character_set_client = utf8;
Query OK, 0 rows affected (0.11 sec)
mysql> set character_set_results = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> set character_set_connection = utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\server\mysql\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set, 1 warning (0.00 sec)
- MySQL数据库也考虑到了用户每次对应客户端操作都需要操作好几次，比较麻烦，所以提供了一条简单指令：set names 客户端字符集;，该指令就等价于第4点三条指令操作。
mysql> show variables like 'character%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\server\mysql\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character%';
+--------------------------+---------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\server\mysql\share\charsets\ |
+--------------------------+---------------------------------+
8 rows in set, 1 warning (0.00 sec)
总结
- 字符集是各种软件都需要考虑的第一件事情，只有确保了字符集的正确使用，才能保证数据的有效通信
- 字符集这块的关系是服务器提供各类字符集的支持，而客户端最终选择要通信的字符集方式
- MySQL是一种支持多字符集的方式，在服务器端由三个变量character_set_client、character_set_connection和character_set_results来控制与客户端的通信字符集，客户端在连接上服务器之后，第一件事就是要通知服务器端客户端的字符集，就是修改三个系统变量
- 而且MySQL提供了一种简便改变服务器端对客户端字符集的调整方式：set names 客户端字符集。这样就可以保证服务器对客户端字符集的掌握，从而在进行数据交互时，服务器能够很好的保障与客户端的数据字符集一致，不会出现乱码。
二、校对集
思考：数据在进行查询操作的时候，必要时需要对数据进行排序处理，使得数据在查看的时候能够有一定的顺序性。那么数据为什么能够实现排序呢？
引入：数据进行排序的本质是参照某个指定的规则能够进行比较，在MySQL中，这种比较的方式是通过校对集来完成的。
1. 校对集概念
定义：校对集，即某个指定==字符集==下进行比较的==校对规则==的集合。
- 在MySQL中，校对集分为三种格式
- _ci：case insensitive，大小写不敏感，即不区分大小写（针对字母:默认校对集）
- _cs：case sensitive，大小写敏感，即区分大小写（针对字母）
- _bin：binary，二进制比较，本质也区分大小写
- 校对集是根据字符集设定，并非所有的字符集都支持三种校对集，事实上，字符集通常支持的是大小写不敏感的（_ci）和二进制比较（_bin）。在MySQL中可以通过show collation;命令来查看所有支持的校对集
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| gb2312_chinese_ci | gb2312 | 24 | Yes | Yes | 1 |
| gb2312_bin | gb2312 | 86 | | Yes | 1 |
| gbk_chinese_ci | gbk | 28 | Yes | Yes | 1 |
| gbk_bin | gbk | 87 | | Yes | 1 |
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
总结：校对集是数据比较的规则，分为三种：区分大小写（_cs），不区分大小写（_ci），和二进制比较（_bin本质也区分大小写）。而实际上常用的比较方式为_ci和_bin，因为比较的时候通常不涉及到大小写，实际数据比较的话也会尽量以数字作为比较对象（不存在大小写问题）
思考：在前面的学习中我们没有涉及到校对集，是不是有专门的SQL指令来控制呢？
引入：校对集其实是一种较小的规则，并不是需要特定的SQL指令来控制，只需要在进行数据库、数据表甚至字段的时候通过具体的选项来指定即可。
2. 校对集设定
定义：校对集设定就是在进行数据库、表甚至字段的时候主动或者被动的使用校对集关键字设定就可以达到目的。
- 指定数据表校对集，数据表的校对集可以在创建表的时候利用表选项指定：create table 表名字(字段名字 字段类型) collate [=] 校对集规则;(如果没有特别指定那么沿用数据库默认的校对集（数据库如果没有指定沿用DBMS系统默认校对集）)
mysql> create table my_collation1(
-> name varchar(1) #一个字符
-> )charset utf8 collate utf8_bin; #指定为二进制校对规则
Query OK, 0 rows affected (0.87 sec)
- 查看数据表的校对集：可以通过查看表的创建语句来实现校对集查看
mysql> show create table my_collation1\G
*************************** 1. row ***************************
Table: my_collation1
Create Table: CREATE TABLE `my_collation1` (
`name` varchar(1) COLLATE utf8_bin DEFAULT NULL #字段默认使用表的校对集
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin #指定的校对集
1 row in set (0.39 sec)
注意：校对集可以针对数据字段来设定，但是通常我们不会这么做，会让整表统一校对集（字符集也一样）
- 数据表创建的时候如果不指定校对集，那么会自动使用字符集对应的默认校对集（通常是_ci）
mysql> create table my_collation2(
-> name varchar(1)
-> )charset utf8;
Query OK, 0 rows affected (0.69 sec)
mysql> show create table my_collation2\G
*************************** 1. row ***************************
Table: my_collation2
Create Table: CREATE TABLE `my_collation2` (
`name` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
注意：校对集如果是默认的，那么将不会特别显示出来，当前utf8字符集默认的校对集就是utf8_collate_ci
- 如果校对集在表中不进行设定，那么可以就会采用数据库默认的校对集来实现。数据库设定校对集可以在创建数据库的时候指定对应的库选项即可：create database 数据库名字 [charset 字符集设定] [collate 校对集设定];
mysql> create database my_database1 charset utf8 collate utf8_bin;
Query OK, 1 row affected (0.39 sec)
mysql> show create database my_database1\G
*************************** 1. row ***************************
Database: my_database1
Create Database: CREATE DATABASE `my_database1` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */
1 row in set (0.00 sec)
另外，数据库创建后，在数据库对应的文件夹下有一个db.opt文件里，里面就有字符集和校对集：mysql/data/my_database1/db.opt
default-character-set=utf8
default-collation=utf8_bin
总结：字符集的设定可以通过数据库、数据表和字段（不用）使用collate 校对集规则设定，而且可以在数据库文件db.opt来查看数据库的，show create table 表名来查看数据表的（默认的不显示）。如果在数据库、表和字段都进行设计了，最终真实使用的时候越靠近数据的才会被使用，也就是优先级是 字段 > 数据表 > 数据库的。需要注意的是一般不需要额外设定校对集，使用字符集默认对应的校对集即可。
思考：校对集的比较方式有三种，到底是如何体现的呢？
引入：校对集的三种方式并不会一次都使用，基本上使用的都是默认的不区分大小写（_ci），如果有具体需求需要针对数据表使用对应区分大小写的校对集，应该在创建表的时候指定好。
3. 校对集应用
定义：校对集的使用就是通过特定的==进行数据比较==的指令来触发校对集规则。
- 为了让数据比较指令能够有效执行，先往两张校对集表中插入对应的数据
mysql> insert into my_collation1 values('a'),('b'),('A'),('B');
Query OK, 4 rows affected (0.12 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into my_collation2 values('a'),('b'),('A'),('B');
Query OK, 4 rows affected (0.08 sec)
Records: 4 Duplicates: 0 Warnings: 0
注意：此时表中的数据使用普通查询后就是按照具体插入顺序显示数据（此时不涉及到校对集）
mysql> select * from my_collation1;
+------+
| name |
+------+
| a |
| b |
| A |
| B |
+------+
4 rows in set (0.03 sec)
mysql> select * from my_collation2;
+------+
| name |
+------+
| a |
| b |
| A |
| B |
+------+
4 rows in set (0.00 sec)
- 触发校对集规则的SQL指令：order by 字段 ASC|DESC; 对某个字段进行升序（ASC默认）或者降序（DESC）实现：表my_collation1使用utf8_bin校对集，表my_collation使用utf8_general_ci默认校对集
mysql> select * from my_collation1 order by name; #默认升序
+------+
| name |
+------+
| A |
| B |
| a |
| b |
+------+
4 rows in set (0.06 sec)
#二进制比较的是最终存储的二进制编码，A转换成ASCII码为65，B为66，a为97，b为98
mysql> select * from my_collation2 order by name; #默认升序
+------+
| name |
+------+
| a |
| A |
| b |
| B |
+------+
4 rows in set (0.00 sec)
注意：校对集必须在表中数据插入前设定好才会有效，如果是先有数据，然后修改表对应的校对集，那么数据也不能按照新的校对集规则来进行比较。
总结
- 校对集就是根据三种规则进行数据比较
- 必须通过数据比较指令Order by（排序）才会激活校对集工作
- 校对集的指定必须在数据插入到表之前设定好，不能在有数据后对数据表进行调整。
