一、基础知识
字符和字符集（Character and Character set）
那什么是字符呢？在计算机领域，我们把诸如文字、标点符号、图形符号、数字等统称为字符，包括各国家文字、标点符号、图形符号、数字等。而由字符组成的集合则成为字符集，是一个系统支持的所有抽象字符的集合。字符集由于包含字符的多少与异同而形成了各种不同的字符集，字符集种类较多，每个字符集包含的字符个数不同。我们知道，所有字符在计算机中都是以二进制来存储的。那么一个字符究竟由多少个二进制位来表示呢？这就涉及到字符编码的概念了。常见字符集名称：ASCII字符集、GB2312字符集、GBK字符集、GB18030字符集、Unicode字符集等。
字符编码（Character Encoding）
字符编码也称字符码，是把字符集中的字符编码为指定集合中某一对象（例如：比特模式、自然数序列、8位组），以便文本在计算机中存储和通过通信网络传输。我们规定字符编码必须完成如下两件事：1）规定一个字符集中的字符由多少个字节表示；2）制定该字符集的字符编码表，即该字符集中每个字符对应的（二进制）值。
校对规则（Collation）
校对规则是指在字符集中进行字符比较的规则，比如是否忽略大小写，是否按二进制比较字符等等。每个字符集都有自己默认的校对规则，不同的校对规则将影响比较结果。
哪些软件有字符集呢？
操作系统有字符集通用的一般都支持，Oracle和MySQL这样的数据库有自己的字符集，有的软件一般也有自己的字符集，当数据库或是软件有自己的字符集时就会使用自己的字符集，如果没有就使用系统自带的字符集。
什么时候用到字符集？
字符串存储和输出时用到字符集（注意存储数字是不需要有字符集概念，只有存储字符时才用到字符集）。如当在数据库中存放一张表字段为ID(NUMBER) NAME(VARCHAR)，当我们往ID字段插入数据时不用字符集，而当NAME字段中插入数据时就需要字符集。在数据库中字符集问题不仅影响到数据的存储，而且同样影响客户端程序和MySQL服务器之间。如果你希望客户端程序使用一个默认的字符集与服务器进行通信，那么你需要指定一个字符集，例如，使用utf8 unicode字符集。数据库字符集用来存储CHAR，VARCHAR，BLOB，LONG等数据类型，用来标识如表名，列名，以及PL/SQL变量等，用来存储SQL和PL/SQL程序单元等。
二、MySQL字符集和校对规则
MySQL服务器可以支持多种字符集，不同的库，不同的表和不同的字段都可以使用不同的字符集。
MySQL中的字符集都对应着一个默认的校对规则（COLLATION），当然一个字符集也可能对应多个校对规则，但是两个不同的字符集不能对应同一个规则。校对规则不指定就是使用默认的，比如utf8字符集对应的默认校对规则就是utf8_general_ci。校对规则后缀如_cs，_ci，_bin；分别表示是大小写相关/大小写无关/以字符串编码的二进制值来比较大小，例如：在校对规则“utf8_general_ci”下，字符“a”和“A”是等价的，就是不区分大小写。如果比较的两个字符集不同，则MySQL在比较前会先将其转换到同一个字符集再比较，如果两个字符集不兼容，则会报错Illegal mix of collations。
需要注意的是，校对规则可能会影响查询。比如数据表的一个字段本身设置的校对规则为utf8_general_ci，且在title字段有索引，而你查询的时候使用了SELECT xx FROM test ORDER BY title COLLATE utf8_bin来用另外校对规则进行排序，则此时就用不了索引，转而使用filesort。在实际项目中，一般不去显示指定的校对规则。
MySQL可以使用SHOW CHARACTER SET命令查看支持哪些字符集，使用SHOW COLLATION命令则会显示出所有支持的校对规则。当然，也可通过information_schema架构下的CHARACTER_SETS表和COLLATIONS查看字符集与校对规则。
下面以一个字符集的例子来区分清楚。
假设我们有一个字母表有四个字母：“A”，“B”，“C”，“D”。我们给每个字母的数字：“A”= 0，“B”= 1，“C”= 2，“D”= 3。字母“A”是一个符号，数字0是编码“A”，并结合四个字母和它们的编码字符集。
假设我们想比较字符串的值，“A”和“B”。最简单的办法是看编码：“A”对应“0”和“B”对应“1”。因为0比1小，所以我们说“A”小于“B”。我们所做的只是对我们的字符集进行比较，整理一套规则（在这种情况下，只有一条规则）：“比较编码”，我们称这种简单的比较规则是二进制比较规则（_bin）。如果说小写和大写字母是等价的？然后我们会有至少两个规则：
1）把小写字母“a”和“b”等同于“A”和“B”；
2）然后比较编码，我们称这种情况为不敏感校对规则，它比二进制更复杂。
MySQL包括字符集的支持，使您能够存储数据采用多种字符集，并利用各种校对规则比较字符串。MySQL对于字符集的指定可以细化到一个数据库，一张表，一列应该用什么字符集。可以启动任何级别的字符集和校对规则。
MySQL字符集支持有两个方面：字符集（character set）和校对规则（collation）。
MySQL字符集支持细化到四个层次：服务器（server），数据库（database），表（table）和列（column）。
SHOW CHARACTER SET语句可以查看MySQL支持的所有字符集及字符集的对应的默认校对规则。而SHOW COLLATION语句可以查看MySQL支持所有的校对规则，虽然任何一个给定的字符集总是至少有一个默认校对规则，但它可能有几种校对规则为一个字符集的校对规则列表，校对规则有这些一般特征：
1）不同的字符集不能有相同的校对规则。
2）每个字符集都有一个校对规则，这是默认的校对规则。例如，为latin1的默认校对规则是latin1_swedish_ci。
有一个约定：他们开始整理名称的字符集与它们相关联的名字，他们通常包括语言的名字，他们结束_ci（不区分大小写），_cs（区分大小写），或_bin（二进制）。
在这些方面，MySQL是远远领先于大多数其他数据库管理系统。然而，如何有效地利用这些特点，你需要知道什么字符集和校对规则可用，如何更改默认设置，以及他们如何影响字符串运算符。
三、四个层次的字符集设置
下面以MySQL中字符集和校对规则的继承规则进行四个层次的字符集设置（服务器、数据库、表、列）。
1. 安装MySQL时使用了一个默认的服务器字符集，这个字符集是Latin1。
2. 编译MySQL时可以手动指定一个默认服务器字符集和校对规则，参数为：
|
1 |
cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci |
3. 安装完成后可以在配置文件my.cnf中指定一个默认的服务器字符集，如果没有指定这个值则继承编译时指定的，参数为：
|
1 |
character_set_server = utf8 |
4. 启动Mysqld时可以在命令行参数中指定一个默认的字符集和校对规则，如果没有指定这个值则继承配置文件中的配置，参数为：
|
1 |
mysqld --character-set-server=latin1 --collation-server=latin1_swedish_ci |
5. 启动数据库后可以设置服务器字符集和校对规则，如果不设置就继承Mysqld进程启动时使用的字符集和校对规则，操作变量为：
|
1 2 |
SET character_set_server=utf8; SET collation_server=utf8; |
6. 可以选择设置数据库字符集和校对规则，如果不设置就继承服务器字符集和校对规则，操作变量为：
|
1 2 |
SET character_set_database =utf8; SET collation_database=utf8; |
7. 创建一个新的数据库时可以指定字符集和校对规则，否则继承服务器字符集和校对规则，语句为：
|
1 |
CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci; |
8. 创建一张新的表时可以指定字符集和校对规则，否则继承数据库字符集校对规则，语句为：
|
1 |
CREATE TABLE t1(id int) CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; |
9. 创建一个字段时可以指定字符集和校对规则，否则继承表字符集和校对规则，语句为：
|
1 |
CREATE TABLE t2(col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci) CHARACTER SET latin1 COLLATE latin1_bin; |
另外还可以通过db.opt文件来修改当前库字符集，因为每一个库创建之后都会生成一个db.opt文件，而这个文件中保存着数据库的默认字符集和校对规则。
|
1 2 |
default-character-set=utf8 default-collation=utf8_general_ci |
10. 修改表字符集和排序规则（会重新组织数据）
|
1 |
ALTER TABLE tn1 CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; |
PS：如果想修改数据库、表、列的字符集，可以使用ALTER语句来修改，详情可以看本博客的数据库和表创建章节。
四、MySQL连接字符集和校对规则
字符集和校对规则系统变量关系到客户端与服务器的交互。前面已经提到过：服务器字符集和校对规则的变量character_set_server和collation_server。数据库的字符集和校对规则的变量character_set_database和collation_database。
在客户端和服务器之间的连接处理涉及到额外的字符集和校对规则的系统变量，每个客户端都有连接相关的字符集和校对规则系统变量。
当客户端连接到服务器时，客户端发送一些SQL语句；然后服务器发送响应，例如结果集或错误消息，再连接返回到客户端。这就导致了一些关于字符集和校对规则连接客户端时的问题，但这些每一个问题都可以使用系统变量来回答，如下：
1）当SQL语句离开客户端时，该字符集是什么？
服务器使用character_set_client变量作为客户端发送的SQL语句中使用的字符集。
2）服务器接收到客户端SQL语句后，该字符集是什么？
为此，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection（除非字符串文字具有象_latin1或_utf8的引介词）。collation_connection对比较文字字符串是重要的。对于列值的字符串比较，它不重要，因为列具有更高的校对规则优先级。
3）服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？
character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据（如列名）。
五、连接字符集设置
SET语句改变变量的值。
|
1 2 3 |
SET character_set_client='utf8'; SET character_set_connection='utf8'; SET character_set_results='utf8'; |
SET NAMES语句可以用来改变字符集，这个语句会同时改变“character_set_client”，“character_set_results”和“character_set_connection”这三个变量的值。
|
1 |
set names utf8; |
CHARSET语句发出了一个SET NAMES语句，并且连接断开后自动重连时使用的缺省字符集也被修改了。
|
1 |
charset utf8; |
六、MySQL之间为什么要进行编/解码
既然系统之间是按照二进制流进行传输的，那直接把这串二进制流直接存入表文件就好啦。为什么在存储之前还要进行两次编解码的操作呢？
文件到引擎编解码是为了知道二进制流内的分词情况，举个简单的例子：我们想要从表里取出某个字段的前两个字符，执行了一句形如select left(col,2) from table的语句，存储引擎从文件读入该column的值是E4B8ADE69687。那么这个时候如果我们按照GBK把这个值分割成E4B8,ADE6,9687三个字，并那么返回客户端的值就应该是E4B8ADE6；如果按照UTF8分割成E4B8AD,E69687，那么就应该返回E4B8ADE69687两个字。可见，如果在从数据文件读入数据后，不进行编解码的话在存储引擎内部是无法进行字符级别的操作的。
客户端到服务器编解码的原因是MySQL需要对传来的二进制流做语法和词法分析，如果不做编码解析和校验，我们甚至没法知道传来的一串二进制流是insert还是update。
七、MySQL字符集相关变量
查看字符集相关变量
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show variables like 'character_set_%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) |
character_set_client
服务器使用character_set_client变量作为客户端发送的SQL语句中使用的字符集。
character_set_connection
连接层字符集，服务器使用系统变量character_set_connection和collation_connection。它把客户端传来的语句，从character_set_client字符集转换成character_set_connection字符集（除非字符串中有类似_latin1或者_utf8的字符集声明）。collation_connection对于字符串的比较是非常重要的。对于字符类型的字段值的比较，collation_connection是不起作用的。因为字段有自己的collation，有更高的优先级。
character_set_system
系统元数据(字段名等)字符集。
character_set_server
用于存储标识符的服务器所使用的字符集。
set character_set_results
character_set_results系统变量表明了服务器返回查询结果时使用的字符集。返回的数据，有比如字段的值和元数据（例如字段名）。
character_set_database
默认数据库使用的字符集，该服务器在默认数据库更改时设置此变量。
character_set_filesystem
文件系统字符集。这个变量是用来解释字符串引用的文件名，如在LOAD DATA INFILE和选择…为导出的文件报表和load_file()功能。这样的文件名转换为character_set_filesystem character_set_client之前打开文件的尝试时。默认值是二进制的，这意味着不发生转换。在多字节的文件名是允许的系统，不同的价值可能更合适。例如，如果系统是文件名使用UTF-8，集character_set_filesystem来为utf8。
查看校对规则字符集变量
|
1 2 3 4 5 6 7 8 9 |
mysql> show variables like 'collation_%'; +----------------------+-----------------+ | Variable_name | Value | +----------------------+-----------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | +----------------------+-----------------+ 3 rows in set (0.01 sec) |
Collation_开头的都是用来做列校对规则的。
八、MySQL字符编码转换流程
使用实验来进一步说明MySQL服务器字符集以及连接字符集之间的作用以及关系。这里我们就简单演示下这几种情况，这里我使用Xshell（utf8字符集）连接Linux系统。
然后，接着上面，我把我的Xshell工具调整为GB2312字符集，然后继续插入数据。
接着上面，继续做另一个测试。
提供测试带代码：
|
1 2 3 4 5 6 7 |
create table charset_test_utf8 (char_col varchar(50)) charset=utf8; set names gbk; insert into charset_test_utf8(char_col) values('中文'); set character_set_client=utf8; insert into charset_test_utf8(char_col) values('中文'); select * from charset_test_utf8; set character_set_results=utf8; |
测试
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
mysql> select 'a' = 'A' collate utf8mb4_general_ci; +--------------------------------------+ | 'a' = 'A' collate utf8mb4_general_ci | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select 'a' = 'A' collate utf8mb4_unicode_ci; +--------------------------------------+ | 'a' = 'A' collate utf8mb4_unicode_ci | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.06 sec) mysql> select ':' = '：' COLLATE utf8mb4_general_ci; +----------------------------------------+ | ':' = '：' COLLATE utf8mb4_general_ci | +----------------------------------------+ | 0 | +----------------------------------------+ 1 row in set (0.00 sec) mysql> select ':' = '：' COLLATE utf8mb4_unicode_ci; +----------------------------------------+ | ':' = '：' COLLATE utf8mb4_unicode_ci | +----------------------------------------+ | 1 | +----------------------------------------+ 1 row in set (0.01 sec) mysql> select 'a' = 'A' collate utf8mb4_bin; +-------------------------------+ | 'a' = 'A' collate utf8mb4_bin | +-------------------------------+ | 0 | +-------------------------------+ 1 row in set (0.00 sec) mysql> select ':' = '：' COLLATE utf8mb4_bin; +---------------------------------+ | ':' = '：' COLLATE utf8mb4_bin | +---------------------------------+ | 0 | +---------------------------------+ 1 row in set (0.00 sec) mysql> select 'a' = 'a' collate utf8mb4_bin; +-------------------------------+ | 'a' = 'a' collate utf8mb4_bin | +-------------------------------+ | 1 | +-------------------------------+ 1 row in set (0.00 sec) |
完结。。。
