mysql 字符集乱码前世今生

内容概述

在MySQL的使用过程中，了解字符集、字符序的概念，以及不同设置对数据存储、比较的影响非常重要。不少同学在日常工作中遇到的“乱码”问题，很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。

本文由浅入深，分别介绍了如下内容：

1. 字符集、字符序的基本概念及联系
2. MySQL的字符集和字符序
3. 如何设置mysql字符集、字符序
4. MySQL数据库中字符集转换流程

字符集、字符序的基本概念及联系

1. 什么是字符集、字符序？简单的来说：

字符集（character set）：定义了字符以及字符的编码。
字符序（collation）：定义了字符的比较规则。

举个例子：

有四个字符：A、B、a、b，这四个字符的编码分别是A = 0, B = 1, a = 2, b = 3。这里编码就构成了字符集（character set）。

如果我们想比较两个字符的大小呢？比如A、B，或者a、b，最直观的比较方式是采用它们的编码，比如因为0 < 1，所以 A < B。

另外，对于A、a，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说 A == a。

这上面定义了两条比较规则，这些比较规则的集合就是collation。

同样是大写字符、小写字符，则比较他们的编码大小；
如果两个字符为大小写关系，则它们相等。

MySQL的字符集和字符序

MySQL支持多种字符集 与 字符序。

1. 一个字符集对应至少一种字符序（一般是1对多）。
2. 两个不同的字符集不能有相同的字符序。
3. 每个字符集都有默认的字符序。

1. 查看mysql支持的字符集和字符序

// 支持的字符集
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
...省略

// 支持的字符序
mysql> SHOW COLLATION WHERE Charset = 'utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
...略

3. 字符序的命名规范

字符序的命名，以其对应的字符集作为前缀，如下所示。比如字符序utf8_general_ci，标明它是字符集utf8的字符序。
排序方式的命名规则为：字符集名字语言后缀，其中各个典型后缀的含义如下：
1）_ci：不区分大小写的排序方式
2）_cs：区分大小写的排序方式
3）_bin：二进制排序方式，大小比较将根据字符编码，不涉及人类语言，因此_bin的排序方式不包含人类语言

如何设置mysql字符集、字符序

1. 查看当前的字符集和字符序

设置之前，先来看看如何查看当前的字符集

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

这些系统变量是用来控制客户和服务器之间的字符集转换工作的。

1）character_set_server：mysql server默认字符集。

2）character_set_database：数据库默认字符集。

3）character_set_client：MySQL server假定客户端发送的查询使用的字符集。

4）character_set_connection：MySQL Server接收客户端发布的查询请求后，将其转换为character_set_connection变量指定的字符集。

5）character_set_results：mysql server把结果集和错误信息转换为character_set_results指定的字符集，并发送给客户端。

6）character_set_system：系统元数据(字段名等)字符集

查看指定数据库的字符集和字符徐设置

mysql> SHOW CREATE DATABASE test_schema;
+-------------+----------------------------------------------------------------------+
| Database    | Create Database                                                      |
+-------------+----------------------------------------------------------------------+
| test_schema | CREATE DATABASE `test_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

2. 修改和设置MySQL服务器级别字符集的一些方法

我们在这里只介绍一些常用的

1） 在参数文件my.cnf中修改配置

[mysqld]
character_set_server=utf8

    --影响参数：character_set_server 和 character_set_database

    --注意：修改后要重启数据库才能生效。

[client]

default-character-set=utf8

    --影响参数：character_set_client，character_set_connection 和character_set_results。

    --注意：修改后无需重启数据库。

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

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

--影响参数：set character_set_client，set character_set_connection，set character_set_results。

3）临时指定

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;

4）mysql客户端使用：

mysql> set names utf8;

// 等同于 
set character_set_client=utf8;
set character_set_connection=utf8;
set character_set_results=utf8;
=======================
mysql> set character set utf8;

// 等同于
set character_set_client=utf8;
set character_set_results=utf8;
set collation_connection=@@collation_database;

MySQL数据库中字符集转换流程

1、MySQL Server收到请求时将请求数据从character_set_client转换为character_set_connection；
2、进行内部操作前将请求数据从character_set_connection转换为内部操作字符集，其确定方法如下：
使用每个数据字段的CHARACTER SET设定值；
若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
若上述值不存在，则使用character_set_server设定值。
3、将操作结果从内部操作字符集转换为character_set_results。

下图源自于《高性能MySQL》中关于字符集转换的图解：

字符集转换的图解

建议配置

这个是从其他地方copy过来的配置，优化过的，建议自己的也配置成这样

建议配置

[mysqld]
character_set_server=utf8mb4

[client]
default-character-set=utf8

参考文件：

1. MySQL字符集详解
2. mysql手册