MySQL字符集初探

官网信息

首先，推荐官网文档中关于字符集、排序规则、统一编码的章节内容，更为细致的进行了解：
“Chapter 10 Character Sets, Collations, Unicode”:
https://dev.mysql.com/doc/translation-refman/8.0/en/charset.html

基本情况

在体验MySQL的过程中，了解到其与其他数据库如Oracle database，在字符集的行为上有所不同，因此进一步了解了MySQL字符集相关的设定。本文涉及都是MySQL 8.0，其他版本需要根据其他版本文档与实际，另外collation(排序规则)与character set是结合在一起的，但本文主要涉及character set。

登录数据库后，通过命令可以查看当前会话的字符集情况：

show variables like '%character%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | gbk                                       |  
| character_set_connection | gbk                                       |
| character_set_database   | utf8mb4                                   | 
| character_set_filesystem | binary                                    |
| character_set_results    | gbk                                       | 
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | D:\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+-------------------------------------------+

character_set_client		//描述SQL语句是以何种字符集从客户端发出的。 
character_set_connection	//描述MySQL服务应该以何种字符集对接收到的SQL语句进行翻译转化
character_set_database		//描述default database的字符集，如果没有default database存在，则和character_set_server一致
character_set_filesystem	//文件系统字符集。用于解释转化与文件名相关的字符串常量，
								比如LOAD DATA、SELECT ... INTO OUTFILE、LOAD_FILE()。
								在文件被尝试open之前，文件名会从character_set_client转为character_set_filesystem。
								默认是binary，意谓着没有转换。如果多字节字符的文件名被允许使用，
								则该变量要使用合适的字符集。
character_set_results		//描述MySQL服务在将查询结果集返回给客户端之前，
								需要将相关数据翻译转化为何种字符集。
结果数据包括列名、列值、错误信息等。
character_set_server		//MySQL server字符集。    
character_set_system		//被server用于存储标识符(可以理解为metadata/元数据)，这个字符集总是utf8。

另外，还有collation用于数据之间的比较排序，即排序规则:

mysql> show variables like '%collation%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | gbk_chinese_ci     |
| collation_database            | utf8mb4_0900_ai_ci |
| collation_server              | utf8mb4_0900_ai_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+

如utf8mb4_0900_ai_ci排序规则:characterset_0900(Unicode9.0规范>_ai(不区分音调)_ci(不区分大小写)

Collation 后缀含义
Suffix	Meaning
_ai		Accent-insensitive
_as		Accent-sensitive
_ci		Case-insensitive
_cs		Case-sensitive
_ks		Kana-sensitive
_bin	Binary

会话级变量在连接建立时初始化，可以在后续操作中修改。当客户端与服务端连接后，客户端会说明它想要与服务端通讯的字符集，然后，服务端会使用这个信息去设置以下系统变量以及排序规则：character_set_client, character_set_results, character_set_connection
这个效果与使用set names命令一致。

MySQL的字符集支持，可以使用各种字符集(character set)存储数据，并根据各种排序规则(collation)执行比较。MySQL server(8.0)默认的character set与 collation分别是utf8mb4和utf8mb4_0900_ai_ci，可以在各种级别指定character set，如server,database,table,column,string literal。

字符集转化行为

一个“连接”是一个客户端程序发起的，开始一个会话与服务端进行交互。客户端通过会话连接向服务端发送SQL语句，如查询。服务端通过会话连接发送响应给客户端，如结果集或错误信息。
关于客户端连接中字符集与排序规则处理的几个问题：

当语句离开客户端的时候是什么字符集？
	服务端认为这些客户端发送过来的内容是character_set_client设定的字符集。

在服务器端接收到这些语句后，应该将这些内容变换为哪种字符集？
	服务端使用character_set_connection和collation_connect系统变量去确定。
	服务端将语句从character_set_client的字符集转换为character_set_connection。
	有个例外是字符串常量是可以以introducer来指定的(意谓着指定后是不根据前面所述规则进行转换)。

Literal字符集设置及乱码情况表现，可以体会一下:

在服务器要把查询结果传输给客户端时，要使用哪种字符集?
	character_set_results系统变量指定了返回给客户端内容的字符集，内容包括列的值，结果元信息，如列名、错误信息等 。
	让服务器端不要进行字符集转换，将character_set_results设为null或binary。

更换客户端相关字符集后的数据情况

在客户端cmd窗口：chcp 936  //GBK情况下，创建表并插入数据。
	
mysql> select * from testdb.users;
+----------+----------+---------+
| username | password | account |
+----------+----------+---------+
| 张三     | 123      |     100 |
| 李四     | 123      |     100 |
| 测试     | 123      |     100 |
+----------+----------+---------+
3 rows in set (0.00 sec)

mysql> show variables like '%character%';
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | gbk                                       |
| character_set_connection | gbk                                       |
| character_set_database   | utf8mb4                                   |
| character_set_filesystem | binary                                    |
| character_set_results    | gbk                                       |
| character_set_server     | utf8mb4                                   |
| character_set_system     | utf8                                      |
| character_sets_dir       | D:\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

在客户端cmd窗口，更改字符集 ：chcp 65001  //修改客户端字符集为UTF-8

查询数据，没有出现乱码，说明MySQL在各阶段都有对字符集的转换处理。

mysql> select * from testdb.users;
+----------+----------+---------+
| username | password | account |
+----------+----------+---------+
| 张三     | 123      |     100 |
| 李四     | 123      |     100 |
| 测试     | 123      |     100 |
+----------+----------+---------+
3 rows in set (0.00 sec)

mysql> show variables like '%character%';
+--------------------------+-------------------------------------------+
| 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       | D:\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+-------------------------------------------+
8 rows in set, 1 warning (0.00 sec)