Mysql怎样运行的学习笔记之01字符集与比较规则

01字符集与比较规则

1、编码

在计算机底层，比如'liu'在计算机并不是文字的形式，而是一串二进制数字，例如：
'a'	->	00000001	(⼗六进制：0x01)
'b'	->	00000010	(⼗六进制：0x02)
'A'	->	00000011	(⼗六进制：0x03)
'B'	->	00000100	(⼗六进制：0x04)
人类---计算机之间的交互(互相不认识)
从我们可以看到的文字到 0、1 的映射称为编码，反过来从 0、1 到文字叫解码。这个就是编码的含义。

2、字符

字符（Character）是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数字等。

3、字符集

字符的集合，叫做字符集。ASCII、UTF-8、UTF-16、UTF-32就是字符集。

（1）ASCII

因为计算机只能处理数字，如果要处理文本，就必须先把文本转换为数字才能处理。最早的计算机在设计时采用 8 个比特（bit）作为一个字节（byte），所以，一个字节能表示的最大的整数就是 255（二进制11111111=十进制 255），0 - 255 被用来表示大小写英文字母、数字和一些符号，这个编码表被称为 ASCII 编码，比如大写字母 A 的编码是 65，小写字母 z 的编码是 122。

（2）GB2312

收录了汉字以及拉丁字⺟、希腊字⺟、⽇⽂平假名及⽚假名字⺟、俄语⻄⾥尔字⺟。其中收录汉字6763个，其他⽂字符号682个。同时这种字符集⼜兼容
ASCII字符集，所以在编码⽅式上显得有些奇怪：
如果该字符在ASCII字符集中，则采⽤1字节编码。
否则采⽤2字节编码。

（3）GBK

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

（4）UTF

编码------Unicode转换格式（Unicode Transformation Formats），即UTF。
UTF-8：每个字3个码元（单字节码元），每个字共3个字节
UTF-16：每个字1个码元（双字节码元），每个字共2个字节
UTF-32：每个字1个码元（四字节码元），每个字共4个字节

Unicode

因为计算机开始是美国人发明的，没考虑其他国家的字符，所以，中国制定了GB2312编码，用来把中文编进去。类似的日本、韩国其他国家都有这些问题，为了统一所有文字的编码，就产生了Unicode。
Unicode 还兼容了很多老版本的编码规范，例如刚刚讲过的 ASCII 码。

码点

我们国家的每一个人都对应唯一的一个身份证号，而 Unicode 也为每个字符发了一张身份证，这张“身份证”上有一串唯一的数字 ID 确定了这个字符。
这串数字在整个计算机的世界具有唯一性，Unicode 给这串数字 ID 起了个名字叫［码点］。

码元

这个“Unicode 转换格式”是为了解决“码点”在计算机存储方式而设计的。

“码点”经过映射后得到的二进制串的转换格式单位称之为“码元”（Code Unit）。“码点”就是一串二进制数，“码元”就是切分这个二进制数的方法。

举个例子，如果有一个字符的码点二进制表示有 n 字节（n*8 个二进制数），其码元为 8 位（1 个字节），那么其拥有码元 n 个。

4、MySQL中⽀持的字符集

我们上边说utf8字符集表示⼀个字符需要使⽤1～4个字节，但是我们常⽤的⼀些字符使⽤1～3个字节就可以表示了。⽽在MySQL中字符集表示⼀个字符所⽤最⼤字节⻓度在某些⽅⾯会影响系统的存储和性能，所以设计MySQL的⼤叔偷偷的定义了两个概念：
	utf8mb3：阉割过的utf8字符集，只使⽤1～3个字节表示字符。
	utf8mb4：正宗的utf8字符集，使⽤1～4个字节表示字符。
有⼀点需要⼤家⼗分的注意，在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使⽤1~3个字节来表示⼀个字符，如果⼤家有使⽤4字节编码⼀个字符的情况，⽐如存储⼀些emoji表情啥的，那请使⽤utf8mb4。

字符集查看

mysql>SHOW CHARSET;
+----------+---------------------------------+---------------------+--------+
|	Charset|	  Description                |	Default	collation  | Maxlen	|
+----------+---------------------------------+---------------------+--------+
|	utf8   |	UTF-8	Unicode              |	utf8_general_ci    |    3	|
...
|	ucs2   |	UCS-2	Unicode              |	ucs2_general_ci    |    2	|
| utf8mb4  |	UTF-8	Unicode              |	utf8mb4_general_ci |    4	|
...
+----------+---------------------------------+---------------------+--------+
41	rows	inset	(0.01	sec)
可以看到，我使⽤的这个MySQL版本⼀共⽀持41种字符集，其中的Default collation列表示这种字符集中⼀种默认的⽐较规则。⼤家注意返回结果中的最后⼀列Maxlen，它代表该种字符集表示⼀个字符最多需要⼏个字节。为了让⼤家的印象更深刻，我把⼏个常⽤到的字符集的Maxlen列摘抄下来，⼤家务必记住：
|字符集名称| Maxlen|
|  ascii |   1   |
| latin1 |   1   |
| gb2312 |   2   |
|   gbk  |   2   |
|  utf8  |   3   |
| utf8mb4|   4   |

比较规则查看

mysql>SHOW COLLATION LIKE 'utf8\_%';
+------------------------+----------+-------+-----------+--------------+-------------+
|	    Collation        |	Charset	|	Id 	|	Default	|	Compiled   |	Sortlen	 |
+------------------------+----------+-------+-----------+--------------+-------------+
|	  utf8_general_ci    |   utf8   | 	33	|	 Yes  	|	   Yes   	|     1	     |
|	  utf8_spanish_ci    |	 utf8  	|  199	|        	|	   Yes     	|  	  8      |
+------------------------+----------+-------+-----------+---------------+------------+
27	rows	inset	(0.00	sec)
utf8_polish_ci表示以波兰语的规则⽐较，utf8_spanish_ci是以⻄班⽛语的规则⽐较，utf8_general_ci
是⼀种通⽤的⽐较规则。
| 后缀 |       英⽂释义      |      描述     |
| _ai | accent insensitive |   不区分重⾳   |
| _as | accent sensitive   |    区分重⾳    | 
| _ci |  case insensitive  |   不区分⼤⼩写  | 
| _cs |  case sensitive    |    区分⼤⼩写   | 
| _bin|      binary        | 以⼆进制⽅式⽐较 |  

5、MySQL有4个级别的字符集和⽐较规则

服务器级别
数据库级别
表级别
列级别

6、客户端和服务器通信中的字符集

我们知道字符'我'在utf8字符集编码下的字节串⻓这样：0xE68891，如果⼀个程序把这个字节串发送到另⼀个程序⾥，另⼀个程序⽤不同的字符集去解码这个字节串，假设使⽤的是gbk字符集来解释这串字节，解码过程就是这样的：
	1. ⾸先看第⼀个字节0xE6，它的值⼤于0x7F（⼗进制：127），说明是两字节编码，继续读⼀字节后是0xE688，然后从gbk编码表中查找字节为0xE688对应的字符，发现是字符'鎴'
	2. 继续读⼀个字节0x91，它的值也⼤于0x7F，再往后读⼀个字节发现⽊有了，所以这是半个字符。
	3. 所以0xE68891被gbk字符集解释成⼀个字符'鎴'和半个字符。

7、MySQL中字符集的转换

mysql> show VARIABLES like '%char%';
+-----------------+--------------------------------------------------------------------+
|           Variable_name        |                        Value                        |
+--------------------------------+-----------------------------------------------------+
|       character_set_client     |                        utf8mb4                      |
|     character_set_connection   |                        utf8mb4                      |
|      character_set_database    |                        utf8mb4                      |
|     character_set_filesystem   |                        binary                       |
|      character_set_results     |                        utf8mb4                      |
|       character_set_server     |                        utf8mb4                      |
|       character_set_system     |                         utf8                        |
|character_sets_dir|C:\ProgramFiles\phpstudy_pro\Extensions\MySQL5.7.26\share\charsets\|
+------------------+-------------------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

character_set_client:服务器解码请求时使⽤的字符集
character_set_connection:服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results:服务器向客户端返回数据时使⽤的字符集

8、总结

1. 字符集指的是某个字符范围的编码规则。
2. ⽐较规则是针对某个字符集中的字符⽐较⼤⼩的⼀种规则。
3. 在MySQL中，⼀个字符集可以有若⼲种⽐较规则，其中有⼀个默认的⽐较规则，⼀个⽐较规则必须对应⼀个字符集。
4. 查看MySQL中查看⽀持的字符集和⽐较规则的语句如下：
	SHOW	(CHARACTER	SET|CHARSET)	[LIKE	匹配的模式];
	SHOW	COLLATION	[LIKE	匹配的模式];
5. MySQL有四个级别的字符集和⽐较规则
	服务器级别
		character_set_server表示服务器级别的字符集，collation_server表示服务器级别的⽐较规则。
	数据库级别
		创建和修改数据库时可以指定字符集和⽐较规则：
		CREATE	DATABASE	数据库名
			[[DEFAULT]	CHARACTER	SET	字符集名称]
			[[DEFAULT]	COLLATE	⽐较规则名称];
		ALTER	DATABASE	数据库名
			[[DEFAULT]	CHARACTER	SET	字符集名称]
			[[DEFAULT]	COLLATE	⽐较规则名称];
		character_set_database表示当前数据库的字符集，collation_database表示当前默认数据库的⽐较规则，这两个系统变量是只读的，不能修改。如果没有指定当前默认数据库，则变量与相应的服务器级系统变量具有相同的值。
	表级别
		创建和修改表的时候指定表的字符集和⽐较规则：
		CREATE	TABLE	表名	(列的信息)
			[[DEFAULT]	CHARACTER	SET	字符集名称]
			[COLLATE	⽐较规则名称]];
		ALTER	TABLE	表名
			[[DEFAULT]	CHARACTER	SET	字符集名称]
			[COLLATE	⽐较规则名称];
	列级别
		创建和修改列定义的时候可以指定该列的字符集和⽐较规则：
		CREATE	TABLE	表名(
			列名 字符串类型	[CHARACTER	SET	字符集名称]	[COLLATE	⽐较规则名称],
			其他列...
		);
		ALTER	TABLE	表名	MODIFY	列名 字符串类型	[CHARACTER	SET	字符集名称]	[COLLATE	⽐较规则名称];
6. 从发送请求到接收结果过程中发⽣的字符集转换：
	客户端使⽤操作系统的字符集编码请求字符串，向服务器发送的是经过编码的⼀个字节串。
	服务器将客户端发送来的字节串采⽤character_set_client代表的字符集进⾏解码，将解码后的字符串再按照character_set_connection代表的字符集进⾏编码。
	如果character_set_connection代表的字符集和具体操作的列使⽤的字符集⼀致，则直接进⾏相应操作，否则的话需要将请求中的字符串从character_set_connection代表的字符集转换为具体操作的列使⽤的字符集之后再进⾏操作。
	将从某个列获取到的字节串从该列使⽤的字符集转换为character_set_results代表的字符集后发送到客户端。
	客户端使⽤操作系统的字符集解析收到的结果集字节串。
⼀般情况下要使⽤保持这三个变量的值和客户端使⽤的字符集相同。
SET	character_set_client	=	字符集名;
SET	character_set_connection	=	字符集名;
SET	character_set_results	=	字符集名;
7. ⽐较规则的作⽤通常体现⽐较字符串⼤⼩的表达式以及对某个字符串列进⾏排序中。