一、字符集和校验规则

字符集是一套符号和编码，校验规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则。MySQL可以使用多种字符集和检验规则来组织字符。

MySQL服务器可以支持多种字符集，在同一台服务器，同一个数据库，甚至同一个表的不同字段都可以指定使用不同的字符集，相比oracle等其他数据库管理系统，在同一个数据库只能使用相同的字符集，MySQL明显存在更大的灵活性。

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则，并且每个校对规则只是针对某个字符集，和其他的字符集么有关系。
在MySQL中，字符集的概念和编码方案被看做是同义词，一个字符集是一个转换表和一个编码方案的组合。

Unicode（Universal Code）是一种在计算机上使用的字符编码。Unicode 是为了解决传统的字符编码方案的局限而产生的，它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。Unicode存在不同的编码方案，包括Utf-8，Utf-16和Utf-32。Utf表示Unicode Transformation Format。

二、查看MySQL字符集方法

1、查看mysql服务器支持的字符集

下面的SQL命令都可以查看MySQL数据库支持字符集
show character set;
select * from information_schema.character_sets;
我们使用第一条命令行举例，执行命令后可以看到下图所示的结果：

2、查看字符集的校对规则

查询MySQL支持的所有校对规则：show collation;
查询MySQL支持的utf8校对规则：show collation like 'utf8%';
当然也可以使用select语句查询：
select * from information_schema.collations where collation_name like 'utf8%';
我们使用第一条命令举例，执行命令后可以看到下图所示的结果：

3、查看当前数据库的字符集

执行show variables like 'character%';可以查看当前数据库使用的字符集，如下图所示：

解释一下上图中变量名代表的意思：
character_set_client：客户端请求数据的字符集
character_set_connection：客户机/服务器连接的字符集
character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server指定的字符集，这个变量建议由系统自己管理，不要人为定义。
character_set_filesystem：把os上文件名转化成此字符集，即把character_set_client转换character_set_filesystem，默认binary是不做任何转换的

character_set_results：结果集，返回给客户端的字符集
character_set_server：数据库服务器的默认字符集
character_set_system：系统字符集，这个值总是utf8，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

4、查看当前数据库的校对规则

执行show variables like 'collation%';可以查看当前数据库使用的校对规则，如下图所示：

解释一下上图中变量名代表的意思：
collation_connection：当前连接的字符集。
collation_database：当前日期的默认校对。每次用USE语句来“跳转”到另一个数据库的时候，这个变量的值就会改变。如果没有当前数据库，这个变量的值就是collation_server变量的值。
collation_server：服务器的默认校对。

排序方式的命名规则为：字符集名字_语言_后缀，其中各个典型后缀的含义如下：
1）_ci：不区分大小写的排序方式
2）_cs：区分大小写的排序方式
3）_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言

三、MySQL字符集的设置

1、概述

MySQL字符集设置分为两类：
1）创建对象的默认值。
2）控制server和client端交互通信的配置。

1.1 创建对象的默认值

字符集合校对规则有4个级别的默认设置：
1）服务器级别;
2）数据库级别;
3）表级别、列级别;
4）连接级别。
更低级别的设置会集成高级别的设置。
这里有一个通用的规则：先为服务器或者数据库选择一个合理的字符集，然后根据不同的实际情况，让某个列选择自己的字符集。

1.2 控制server和client端交互通信的配置

大部分MySQL客户端都不具备同时支持多种字符集的能力，每次都只能使用一种字符集。
客户和服务器之间的字符集转换工作是由如下几个MySQL系统变量控制的。
1）character_set_server：MySQL服务端默认字符集。
2）character_set_database：数据库默认字符集。
3）character_set_client：MySQL服务端假定客户端发送的查询使用的字符集。
4）character_set_connection：MySQL服务端接收客户端发布的查询请求后，将其转换为character_set_connection变量指定的字符集。
5）character_set_results：MySQL服务端把结果集和错误信息转换为character_set_results指定的字符集，并发送给客户端。
6）character_set_system：系统元数据(字段名等)字符集
还有以collation_开头的同上面对应的变量，用来描述字符校对规则。
注意事项：
• my.ini中的default_character_set设置只影响mysql命令连接服务器时的连接字符集，不会对使用libmysqlclient库的应用程序产生任何作用！
• 对字段进行的SQL函数操作通常都是以内部操作字符集进行的，不受连接字符集设置的影响。
• SQL语句中的裸字符串会受到连接字符集或introducer设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！

1.3 默认情况下字符集选择规则

(1)编译MySQL时，指定了一个默认的字符集，这个字符集是 latin1;
(2)安装MySQL时，可以在配置文件 (my.ini) 中指定一个默认的的字符集，如果没指定，这个值继承自编译时指定的;
(3)启动mysqld时，可以在命令行参数中指定一个默认的的字符集，如果没指定，这个值继承自配置文件中的配置,此时character_set_server被设定为这个默认的字符集;
(4)当创建一个新的数据库时，除非明确指定，这个数据库的字符集被缺省设定为character_set_server;
(5)当选定了一个数据库时，character_set_database被设定为这个数据库默认的字符集;
(6)在这个数据库里创建一张表时，表默认的字符集被设定为character_set_database，也就是这个数据库默认的字符集;
(7)当在表内设置一栏时，除非明确指定，否则此栏缺省的字符集就是表默认的字符集;

2、通过SQL命令完成字符集和校对规则的设定

2.1 为数据库指定字符集和校对规则

在创建数据库的时候就可以为数据库指定字符集和校对规则，SQL命令示例如下：
create database dbtest charset=utf8 collate utf8_romanian_ci;
charset=utf8表示设定数据库字符集为utf8
collate utf8_romanian_ci表示设定数据库校对规则为utf8_romanian_ci
我们可以通过show create database dbtest查看创建数据库的SQL语句，命令执行效果如下图所示：

注意：创建数据库分配字符集可以采用以下几种语句都行：
charset=utf8;
default charset=utf8;
charset utf8;
default charset utf8;
char set=utf8;
default char set=utf8;
char set utf8;
default char set utf8;
character set=utf8;
default character set=utf8;
character set utf8;
default character set utf8;

2.2 为表指定字符集和校对规则

我们可以通用创建数据库表的时候为表指定字符集和校对规则，执行SQL命令如下：

default charset=utf8表示设置字符集
collate utf8_romanian_ci表示设置校对规则
我们可以通过show create table table_charset查看创建表的SQL语句，命令执行效果如下图所示：

注意：为表指定字符集可以使用以下几种方式：
default charset=utf8;
charset=utf8;
default character set=utf8;
character set=utf8;
default char set=utf8;
char set=utf8;

2.3 为表列指定字符集和校对规则

我们可以通过创建数据库表的时候就为列指定字符集和校对规则，执行SQL命令如下：

语法与设置数据库表基本上一样，然后我们通过下面的SQL命令查看这张表的字符集规则

命令行执行结果如下图所示

2.4 修改和设置MySQL服务器级别字符集

MySQL服务器支持众多不同的字符集，这类字符集可在编译时和运行时指定。

1）编译时指定
编译时可指定默认字符集和默认校对规则，要想同时更改默认字符集和校对规则，要同时使用–with-charset和–with-collation选项。
校对规则必须是字符集的合法校对规则,如以下编译示例
./configure --with-charset=utf8 --with-collation=utf8_romanian_ci
通过configure选项–with-extra-charsets=LIST，可以定义在服务器中再定义增加字符集。
LIST指下面任何一项：
a.空格间隔的一系列字符集名
b.complex -，以包括不能动态装载的所有字符集
c.all –，以将所有字符集包括进二进制
编译示例如下所示
./configure --with-charset=utf8 --with-collation=utf8_romanian_ci --with-extra-charsets=all
当然编译指定一般是在Linux操作系统下执行，在windows下面安装MySQL一般不做编译指定。

2）在参数文件my.ini中指定

3）在启动参数前指定

4）在mysql客户端登陆时通过–default-character-set指定

5）临时指定

a）分别指定
mysql> SET character_set_client = utf8;
mysql> SET character_set_connection = utf8;
mysql> SET character_set_database = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_server = utf8;

b）mysql客户端使用：set names utf8;
等同于
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;

c）set character set utf8;
等同于
set character_set_client=utf8;
set character_set_results=utf8;
set collation_connection=@@collation_database;

3、总结

下面介绍下几个MYSQL命令：
查看数据库支持的所有字符集
show character set;
show char set;
查看当前状态 里面包括当然的字符集设置
status;
\s;
查看系统字符集设置，包括所有的字符集设置
show variables like 'char%';
查看sqlstudy数据库中表的字符集设置
show table status from sqlstudy like '%countries%';
查看表列的字符集设置，关键是在同一个表中，每列可以设置成不同的字符集
show full columns from countries;

知道怎么查看字符集了，下面我来说下如何设置这些字符集
1.修改服务器级
a. 临时更改,执行下面命令行：
SET GLOBAL character_set_server=utf8;
b. 永久更改：
修改my.ini文件

2.修改数据库级
a. 临时更改，执行命令行：
SET GLOBAL character_set_database=utf8;
b. 永久更改：
改了服务器级就可以了

3.修改表级，执行命令行：
ALTER TABLE table_name DEFAULT CHARSET utf8;
更改了后永久生效

4.修改列级
修改示例：

更改了后永久生效

5.更改连接字符集
a. 临时更改：
set names utf8;
b. 永久更改：
修改my.ini文件

三、MySQL数据库乱码原因以及解决方案

1、产生乱码的根本原因

1）客户机没有正确地设置client字符集，导致原先的SQL语句被转换成connection所指字符集，而这种转换，是会丢失信息的，如果client是utf8格式，那么如果转换成gb2312格式，这其中必定会丢失信息，反之则不会丢失。一定要保证connection的字符集大于client字符集才能保证转换不丢失信息。
2）数据库字体没有设置正确，如果数据库字体设置不正确，那么connection字符集转换成database字符集照样丢失编码，原因跟上面一样。

2、乱码或数据丢失

character_set_client:我们要告诉服务器，我给你发送的数据是什么编码？
character_set_connection:告诉字符集转换器，转换成什么编码？
character_set_results:查询的结果用什么编码？
如果以上三者都为字符集N,可简写为set names ‘N’;

2.1 乱码问题

向默认字符集为utf8的数据表插入utf8编码的数据前连接字符集设置为latin1，查询时设置连接字符集为utf8。
插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和character_set_results均为latin1；
插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存；
查询时的结果将经过utf8=>utf8的字符集转换过程，将保存的6个字节原封不动返回，产生乱码
依次执行下面SQL命令

我们会看到如下的输出结果

此时修改连接编码，在进行查询

命令执行后的输出结果

注意：存储字符集编码比插入时字符集大时，如果原封不动返回数据会出现乱码，不过可通过修改查询字符集，避免乱码，即不会丢失数据。

3、乱码终极解决方案

1）首先要明确你的客户端时候何种编码格式，这是最重要的（IE6一般用utf8，命令行一般是gbk，一般程序是gb2312)
2）确保你的数据库使用utf8格式，很简单，所有编码通吃。
3）一定要保证connection字符集大于等于client字符集，不然就会信息丢失，比如： latin1 < gb2312 < gbk < utf8，若设置set character_set_client = gb2312，那么至少connection的字符集要大于等于gb2312，否则就会丢失信息
4）以上三步做正确的话，那么所有中文都被正确地转换成utf8格式存储进了数据库，为了适应不同的浏览器，不同的客户端，你可以修改character_set_results来以不同的编码显示中文字体，由于utf8是大方向，因此web应用是我还是倾向于使用utf8格式显示中文的。

老九学堂会员社群出品

作者：naaman