mysql字符集和校对规则(character sets and collations)详解

mysql字符集(character sets)是指一系列符号以及符号对应的编码的集合，比如英文字母可以用ASCII编码，中文可以用GBK或者UTF8编码。校对规则(collations)则是指一种比较字符的规则，这种比较规则决定了mysql如何进行排序以及如何对字符比较大小。

mysql的character sets和collations有很多种，而且可以在多个维度去配置，包含服务器的配置和客户端的配置，对于初学者往往容易搞混，有时候出了乱码等问题也不知道怎么排查。今天笔者就详细梳理一下mysql中的character sets和collations。

我们先来看看mysql都可以配置哪些字符集，输入如下命令查看。

show variables like '%character%';

 可以看到，有多达7个的character_set相关的参数，接下来我们就详细说说这些参数。

查看mysql中支持的字符集和校对规则 

mysql中每个字符集都会对应多个校对规则，是一对多的关系。比如utf8对应的collation有utf8_general_ci，utf8_bin，utf8_unicode_ci等。而且每个character set会有个默认的collation与之对应，当我们在创建数据库或者创建表时如果只指定character set，不指定collation，就会使用character set默认的collation。collation的命名是以对应的character set为开头，比如collation为utf8_general_ci，我们就知道这个collation对应的字符集是utf8。

查看字符集

2种方式可以查看mysql中支持哪些字符集

1. 通过INFORMATION_SCHEMA.CHARACTER_SETS表来查看

2. 通过SHOW CHARACTER SET来查看，和上面表的结果是一样的。

查看collations

通过 show collation 查看校对规则有哪些，还可在语句后面加where条件筛选。

配置与查看字符集与校对规则

服务器端相关character set和collation

mysql服务器端相关字符集是服务器对数据的存储等相关的字符集，不涉及客户端的问题。

mysql服务器端的字符集和校对规则可以在四个级别指定：server, database, table, column

server级别

在mysql5.7中，server character set 和 server collation 默认为 latin1 和 latin1_swedish_ci，在mysql8中，默认为utf8mb4 和 utf8mb4_0900_ai_ci

如果想要修改server级别的character set和collation，可以在启动时指定参数

 mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci

或者将上面的参数写入到my.ini中[mysqld]下面，如下图。

server级别的character set和collation的作用是为创建数据库(CREAT DATABASE)时指定默认字符集和校对规则。也就是说创建数据库时如果没有指定character set和collation，就默认使用server级别的。

server级别的character set 和 collation可通过如下命令查看

show variables like 'character_set_server'
show variables like 'collation_server'

 

database级别

数据库级别的character set和collation可以在创建和修改数据库时指定，如果不指定，则使用server级别的。

CREATE DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name [[DEFAULT] CHARACTER SET charset_name] [[DEFAULT] COLLATE collation_name]

举例：

create database demo character set utf8 collate utf8_general_ci;

数据库的character set和collation可通过INFORMATION_SCHEMA.SCHEMATA表查看。

比如下图，我创建了三个数据库test, test2, test3，test数据库的character set和collation分别为utf8和utf8_general_ci，而test2和test3数据库的character set和collation分别为latin1和latin1_swedish_ci。

也可以通过如下方式查看

USE db_name;
SELECT @@character_set_database, @@collation_database;

数据库级别的character set和collation会影响以下行为：

1. 创建表时，会默认使用database的character set和collation。

2. 使用mysql的LOAD DATA加载数据时，默认采用database的character set和collation。

3. 作用于存储过程和函数，在创建存储过程和函数时，传递的参数默认采用database的character set。

比如我的database character set为latin1，然后我创建一个存储过程proc1(nameStr varchar(20))，当我调用存储过程call proc1('张三')时，会报错，因为存储过程proc1的参数只支持latin1字符集。

table级别

table的character set和collation可在创建表时指定，不指定默认采用database级别的。

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

table级别的character set和collation会影响column级别的character set和collation，也就是column如果不指定character set和collation，就会默认继承table级别的。

要想查看表的character set和collation，可通过information_schema.TABLES表查看，不过这个表只保存了collation信息，但是通过collation我们就能知道character set是什么了。

 或者通过如下命令查看

column级别

指定column级别的character set和collation，只有column的类型为字符型，如char,varchar,text等时，才可指定character set和collation。

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
    
举例
CREATE TABLE t1
(
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_german1_ci
);

ALTER TABLE t1 MODIFY
    col1 VARCHAR(5)
      CHARACTER SET latin1
      COLLATE latin1_swedish_ci;

要查看列的character set和collation，可通过information_schema.COLUMNS表查看。

客户端相关character set和collation

客户端可能和mysql服务器采用不同的字符集，比如我们的java应用用的是一套字符集，mysql服务器用的是另一套字符集，这种情况下就要指定客户端和服务器连接交互时的字符集。和客户端相关的字符集配置有3个参数：character_set_client、character_set_connection、character_set_results。

这3个参数都是session级别的，也就是说不同的客户端连接时可以把这3个参数指定为不同的值，而且在客户端和服务器连接建立成功后，可以动态修改这3个值，不需要重启mysql。

参数作用

mysql服务器把从客户端接收的数据从character_set_client 转成 character_set_connection，然后进行后续处理。把查询结果转成character_set_results返回给客户端。所以character_set_client和character_set_connection是在向mysql发送命令的时候起作用，character_set_results是在接收mysql数据时起作用。

参数查看与设置

在连接mysql成功后，通过如下2种方式查看这三个参数的值。

方式一：

SELECT * FROM performance_schema.session_variables
WHERE VARIABLE_NAME IN (
'character_set_client', 'character_set_connection',
'character_set_results', 'collation_connection'
) ORDER BY VARIABLE_NAME;

方式二：

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

设置这三个参数的值有以下3种方式。

方式一：

在mysql客户端连接时指定，可以配置在my.ini中，或者写在命令行后面。

[mysql]

default-character-set=utf8

mysql -uroot -p --default-character-set=utf8

方式二：

set names charset_name 

这个命令等同于如下命令

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET character_set_connection = charset_name;

方式三：

set character set charset_name，注意区分和set names的区别。这个命令会把character_set_connection设置为和database一样，而不是指定的charset_name。

等于如下命令。

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET collation_connection = @@collation_database;

character_set_client、character_set_connection、character_set_results深入理解

一般我们把character_set_client、character_set_connection、character_set_results这三个值设置为一样的比较好，这样可以避免不必要的麻烦。但是具体这几个参数怎样在发挥作用，下面我们通过几个例子来看一下。注意，下面的例子可能会比较容易把人绕晕，如果不感兴趣，可以不看。上面讲解的关于字符集和校对规则的知识应该可以应对日常开发了。

character_set_results参数作用示例

这里我们可以做个试验加深理解，我们先试验一下character_set_results。

我们建一个表，字符集设置为utf8，在表中插入一行数据。注意我这里插入数据是用的mysql 连接工具datagrip，没有在cmd窗口中用mysql命令行，因为用命令行可能会影响后面的分析结果。

create table demo
(
	id int auto_increment,
	name varchar(20) null,
	constraint demo_pk
		primary key (id)
) character set utf8;
insert into demo(name) value ('gitcat熊');

然后我们打开cmd窗口，连接mysql服务器。可以看到，连接好后，默认的character_set_client、character_set_connection、character_set_results都是gbk，这是因为windows系统默认的编码是gbk。

然后我们查询数据看看。可以看到中文可以正常显示。这是因为mysql server端demo表的编码是utf8，但是character_set_results配置的是gbk，所以mysql在返回数据之前把查出的数据转成了gbk，又因为我们cmd窗口是以gbk编码的，所以就正常显示出了数据。 

我们把character_set_results设置成utf8再看看。可以看到显示出问题了。因为我们告诉mysql server character_set_results=utf8，所以mysql就会把查询结果转成utf8返回给我们，但是我们的cmd窗口是gbk编码的，窗口会按照gbk解码数据显示，自然就显示出问题了。 

我们可以验证一下。熊的utf8编码为E7868A，我们把这个编码转成gbk看看，因为gbk是两个字节为单位编码，所以我们先查询一下gbk编码E786对应的字是什么，从下图可以看到正好是我们返回的select结果的第一个汉字“鐔”，然后再查一下8A，查不到可显示的字符，因为GBK的编码范围是在8140-FEFE，所以返回结果显示了个“□”。 

接下来我们把当前cmd窗口修改成用utf8编码显示，设置方式网上有很多，就是在cmd窗口执行一下命令 chcp 65001，会打开一个新的窗口，我们在新窗口重新连接mysql，可以看到，连接后的默认编码已经变成utf8了。

这时我们再查询一下，可以看到可以正常显示。

这时如果我们把character_set_results设置成gbk，反而不能正常显示了。因为我设置character_set_results=gbk，mysql server就会认为你要把查询结果转成gbk，mysql server好心把结果给你转成gbk了，结果你的窗口又以utf8解码来显示，当然就出问题了。 熊的gbk编码是D0DC，ÐÜ对应的unicode，正好也是D0和DC，印证了mysql服务器确实把熊这个字转成gbk编码传给我们了。

细心的网友可能会发现，这里有个问题，就是为什么cmd窗口是以unicode解码的，而不是utf8？因为我们返回的数据格式不符合utf8规则！

utf8编码规则如下，可以看到，不是随便给个2字节或者3字节的编码，都能用utf8来解析出字符。所以猜测cmd窗口对于不能用utf8解析的编码，只能以unicode来解码了。如果我们返回的编码正好是符合utf8规则的，那么cmd窗口就会以utf8正确解析出结果。

Unicode符号范围 | UTF-8编码方式
(十六进制) | （二进制）
--------------------+---------------------------------------------
0000 0000-0000 007F | 0xxxxxxx
0000 0080-0000 07FF | 110xxxxx 10xxxxxx
0000 0800-0000 FFFF | 1110xxxx 10xxxxxx 10xxxxxx
0001 0000-0010 FFFF | 11110xxx 10xxxxxx 10xxxxxx 10xxxxxx
以汉字“严”为例，演示如何实现UTF-8编码。
已知“严”的unicode是4E25（100111000100101），
根据上表，可以发现4E25处在第三行的范围内（0000 0800-0000 FFFF），
因此“严”的UTF-8编码需要三个字节，即格式是“1110xxxx 10xxxxxx 10xxxxxx”。
然后，从“严”的最后一个二进制位开始，依次从后向前填入格式中的x，多出的位补0。
这样就得到了，“严”的UTF-8编码是“11100100 10111000 10100101”，转换成十六进制就是E4B8A5。

真的如我们猜测的这样吗？我们可以继续做试验来看。请接着往下看，下面会更精彩！

我们可以来做个实验验证上面我们的猜想。还是保持我们上面的实验环境，即cmd窗口是utf8模式，设置character_set_results=gbk，然后我们在数据表demo中插入如下一条数据，注意我插入数据用的是datagrip，不是在当前cmd窗口，因为当前cmd窗口我们修改了一些参数，直接在这里插入会影响我们分析。

insert into demo(name) values('鍦ㄥ悧');

然后查询一下结果。

 

很神奇，竟然没有"乱码"，当然其实是乱码了，因为和我们数据库中存入的结果不一样了。我们分析一下这里发生了什么。

首先mysql server从数据库中查出"鍦ㄥ悧"这3个字，然后发现character_set_results=gbk，好吧，那mysql server就按照要求，把这3个字转成gbk格式发送给客户端，这3个字对应的gbk编码是E59C(鍦)A8E5(ㄥ)9097(悧)，现在cmd窗口接收到这个编码了，要显示在屏幕上了，因为cmd窗口设置的是utf8编码，所以就以utf8方式来解析收到的编码E59CA8E59097，而这一串编码正好对应utf8中的"在吗"。这也印证了我们之前的猜想，如果server传回来的数据是符合utf8编码规则的，cmd窗口就会以utf8规则帮我们解码出数据显示。

character_set_client、character_set_connection参数作用

接下来我们试验一下character_set_client、character_set_connection这两个参数的设置对数据的影响。前面说了，server把从客户端接收的数据从character_set_client 转成character_set_connection，比如我们发送了一条insert语句，mysql server就会以为，你发送的语句是用character_set_client编码的，但是我要转成character_set_connection，然后再执行插入语句。

我们还是先以默认方式连接mysql数据库，如下。

然后在我们上面建的demo表里插入一条数据。可以看到，数据被正确地插入和读出。因为我们当前窗口的编码是gbk，而设置的character_set_client和character_set_connection也是gbk，所以mysql就会把我们的数据从gbk转成gbk，再存入数据库，当然就没问题了。当然存入数据库时发现表的编码是utf8，所以其实这里又转成utf8存入数据库的，这一步和character_set_client和character_set_connection就没关系了，我们先不深究。

接下来继续试验，这里只说一下结果，就不截图了。

cmd窗口为gbk编码时的情况

1. 如果cmd窗口是gbk编码，character_set_client是gbk，character_set_connection是utf8，插入和查询也没问题。

2. 如果cmd窗口是gbk编码，character_set_client是utf8，character_set_connection是utf8，插入无法插入，会报错如下。

我们来分析一下这种情况，因为当前窗口是gbk编码，所以传给server的编码是gbk的，但是因为character_set_client=utf8，server误以为是utf8的，按照utf8来解码，发现和utf8编码规则不符合，所以报错了。如果我们传给server的gbk编码正好也符合utf8编码，server是不会报错的，会插入成功，只是插入的数据不是我们预期的。比如下图的例子，我们还是以“鍦ㄥ悧”这三个字举例，如前所述，“鍦ㄥ悧”这三个字的gbk编码是E59C(鍦)A8E5(ㄥ)9097(悧)，而这个编码正好也符合的utf8的编码，server在拿到这个编码后，按照utf8的规则解码可以解码成功，并且解码出来是“在吗”，于是就不会报错了。

 

 3. 如果cmd窗口是gbk编码，character_set_client是utf8，character_set_connection是gbk，这种情况可以插入数据，但是插入的数据不对。

 因为我们gbk编码的熊字不能正确被解码为utf8，所以在解码为utf8时就已经乱码了，后面所有的操作都会是乱码的，所以数据是不对的。

cmd窗口为utf8编码时的情况

1. 如果cmd窗口是utf8编码，character_set_client和character_set_connection设置成utf8，毫无疑问，这种情况肯定是没问题的，因为我们用的编码都一样。

2. 如果cmd窗口是utf8编码，character_set_client=gbk和character_set_connection=utf8，结果如下，可以看到utf8编码的熊为E7868A，E786正好对应gbk的"鐔"，说明server是按照gbk解码了utf8编码的数据。

下面附一张在情况2这种情况下的另一个插入语句，有了前面我们的分析，你能明白结果为什么是这样了吗？

3. 如果cmd窗口是utf8编码，character_set_client=gbk和character_set_connection=gbk，2次插入语句结果分别如下。

4. 如果cmd窗口是utf8编码，character_set_client=utf8和character_set_connection=gbk，结果如下。

 情况3和情况4大家可以根据前面的分析自己分析一下结果，看看是否理解了。