MySQL运维07-字符集

1、MySQL的字符集

1.1、什么是字符集

字符集（character set）是一套符号和编码。假设我们有一个字母表使用了4个字母：‘A’、‘B’、‘a’、‘b’。我们现在为每个字母赋予一个数值：‘A’=0，‘B’=1，‘a’=2，‘b’=3，即字母’A’是一个符号，数字0是’A’的编码，那么这4个字母和它们的编码组合在一起就是一个字符集。我们可以认为字符集是字符的二进制的编码方式，即二进制编码到一套符号的映射。

1.2、字符集在MySQL中的作用

1. 基于字符集来存储字符串。
2. 基于字符集，使用校对规则来比较字符串。校对规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则。

1.3、字符集和校对规则的级别

1. MySQL字符集和校对规则有4个级别：服务器级、数据库级、表级、连接级。更低级别的配置会继承更高级别的配置。例如，如果创建一个数据库，不指定字符集，那么它会继承服务器级的默认字符集。
2. 对于生产环境，建议在表级别指定默认的字符集，以避免歧义或继承了错的数据库默认字符集。

1.4、MySQL中与字符集相关命令

1. 列出所有可用的字符集。

mysql>SHOW CHARACTER SET;

2. 列出utf8字符集的校对规则。

mysql>SHOW COLLATION LIKE 'utf8%';

1.5、字符长度

1. LENGTH()返回值为字符串的字节长度，单位为字节。一个多字节字符算作多字节。
2. CHAR_LENGTH()返回值为字符串的字符长度，长度的单位为字符。一个多字节字符算作一个单字符。
3. 例如：对于一个包含了5个二字节的字符集，LENGTH()返回值为10，而CHAR_LENGTH()的返回值为5。

2、UTF-8字符集

2.1、Unicode编码

1. 因为现存编码不能在多语言电脑环境中使用，而且字符数有局限。所以诞生了Unicode（统一码、万国码、国际码、单一码）。Unicode是计算机科学领域里的一项业界标准。它对世界上大部分的文字系统进行了整理、编码，使得电脑可以用更为简单的方式来呈现和处理文字。
2. 一个字符的Unicode编码是确定的。

2.2、UTF-8字符集

1. 一个字符的Unicode编码是确定的，但Unicode在不同系统平台的实现方式不都是一致的。Unicode的实现方式称为Unicode转换格式（Unicode Transformation Format，UTF）。
2. UTF-8编码是一种变长编码，思想是不同的Unicode字符采用变长字节序列编码：基本拉丁字母、数字和标点符号使用一个字节。大多数的欧洲和中东手写字母适合两个字节序列。韩语、中文和日本象形文字使用三个字节序列。
3. utf8是MySQL存储Unicode数据的一种可选方法，MySQL还有其他的存储Unicode数据的字符集。utf8字符集的最大长度是3个字节（中文3个字节，对于英文数字仍然使用一个字节），默认校对（排序）规则为utf8_general_ci（不区分大小写）。
4. 超集、子集：有字符集A、B。如果B支持的所有字符A都支持，那么字符集A是字符集B的超集。如果A是B的超集，那么字符集B是字符集A的子集。比如，GBK字符集是GB2312字符集的超集，它们又都是ASCII字符集的超集。

2.3、生产环境建议使用utf8字符集

1. 默认情况下，MySQL的字符集是latin1（ISO_8859_1）。latin1字符集是单字节编码，应用于英文系列，最多能表示的字符范围是0_{255（编码范围是0x00}0xFF），其中0x00~0x7F之间和ASCII码完全一致，因此它是向下兼容ASCII的。latin1字符有限，如用来存储中文、日文、韩文、希伯来文等语言时往往会导致乱码，为了避免乱码，支持国际化，建议是生产环境都统一使用utf8字符集。大家都统一使用utf8字符集，将一劳永逸地避免各种乱码问题。一个数据库如果存在各种字符集，就会很容易出错，也会大大提高开发的难度。
2. utf8字符集也有弊端，主要就是空间的消耗。有时，我们可能为了节省空间（如果空间真的是一个需要考虑的因素）而选择其他字符集（如用GBK存储汉字），对于大批量的机器，特定的服务选择特定的字符集，这种情况下所节省的空间也是很可观的，但对于一般的中小型公司，建议统一使用utf8，一劳永逸地解决乱码问题是更明智的选择。

3、MySQLServer和Client端通信时的字符集

3.1、MySQLClient的字符集配置

绝大部分MySQL客户端都不具备同时支持多种字符集的能力，每次都只能使用一种字符集。客户和服务器之间的字符集转换工作是由如下几个MySQL系统变量来控制的。

• character_set_server：MySQL Server默认字符集。
• character_set_database：数据库默认字符集。
• character_set_client：MySQL Server假定客户端发送的查询使用的字符集。
• character_set_connection：MySQL Server接收客户端发布的查询后，将其转换为character_set_connection变量指定的字符集。
• character_set_result：MySQL Server把结果集和错误信息转换为character_set_resul t指定的字符集，并发送给客户端。

3.2、MySQLServer和Client端通信时的字符集转换过程

下图是字符集的转换过程，当一个客户端和数据库打交道时，客户端、连接、操作系统、数据库、输出结果都有自己的字符集设置，如果字符集不一致，那么就可能需要进行转换，一般情况下，目标字符集应确保是源字符集的超集，以确保转换正常，如果目标字符集不能容纳源字符集的编码或设置错了字符集，那么转换会导致乱码。

3.3、MySQL客户端字符集相关常用操作命令

1. 通过MySQL客户端导入数据时，在使用“mysql>source/path/imp_data.sql”命令的过程中有时可能会出现乱码，这时可能需要先运行SET NAMES x语句设置字符集。SET NAMES x语句与下面这3个语句是等价的。

mysql> SET character_set_client = x;
mysql> SET character_set_connection = x;
mysql> SET character_set_results = x;

2. 有些客户端命令支持“–default-character-set”选项，此选项允许用户连接时设置字符集。它等同于以下这3条语句。

mysql> SET character_set_client = x;
mysql> SET character_set_connection = x;
mysql> SET character_set_results = x;

4、总结

1. 所谓字符集，就是一组字符和每个字符对应编码的集合，时一组字符的二进制编码方式。MySQL需要基于字符集中的字符和编码对应方式，对数据进行存储和排序。
2. MySQL的字符集定义级别有服务器、数据库、表和连接四级，下一级会继承上一级的字符集默认设置。
3. 一个字符的Unicode编码是唯一的，所以Unicode交统一码。但Unicode在不同系统平台的实现方式是不一致的，这个实现方式称为Unicode转换格式（Unicode Transformation Format，UTF）。UTF-8编码是一种变长编码，拉丁字母、数字和标点符号的编码长度为1个字节，欧洲和中东字母编码长度为2个字节，中日韩文字的编码长度为3个字节。
4. MySQL数据库建议默认使用UTF-8编码，并建议在表级别设置字符集。