Hijack

Home | MicroBlog | About | Contacts | Archive | RSS

MySQL字符集

整体来说，MySQL内部的字符集还是略微复杂的，而我们平时使用过程中也经常会遇到一些字符串乱码问题，没错，我就是遇到了这个问题，本想简单解决，却发现情况比预期的略微复杂。

这里希望本文可以让你真正找到你所遇到的问题的原因与解决方案。

字符集

首先需要介绍一下字符集的概念，我们知道计算机是只能存储二级制数据的，而字符集的作用就是帮助你将平时遇到的各种文字映射成二进制数据。将文字转换成二进制称为字符编码，将二进制转换成文字称为字符解码。

这里先介绍3种最最常见的字符集，方便理解概念。

ASCII

ASCII全称是American Standard Code for Information Interchange，是基于拉丁字母的一套电脑编码系统。但其只包含128个字符，包括基本英文字符，阿拉伯数字以及英文标点符号，所以其主要用于显示现代英语，但对于其他语言乃至Emoji表情符号等，ASCII就无能为力了。

编码举例：

'L' ->  01001100（十六进制：0x4C）

Latin1

也称作ISO 8859-1，Latin1是单字节编码，向下兼容ASCII，共收录了256个字符，在ASICC的基础上又扩充了128个西欧常用字符，可以说是对ASCII的一个补充。 256个字符就厉害了，一个字节由256个字符组成，这样它可以对任意单字节进行编码。

编码举例：

'L' ->  01001100（十六进制：0x4C）
'à' ->  11100000（十六进制：0xE0）

UTF-8

UTF-8（8-bit Unicode Transformation Format）是一种针对Unicode的可变长度字元编码，可以说UTF-8是Unicode编码的一种实现，它整理、编码了世界上大部分的文字系统，使得电脑可以用更为简单的方式来呈现和处理文字，也就是说他可以很好的支持中文以及其他各国语言。自2009年以来，UTF-8一直是万维网的最主要的编码形式，它同样也向下兼容ASCII，也正是由于它的普及，我们平时经常见到，甚至正在使用的大都是这种字符集。

编码举例：

'L' ->  01001100（十六进制：0x4C）
'啊' ->  111001011001010110001010（十六进制：0xE5958A）

MySQL中的字符集

为了便于解释，先安装一个MySQL Server(5.7) 和 MySQL CLI(MyCLI)。

首先查看一下当前MySQL中的一些字符集相关的变量

mysql root@localhost:(none)> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
|--------------------------+----------------------------|
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set
Time: 0.009s

MySQl 5.7默认使用的字符集 就是Latin1

我们在建表，建库的时候，可以在数据库、表、字段的后面加上字符集相关参数。 这里我们创建一个名为testdb的数据库和一张名为test的表：

MySQL root@localhost:testdb> CREATE DATABASE `testdb` DEFAULT CHARACTER SET latin1;
MySQL root@localhost:testdb> USE `testdb`;
MySQL root@localhost:testdb> CREATE TABLE `test` (
  `latin1_name` varchar(255) CHARACTER SET latin1,
  `utf8mb4_name` varchar(225) CHARACTER SET utf8mb4,
  `default_name` varchar(225)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8

对于某个字段来说，它所使用的字符集的优先级为：字段字符集->表字符集->数据库字符集->服务端字符集。

根据此规则以及上面的MySQl字符集变量，对于testdb中未显示指定字符集的表或者字段，将默认使用latin1字符集, test表使用utf8字符集，表中各字段的字符集如下：

字段名 字符集
latin_name latin1
utf8mb4_name utf8mb4
default_name utf8

utf8与utf8mb4

这里出现了一个叫utf8mb4的字符集，实际上这才是MySQL中真正的utf8字符集，而MySQL中的utf8实际上是一个阉割版的utf8字符集，它是utf8mb3，他每个字符最长只有3字节，是不完全的，这是MySQL的一个历史问题。

查询中字符集的转换过程

这里重点介绍下在Query请求中，character_set_connection、character_set_client以及character_set_results，这三个变量与请求的字符集的转换关系，整个过程如下图：

图片来自此文

151539928.jpeg

存储数据使用Latin1字符集会有什么问题？

首先我们要明确，如果把utf8当做字节流处理，latin1是可以对其进行编码的，因为latin1是单字节编码的，但是反之则不行。所以无论客户端和数据存储使用什么编码，只要将character_set_connection、character_set_client以及character_set_results都设置成latin1，那么一切都会是正确的，举例来说：

MySQL root@localhost:(none)> set names latin1;

MySQL root@localhost:(none)> SHOW VARIABLES LIKE 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL root@localhost:testdb> SHOW FULL COLUMNS FROM test;
| Field        | Type         | Collation          | Null | Key | Default | Extra | Privileges                      | Comment |
+--------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+
| latin1_name  | varchar(255) | latin1_swedish_ci  | YES  |     | <null>  |       | select,insert,update,references |         |
| utf8mb4_name | varchar(225) | utf8mb4_general_ci | YES  |     | <null>  |       | select,insert,update,references |         |
| default_name | varchar(225) | utf8_general_ci    | YES  |     | <null>  |       | select,insert,update,references |         |
+--------------+--------------+--------------------+------+-----+---------+-------+---------------------------------+---------+

MySQL root@localhost:testdb> INSERT INTO `test` (`latin1_name`, `default_name`) VALUES ('红', '红');

MySQL root@localhost:testdb> select `latin1_name`, `default_name` from test WHERE `latin1_name`='红';
+-------------+--------------+
| latin1_name | default_name |
+-------------+--------------+
| 红          | 红           |
+-------------+--------------+

我们可以看出虽然latin1_name和default_name分是latin1和utf8编码，但，我们对于中文红的都能够正确读取和显示。

值得一提的是，对于default_name，因为客户端和存储数据都使用的是utf8，但是client, connection, results使用的却是latin1，这会导致字符经过了两次utf8编码（Double Encoding）

  1. Client将其编码成utf8传输到MySQL Server
  2. 而MySQL Server却认为这是latin1
  3. 由于存储数据使用utf8字符集，这时MySQL Server把其当做Latin1字节流，再次对齐进行utf8编码。

上面说的二次编码问题，不会对读取和显示造成什么影响，但是如果需要对该字段进行排序，可能会导致，排序结果不符合你的预期。

比较规则

说到排序与比较，这里也浅提一嘴比较规则。 对于不同的字符集会有不同的比较规则，当你设置好字符集时，MySQL会自动帮你设置好该字符集的默认比较规则，一般情况我们不需要去修改（utf8默认为utf8_general_ci）。除了一些特殊情况：比如你需要区分大小写，轻重音等等。

总结

说了这么多，其实你只要无脑的将相关的编码都设置成utf8mb4，在现代应用中，几乎是不会遇到乱码问题的。

最后力荐一篇Mysql字符集相关Blog：sql.rjweb.org，它解决了我在MySQL字符集上的的很多困惑。

参考

Build with Jekyll and true minimal theme