Mysql 中文乱码 —— 关于字符集的问题

Mysql 中文乱码 —— 关于字符集的问题

本文是自己碰到此问题，通过其他博客浏览收集的资料进行了整合。

一、字符集和校验规则

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

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

在MySQL中，字符集的概念和编码方案被看做是同义词，一个字符集是一个转换表和一个编码方案的组合。

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

目前支持中文的常用的字符集有UTF-8、GBK、GB2312等，MYSQL服务器支持多种字符集，在同一台服务器、同一个数据库或者同一个数据表中的不同字段都可以使用不同的字符集。

mysql的字符集和校对规则有四个级别的默认设置：服务器级、数据库级、数据表级、字段级。它们分别在不同的地方设置，作用也不相同。

二、校对规则

mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

名词解释：

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

排序方式的命名规则为：字符集名字_语言_后缀，其中各个典型后缀的含义如下：

1. _ci：不区分大小写的排序方式
2. _cs：区分大小写的排序方式
3. _bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言

utf8mb4是utf8的超集,MySQL在5.5.3之后增加了utf8mb4的编码,mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。

mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面(BMP)。包括 Emoji 表情(Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上)，和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

三、数据库的字符集

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

名词解释：

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，不需要设置。这个字符集用于数据库对象（如表和列）的名字，也用于存储在目录表中的函数的名字。

四、字符集设置

1.服务器级

在my.cnf中设置

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4

2. 数据库级

在创建数据库时指定字符集和校队规则

CREATE DATABASE IF NOT EXISTS database_name DEFAULT CHARSET utf8 COLLATE utf8;

查看数据库字符集和校队规则

show variables like 'character_set_database';
show variables like 'collation_database';

3. 表级别、列级别

创建数据表时指定字符集

create table table_name (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`),
)default charset=utf8mb4;

查看数据表的字符集

show create table table_name;

4. 连接级别

三、MySQL数据库乱码原因解析及案例

1.产生乱码的根本原因

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

数据库字体没有设置正确，如果数据库字体设置不正确，那么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个字节原封不动返回，产生乱码……

2.1 场景二 丢失数据

向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8 插入时根据连接字符集设置，character_set_client、character_set_connection和character_set_results均为utf8； 插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的Unicode字 符，会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。

乱码终极解决方案

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格式显示中文的。