理解mysql字符集(mysql 5.6)

字符集概述和基本指令

字符集（character）是一套符号和编码。校对规则（collation）是在字符集内用于比较字符的一套规则，拿mysql官方文档的例子来说：

假设我们有一个字母表使用了四个字母：‘A’、‘B’、‘a’、‘b’。我们为每个字母赋予一个数值：‘A’=0，‘B’= 1，‘a’= 2，‘b’= 3。字母‘A’是一个符号，数字0是‘A’的编码，这四个字母和它们的编码组合在一起是一个字符集。

假设我们希望比较两个字符串的值：‘A’和‘B’。比较的最简单的方法是查找编码：‘A’为0，‘B’为1。因为0 小于1，我们可以说‘A’小于‘B’。我们做的仅仅是在我们的字符集上应用了一个校对规则。校对规则是一套规则（在这种情况下仅仅是一套规则）：“对编码进行比较。”我们称这种全部可能的规则中的最简单的 校对规则为一个binary（二元）校对规则。

但是，如果我们希望小写字母和大写字母是等价的，应该怎样？那么，我们将至少有两个规则：（1）把小写字母‘a’和‘b’视为与‘A’和‘B’等价；（2）然后比较编码。我们称这是一个大小写不敏感的校对规则。比二元校对规则复杂一些。

mysql能够做这些事情：

1用多种字符集来存储字符串

2多种校对规则来比较字符串

3同一台服务器、同一个数据库或甚至在同一个表中使用不同字符集或校对规则来混合字符串

4许定义任何级别的字符集和校对规则

那么，如何查看mysql支持哪些字符集呢，指令如下

如何查看对应的字符集下有哪些校对规则呢（也可称为排序规则），比如我要查看字符集utf8对应有哪些校对规则，指令如下

注：每一个字符集都有默认的校对规则，我们强烈建议不要随意修改字符集的校对规则

字符集的问题不仅作用于数据存储，还有客户端程序和服务器端的交互上，如果你想让自己的客户端程序与服务器的“互相沟通“的这个过程中使用不同于默认设置的字符集，那么指令如下，比如需要用uft8作为沟通桥梁的字符集

SET NAMES 'utf8';

各种级别的字符集和校验规则设置

字符集和校对规则有5级别的默认设置：服务器级、数据库级、表级，字段级，连接级。

服务器级别的字符集设置

在服务器级别，确定方法很简单。当启动mysqld时，根据使用的初始选项设置来确定服务器字符集和 校对规则。配置参数是character-set-server和collation_server，

以下是官网对这两个参数的说明

数据库级别的字符集设置

在数据库级别，每一个数据库有一个数据库字符集和一个数据库校对规则。CREATE DATABASE和ALTERDATABASE语句有一个可选的子句来指定数据库字符集和校对规则，指令如下

数据库字符集设置和校验规则如下：

通常来说，设置数据库级别的字符集和校对规则只有以下两个用途

1 如果在该库下create table时没有指定字符集和校对规则，则数据库级别的字符集和校验规则作为该表的默认值

2 用于load data infile

如果创建db的时候没有指定字符集，那么就取character_set_databaseandcollation_database这两个系统变量的值作为默认值；如果这两个系统变量都没定义，那么就取server级别的字符集和校验规则

 

表级别的字符集设置

在表级别，设置字符集和校验规则的指令如下

设置和规则和database级别基本一样，如果没有定义，则采用database级别的字符集，这里不再赘述

字段级别的字符集设置

在字段级别，指令如下

设置和规则和database级别基本一样，如果没有定义，则采用表级别的字符集，这里不再赘述。注意：不要随意修改字段的字符集，如果修改后的字符集和之前设置的不兼容，可能会引起数据丢失。

设置字符串的字符集设置

范例如下

SELECT 'string';
SELECT _latin1'string';
SELECT _latin1'string' COLLATElatin1_danish_ci;

如果没有设置，那么默认就是由character_set_connection 和collation_connection这两个参数控制

连接的字符集设置

连接字符集用于处理客户端和服务器之间的数据传输，通常非常复杂。首先什么是connection的概念呢？也就是连接服务器时所作的事情。当客户端发送SQL语句，例如查询，通过连接发送到服务器。服务器通过连接发送响应给客户端，例如结果集或者错误信息。对于客户端连接，这样会导致一些关于连接的字符集和校对规则的问题，这些问题均能够通过系统变量来解决

1 当查询离开客户端以后，反映在statement语句中使用什么字符集？

服务器端使用character_set_client参数作为客户端发送的查询中使用的字符集

2 服务器接收到查询后，将statement翻译过来应该用哪种字符集？

转换时，服务器使用character_set_connection和collation_connection系统变量。它将客户端发送的查询从character_set_client系统变量转换到character_set_connection（除非字符串文字具有象_latin1或_utf8的引介词）。collation_connection对比较文字字符串是重要的。对于column列值的字符串比较，它不重要，因为column拥有自己的字符集，并且具有更高的 校对规则优先级。

 

3服务器发送结果集或返回错误信息到客户端之前应该转换为哪种字符集？

character_set_results变量指示服务器返回查询结果到客户端使用的字符集。包括结果数据，例如列值和结果元数据（如列名）。

有两个语句影响连接字符集：

SET NAMES 'charset_name'

SET CHARACTER SET charset_name

SET NAMES显示客户端发送的SQL语句中使用什么字符集。因此，SETNAMES 'cp1251'语句告诉服务器“将来从这个客户端传来的信息采用字符集cp1251”。它还为服务器发送回客户端的结果指定了字符集。（例如，如果你使用一个SELECT语句，它表示列值使用了什么字符集。）

SET NAMES 'x'语句与这三个语句等价：

mysql> SET character_set_client = x;

mysql> SET character_set_results = x;

mysql> SET character_set_connection = x;

将x设置为character_set_connection也就设置了collation_connection是x的默认校对规则。

SET CHARACTER SET语句是类似的，但是为 默认数据库设置连接字符集和校对规则。SET CHARACTER SET x语句与这三个语句等价：

mysql> SET character_set_client = x;

mysql> SET character_set_results = x;

mysql> SET collation_connection = @@collation_database;

当一个客户端连接时，它向服务器发送希望使用的字符集名称。服务器为那个字符集设置character_set_client、character_set_results和 character_set_connection变量。（实际上，服务器为使用该字符集执行一个SET NAMES操作。）

对于mysql客户端，如果你希望使用与默认字符集不同的字符集，不需要每次启动时执行SET NAMES语句。可以在mysql语句行中或者选项文件中添加一个--default-character-set选项设置。例如，你每次运行mysql时，以下的选项文件设置把三个字符集变量修改为koi8r：

[mysql]

default-character-set=koi8r

例如：假设column1定义为CHAR(5) CHARACTER SET latin2。如果没有设定SET NAMES或SET CHARACTER SET，那么对于SELECT column1 FROM t，当连接后，服务器使用客户端指定的字符集返回列column1的所有值。另一方面，如果你设定SET NAMES 'latin1'或SET CHARACTER SET latin1，那么发送结果之前，服务器转换latin2值到latin1。转换可能会丢失那些不属于两种字符集的字符。

如果不希望服务器执行任何转换，设置character_set_results为NULL：

mysql> SET character_set_results = NULL;

元数据的字符集

MySQL使用Unicode字符集存储元数据，即UTF8。如果你从不使用重音字符，这不会导致任何破坏。但如果你使用重音字符，应该注意的是元数据是用UTF8存储。

这意味着，USER()、CURRENT_USER()、DATABASE()和VERSION()函数的返回值被 默认设置为UTF8字符集，这与同义函数如SESSION_USER() 和SYSTEM_USER()的结果相同。

服务器将character_set_system系统变量设置为元数据字符集的名：

mysql> SHOW VARIABLES LIKE 'character_set_system';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| character_set_system | utf8  |

+----------------------+-------+

存储元数据使用Unicode并不意味着列头和DESCRIBE函数的结果默认在character_set_system字符集中。当你使用SELECT column1FROM t语句时，名字为column1的列从服务器返回客户端并使用由SET NAMES语句确定的字符集。更明确地说，使用的字符集是由character_set_results系统变量的值确定的。如果这个系统变量设置为NULL，不执行字符转换，服务器使用最初的字符集（字符集由character_set_system系统变量设置）返回元数据。

关于乱码

从上述的描述中，我们可简单地把一个客户端发起一个sql请求到返回结果中涉及到的字符串编码转换简化成以下步骤

1  character_set_client 是客户端连接上来的字符集，这时可查看session级别的设置

2  数据从character_set_client对应的字符集转换为character_set_connection对应的字符集

3 character_set_connection对应的字符集转为mysql服务器的字符集设置，即表和字段的字符集

4 操作结果从表字段字符集转为character_set_connection

5character_set_connection转换成character_set_results设置

大概的转换过程我贴了《高性能mysql》一书中关于字符集转换的图

出现乱码原因，一般有3个：一是存入和取出的编码不一致，比如character_set_client和character_set_results不一致；二是虽然存入和取出的编码一致，但是期间有过字符集转换，但是转换过程并不是无损的，三是mysql客户端所在字符集设置存在问题，比如xshell等客户端软件的字符集编码问题。范例如下

character_set_client和character_set_results不一致必然引起的乱码

这时如果我们把character_set_results也设置成latin1，期间涉及到的”字符集测试“正好能够转换，所以不再乱码

这时我们再模拟一个，虽然存入和取出的字符集一致，但期间涉及到的字符集无法完整转换的case，因为utf8能够表示的字符数量远大于gbk，gbk无法完整转换成utf8，那么也会导致乱码

总结和建议

1 最好的最优的方案，个人感觉就是，别整那么麻烦，所有的字符集统一用utf8，如果需要用到emoji表情则统一使用utf8mb4，而校验规则统一不做设置，采用对应字符集的默认校验规则就好

2 在每一个查询前，通过set names utf8将character_set_client，character_set_connection和character_set_results都设置成utf8