从一个慢查询到MySQL字符集编码

2022-12-25 16:31:05

原文:从一个慢查询到MySQL字符集编码

从一个慢查询到MySQL字符集编码

目录

1. 问题起源

最近在完成一个线上日志修复工作的过程中遇到了一个意想不到的慢查询。当时使用的SQL以及表结构其实都很简单，而且在关键的字段上也有索引，但是MySQL的执行计划就是跑出来了Range checked for each record (index map: 0x1)。如下为问题中的表结构定义和执行计划（删减了其他字段，留下了关键的部分）:

Create Table: CREATE TABLE `Order1` (
  `orderid` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `productid` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  KEY `productid` (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Create Table: CREATE TABLE `Product` (
  `productid` varchar(255) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  KEY (`productid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> explain select * from  Order1 left join Product using (productid ) ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | Order1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                           |
|  1 | SIMPLE      | Product | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

正常情况下我们希望MySQL使用到表中定义的索引productid，并且执行计划应该是如下的情形：

mysql> explain select * from  Order1 left join Product using (productid ) ;
+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table   | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | Order1  | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | Product | NULL       | eq_ref | PRIMARY       | PRIMARY | 257     | Order1.productid |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

但是最后MySQL的执行过程就是没有使用索引，甚至于强制索引force index 也没有用：

mysql> explain select * from  Order1 left  join Product force index (productid) on Order1.productid = Product.productid ;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | Order1  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                           |
|  1 | SIMPLE      | Product | NULL       | ALL  | productid     | NULL | NULL    | NULL |    1 |   100.00 | Range checked for each record (index map: 0x1) |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+------------------------------------------------+
2 rows in set, 2 warnings (0.00 sec)

当时有些懵逼没太明白是因为什么。后面经过一顿GOOGLE之后，明白了其实是字符集排序规则导致的。观察可以发现两张表的字符集排序规则其实是不一样的。Product默认字符集是latin1 ，在MySQL中默认字符集的排序规则是latin1_swedish_ci，而Order1表中的productid 的排序规则是latin1_bin。因此对于MySQL来说没法使用索引进行join，只能使用Range checked for each record的方式来完成查询。由此可见在SQL执行的过程中，字符集和字符集排序规则对于执行计划也是相当重要的。而在MySQL数据库的字符集设置相当灵活和复杂，因此经常容易导致各种问题（例如索引失效，乱码，字符集转换损失性能等），因此本文专门整理和介绍下MySQL字符集相关的内容，为DBA同学和开发同学在实际工作中提供一些参考信息。

2. MySQL字符集和字符集排序规则

2.1 字符集相关概念

在谈起数据库的字符集之前，首先需要了解下字符，字符集和字符编码的概念。相信很多人在一开始的时候也对这些概念比较混乱。（以下的概念说明摘自*和百度百科）

  1. 字符

在电脑和电信领域中，字符（Character）是一个信息单位。对使用字母系统或音节文字等自然语言，它大约对应为一个音位、类音位的单位或符号。简单来讲就是一个汉字、假名、韩文字……，或是一个英文、其他西方语言的字母[1]。

  1. 字符集

字符集（Character set）是多个字符的集合，字符集种类较多，每个字符集包含的字符个数不同，常见字符集名称：ASCII字符集、GB2312字符集、BIG5字符集、 GB18030字符集、Unicode字符集等[2]。同时字符集还为每个集合中的字符规定了一套编码规则，将每个字符与一个编码做映射。

  1. 字符编码

字符编码（英语：Character encoding）也称字集码，是把字符集中的字符编码为指定集合中某一对象（例如：比特模式、自然数序列、8位组或者电脉冲），以便文本在计算机中存储和通过通信网络的传递[3]。

由上面的概念可以见得，字符集就是一组字符的抽象集合，也可以称为字符集合，例如所有的汉字可以算成一个字符集，所有的英文字母也可以算成一个字符集。字符集只是逻辑上的概念，如何将字符集映射到计算机世界中具体的表现呢？就是靠字符编码。在计算机中信息是以一个个0和1表示的，因此字符集最后也是需要在计算机世界中表现成二进制的形式存储。字符集编码就给出了从逻辑上的字符集到二进制编码的映射。例如我们经常在工作中听到unicode 和utf-8，unicode就是一个字符集，而utf-8是unicode 字符集在计算机中具体的实现方式。换句话说，字符集规定了有多少的字符，每个字符的编码是多少(例如ASCII码表中，01000011这个二进制对应的十进制是67，代表的是C)，而字符集编码将字符集中规定的编码转换成最终的二进制格式(比如ASCII码表中C的编码是67，可以使用单字节0x43表示这种编码方案表示，也可以使用多个字节，例如0x0043来表示)。

从一个慢查询到MySQL字符集编码

ps:关于字符集相关的更多介绍可以参见如下的参考文献：

[1].字符集与排序规则概念

[2].刨根究底字符编码之一——关键术语解释(上)

[3].字符编码-教程(1)-概述与基本知识

[4].程序员必备：彻底弄懂常见的7种中文字符编码

2.2 MySQL中的字符集和字符集排序规则

2.2.1 字符集和字符集排序基本概念

上一节简单介绍了字符集相关的概念，本节开始讲介绍MySQL的字符集以及字符集排序规则相关内容。一般而言MySQL中的字符集和前文介绍的字符集没有任何的区别。包括了工作中常用的字符集例如Latin1、GBK、GB2312、BIG5、UTF8、UTF8MB4、UTF16、UTF32等等。通过命令SHOW CHARACTER SET，可以看到MySQL支持的所有字符集：

+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

输出的第一列是字符集名称，第二列是字符集的描述，第三列是字符集默认的排序规则，第四列表示一个字符集的一个字符最大的字节数。这在里着重介绍下字符集的排序规则。首先在MySQL的官网文档中，排序规则(collations，下文都称之为collation)的定义是：

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

翻译过来就是collation就是一套规则，用来比较字符集中的字符。那么怎么理解这个比较字符集中的字符呢？文档给出了一个例子，例如有一个简单的字符集包括了如下的几个字符：a b A B。为了能够比较每个字符，我们给每个字符都附上一个编码，比如a=0，b=1，A=2，B=3。那么在排序比如order by 或者比较 两个字符是否相等的时候，自然可以使用这个编码来进行，例如order by 的时候就应该是 a<b<A<B ，而比较大小的时候是a<A 。这个规则就算做是一个collation，也是最简单的规则。但是如果今天不需要大小写敏感，那么a就是等于A ，所以这个规则就需要做下改变，例如a 和 A 的编码都变成0 ，然后order by的时候a和A不分先后，这样子的规则可以称之为大小不敏感。再比如世界上有些语言例如德语，会有口音敏感或者不敏感之分(MySQL文档上给出的原文是accent-sensitive ，我是直译过来的，如果有更好的翻译请大家告诉我)，那么会可能存在Ö=OE这样子的情况，因此会有collation是口音敏感或者不敏感[5]。

Suppose that we have an alphabet with four letters: A, B, a, b. We give each letter a number: A = 0, B = 1, a = 2, b = 3. The letter A is a symbol, the number 0 is the encoding for A, and the combination of all four letters and their encodings is a character set.

Suppose that we want to compare two string values, A and B. The simplest way to do this is to look at the encodings: 0 for A and 1 for B. Because 0 is less than 1, we say A is less than B. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters a and b as equivalent to A and B; (2) then compare the encodings. We call this a case-insensitive collation. It is a little more complex than a binary collation.

In real life, most character sets have many characters: not just A and B but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules, not just for whether to distinguish lettercase, but also for whether to distinguish accents (an “accent” is a mark attached to a character as in German Ö), and for multiple-character mappings (such as the rule that Ö = OE in one of the two German collations).

这么看来collation其实就是定义了字符集中的字符需要怎么被比较以及如何排序的问题。那么在MySQL中有多少中collation呢?通过 SHOW COLLATION 命令可以看到(全部内容就不贴出来了，请大家自行执行命令)：

+--------------------------+----------+-----+---------+----------+---------+
| Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci          | big5     |   1 | Yes     | Yes      |       1 |
| big5_bin                 | big5     |  84 |         | Yes      |       1 |
| dec8_swedish_ci          | dec8     |   3 | Yes     | Yes      |       1 |
| dec8_bin                 | dec8     |  69 |         | Yes      |       1 |
| cp850_general_ci         | cp850    |   4 | Yes     | Yes      |       1 |
| cp850_bin                | cp850    |  80 |         | Yes      |       1 |
... ...
| geostd8_general_ci       | geostd8  |  92 | Yes     | Yes      |       1 |
| geostd8_bin              | geostd8  |  93 |         | Yes      |       1 |
| cp932_japanese_ci        | cp932    |  95 | Yes     | Yes      |       1 |
| cp932_bin                | cp932    |  96 |         | Yes      |       1 |
| eucjpms_japanese_ci      | eucjpms  |  97 | Yes     | Yes      |       1 |
| eucjpms_bin              | eucjpms  |  98 |         | Yes      |       1 |
| gb18030_chinese_ci       | gb18030  | 248 | Yes     | Yes      |       2 |
| gb18030_bin              | gb18030  | 249 |         | Yes      |       1 |
| gb18030_unicode_520_ci   | gb18030  | 250 |         | Yes      |       8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)

可以看到这些collation都有一种统一的格式：字符集+语言名称+后缀。前面两个部分好理解，来看下最后一个部分所代表的含义：

后缀 含义
_ai Accent-insensitive 口音不敏感
_as Accent-sensitive 口音敏感
_ci Case-insensitive 大小写不敏感
_cs Case-sensitive 大小写敏感
_bin Binary 二进制排序

在这里MySQL给出了一些解释：对于是ci后缀的collation(大小写不敏感)也意味着Accent-insensitive(口音不敏感)。同理对于是cs后缀的collation(大小写敏感)也意味着Accent-sensitive(口音敏感)[6]。

For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. If a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as. For example, latin1_general_ci is explicitly case-insensitive and implicitly accent-insensitive, and latin1_general_cs is explicitly case-sensitive and implicitly accent-sensitive.

这里举一个实际的例子说明下这个collation的cs和ci后缀的作用(一般在工作中经常遇到的是这两个collation)。假设当前有这么一张表：

mysql> show create table test_collation \G
*************************** 1. row ***************************
       Table: test_collation
Create Table: CREATE TABLE `test_collation` (
  `c` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
  `c1` varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

其中的数据是：

mysql> select * from test_collation ;
+------+------+
| c    | c1   |
+------+------+
| a    | A    |
| b    | B    |
| B    | b    |
+------+------+
3 rows in set (0.00 sec)

那么我们分别执行如下的几个SQL：

1. select * from test_collation where c = 'b';
2. select * from test_collation where c1 = 'b';
3. select c from test_collation group by c;
4. select c1 from test_collation group by c1;

分别得到如下的几个结果：

1. select * from test_collation where c = 'b';
+------+------+
| c    | c1   |
+------+------+
| b    | B    |
+------+------+
1 row in set (0.00 sec)

2. select * from test_collation where c1 = 'b';
+------+------+
| c    | c1   |
+------+------+
| b    | B    |
| B    | b    |
+------+------+
2 rows in set (0.00 sec)

3. select c from test_collation group by c;
+------+
| c    |
+------+
| a    |
| B    |
| b    |
+------+
3 rows in set (0.00 sec)

4. select c1 from test_collation group by c1;
+------+
| c1   |
+------+
| A    |
| B    |
+------+
2 rows in set (0.00 sec)

c 是大小写敏感的列，cs 是大小写不敏感的列。通过实验结果可以可以得到如下几个结论：

  1. 大小写敏感的情况下，b和B(大小和小写是不等的)。
  2. 大小写不敏感的话，查询b会得到B和b的结果(相信很多DBA在工作中也遇到个这个问题，明明应该一条记录，怎么就查出了两条)。
  3. 对于group by，cs会区分b和B，ci则会把两个字符当做一个，这点从上面1-2可以推测出。

ps 关于collation的问题，还可以参考这篇文章:

[1].How to choose your MySQL encoding and collation。

2.2.2 collation bin和Binary strings的不同

在前文的介绍中，我们忽略了一种类型的collation: _bin。这种排序规则是按照字符串的二进制值进行排序和比较[7]。

For the binary collation of the binary character set, comparisons are based on numeric byte values. For the _bin collation of a nonbinary character set, comparisons are based on numeric character code values, which differ from byte values for multibyte characters. For information about the differences between the binary collation of the binary character set and the _bin collations of nonbinary character

我们可能会想起在MySQL中有另外的一种数据类型叫做Binary strings (as stored using the BINARY, VARBINARY, and BLOB data types)，那么这个Binary strings 和 collation 中的_bin 又有什么区别呢？

  1. 从用于排序比较的数据单元(The Unit for Comparison and Sorting)

Binary strings是一连串字节序，比较和排序是基于字节值的。而collation 中的_bin对应的字符串一般而言是多字节的(每个字符由N个字节组成，因此是多个字节)，排序和比较的规则是基于每个字符的字节值。

  1. 字符集的转换(Character Set Conversion)

对于具有_bin 后缀的collation的字符集，可以在多种情况下自动转换成其他的字符集。对于Binary strings而言，只能 copied byte-wise按照字节的值一个个复制。

  1. 大小写转换(Lettercase Conversion)

对于具有_bin 后缀的collation的字符集可以进行字符的大小写转换，而Binary strings会直接忽略这个事情，除非先把它转成某一个字符集。例如：

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
mysql> SELECT LOWER('aA'), UPPER('zZ');
+-------------+-------------+
| LOWER('aA') | UPPER('zZ') |
+-------------+-------------+
| aa          | ZZ          |
+-------------+-------------+
mysql> SET NAMES binary;
mysql> SELECT LOWER('aA'), LOWER(CONVERT('aA' USING utf8mb4));
+-------------+------------------------------------+
| LOWER('aA') | LOWER(CONVERT('aA' USING utf8mb4)) |
+-------------+------------------------------------+
| aA          | aa                                 |
+-------------+------------------------------------+
  1. 对于字符串最后的空格的处理(Trailing Space Handling in Comparisons)

对于具有_bin 后缀的collation的字符集，字符串最后的一个空格，在比较字符串是否相等的时候不会被计入：

mysql> SET NAMES utf8 COLLATE utf8_bin;
Query OK, 0 rows affected (0.00 sec)
mysql>  SELECT 'a a ' = 'a a';
+----------------+
| 'a a ' = 'a a' |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

而对于Binary strings空格会被计入：

mysql> SET NAMES binary;
mysql> SELECT 'a ' = 'a';
+------------+
| 'a ' = 'a' |
+------------+
|          0 |
+------------+

而在插入数据的时候，_bin 后缀的collation的字符集会忽略空格，但是Binary strings不会：

mysql> CREATE TABLE t1 (
         a CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin,
         b BINARY(10)
       );
mysql> INSERT INTO t1 VALUES ('x','x');
mysql> INSERT INTO t1 VALUES ('x ','x ');
mysql> SELECT a, b, HEX(a), HEX(b) FROM t1;
+------+------------+--------+----------------------+
| a    | b          | HEX(a) | HEX(b)               |
+------+------------+--------+----------------------+
| x    | x          | 78     | 78000000000000000000 |
| x    | x          | 78     | 78200000000000000000 |
+------+------------+--------+----------------------+

2.3 字符集配置以及转换规则

前文我们介绍了字符集与字符集排序规则，本节开始介绍下MySQL中字符集配置相关的内容。在MySQL中字符集设置相当灵活和复杂，使用命令可以看到相关的几个配置如下：

+--------------------------+------------------------------------------+
| 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       | /home/mysql/mysql-5.7.18/share/charsets/ |
+--------------------------+------------------------------------------+

接下来介绍下每个配置：

  1. character_set_client : 客户端发送SQL语句给MySQL所使用的编码。这个值随每个客户端设置的不同而变化。

The character set for statements that arrive from the client. The session value of this variable is set using the character set requested by the client when the client connects to the server. (Many clients support a --default-character-set option to enable this character set to be specified explicitly. See also Section 10.4, “Connection Character Sets and Collations”.) The global value of the variable is used to set the session value in cases when the client-requested value is unknown or not available, or the server is configured to ignore client requests:

  • The client requests a character set not known to the server. For example, a Japanese-enabled client requests sjis when connecting to a server not configured with sjis support.
  • The client is from a version of MySQL older than MySQL 4.1, and thus does not request a character set.
  • mysqld was started with the --skip-character-set-client-handshake option, which causes it to ignore client character set configuration. This reproduces MySQL 4.0 behavior and is useful should you wish to upgrade the server without upgrading all the clients.

Some character sets cannot be used as the client character set. Attempting to use them as the character_set_client value produces an error. See Impermissible Client Character Sets.

  1. character_set_connection : MySQL连接字符集。其实一开始接触到这个配置的时候，我也是一脸懵逼为什么要有一个这样子的配置。客户端已经有字符集配置了，为什么还要在进入内部字符集之前设置一个这样子的字符集进行转换。后面我查了下发现不单单是我，国内外很多人都有这个疑问，比如这个贴子下[What is the purpose of character_set_connection?] 就对这个问题进行了讨论。后续我也仔细阅读了MySQL的问题，根据我自己的理解之所以设置这个参数，应该是针对于这种情况的：

假设MySQL当前没有character_set_connection这个参数，SQL语句在server端通过character_set_client参数解码之后变进入内部字符集进行比较，那么例如这样子的SQL中的字符串("ABC")的编码也会是character_set_client的编码值：select * from order where orderid ="ABC"。那么如果开发者想让"ABC"拥有其他编码怎么办，MySQL提供了一个叫做Character Set Introducers的[8]方法，可以这么指定 select * from order where orderid = _utf8 "ABC"。后面发现每个语句都写一个Character Set Introducers来指定编码太累了，于是提供了character_set_connection参数，对于没有Character Set Introducers的字符串，都编码成character_set_connection所指定的编码。

The character set used for literals specified without a character set introducer and for number-to-string conversion. For information about introducers, see Section 10.3.8, “Character Set Introducers”.

​ ps 关于character_set_connection参数的其他讨论，可以参见如下的几篇文章：

​ [1].mysql中character_set_connection的作用

​ [2].理解MySQL字符集

​ [3].What is the purpose of character_set_connection?

​ [4].彻底解决MySQL中的乱码问题

  1. character_set_database : 这个好理解，就是数据库的字符集。

The character set used by the default database. The server sets this variable whenever the default database changes. If there is no default database, the variable has the same value as character_set_server.

  1. character_set_filesystem : 文件系统字符集。 该变量用于解释引用文件名的字符串文字，例如在LOAD DATA INFILE和SELECT ... INTO OUTFILE语句和LOAD_FILE（）函数中。

The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA and SELECT ... INTO OUTFILE statements and the LOAD_FILE() function. Such file names are converted from character_set_client to character_set_filesystem before the file opening attempt occurs. The default value is binary, which means that no conversion occurs. For systems on which multibyte file names are permitted, a different value may be more appropriate. For example, if the system represents file names using UTF-8, set character_set_filesystem to 'utf8mb4'.

  1. character_set_results : SQL语句执行的结果集字符集。当查询执行完毕之后，返回给客户端的结果使用这个字符集编码。

The character set used for returning query results to the client. This includes result data such as column values, result metadata such as column names, and error messages.

  1. character_set_server : 整个服务器基本的字符集配置。如果创建数据库的时候不指定字符集就使用这个配置。

The servers default character set. See Section 10.15, “Character Set Configuration”. If you set this variable, you should also set collation_server to specify the collation for the character set.

  1. character_set_system : 系统元数据字符集，系统元数据(表名、字段名等)存储时使用的编码字符集，该字段和具体存储的数据无关。总是固定不变的UTF8字符集。

The character set used by the server for storing identifiers. The value is always utf8.

前面介绍了很多的关于字符集的配置，这些字符集配置之间也有一定的转换关系[9]:

从一个慢查询到MySQL字符集编码

对于Client发起的SQL，会使用 character_set_client进行编码。当SQL到了MySQL Server的时候，会使用character_set_client进行解码。之后如果character_set_client的编码与character_set_connection编码不一致的时候，会将请求的数据转换成character_set_connection的编码。在进行内部操作前会判断内部编码是否与character_set_connection一致，如果不一致则将character_set_connection的编码进行转换：

  • 使用每个数据字段的CHARACTER SET设定值；
  • 若上述值不存在，则使用对应数据表的DEFAULT CHARACTER SET设定值(MySQL扩展，非SQL标准)；
  • 若上述值不存在，则使用对应数据库的DEFAULT CHARACTER SET设定值；
  • 若上述值不存在，则使用character_set_server设定值。

3. MySQL字符集的一些问题

3.1 字符集配置对于Innodb引擎存储数据所带来的一点影响

MySQL对于一条记录的长度是有限制而且这个限制分为两层，在server层一条记录最大不能超过65535个字节；在Innodb引擎层因为记录都必须按照B树的格式组织，因此如果在一个page是16KB的配置下，最大的记录长度不能大于16KB/2=8K(如果一个page里面就只有一条记录，那么B树就退化成链表也就失去了B树的意义)。此外还要扣除page中的一些元数据的长度，最后实际一个记录的最大长度会小于8K(实际应该是8126 B)。如果一个记录的行大于8K怎么办，比如有一个字段用于存储文章的内容定义为varchar(3000)，然后是utf8mb4字符集(utf8mb4 是4个字节的，然后3000个字符最大就是12000个字节大约12K)。对于这种大字段Innodb会将其存储在溢出页(page overflow)中。不同的行格式对于溢出页的实现还有一定的区别：

  1. compact 格式: 如果字段长度小于768 Bytes则不会发生page overflow。而如果超过了768 Bytes，那么前768 Bytes 依然在数据页中，剩余的部分放在溢出页(off-page)中。同时当前页中增加一个 20 个字节的指针（即 SPACEID + PAGEID + OFFSET）和本地长度信息（2 个字节），共计 768 + 20 + 2 = 790 个字节存储在当前记录。

  2. compressed或dynamic格式: 这种格式下数据库会尽可能的存放数据在数据页中，只有当一个页没法存放2条数据的时候，会将最长的一列的所有数据放入溢出页，同时在原来的记录上保留20个字节的指针，而对text的数据类型会存放前40byte 在 数据页中。

根据上面的分析可以看出，字符集的设置对于表能够定义的列和记录的最大长度都有影响。例如文献[12]给出的一个例子:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used 
table type, not counting BLOBs, is 65535. This includes storage overhead, 
check the manual. You have to change some columns to TEXT or BLOBs

这里定义了的66000个字符并且是latin1的字符集，因此超过了65535个字节的限制。如果把这个表的字段减少一个就可以创建成功:

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),        f VARCHAR(10000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.00 sec)

那么换一个字符集比如utf8，那么创建就会失败：

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),        c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),        f VARCHAR(10000)) ENGINE=InnoDB CHARACTER SET utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql>

上面演示的例子都是server层面的限制，接下来看看引擎层面的限制:

mysql> create table t (a varchar(1000),a1 varchar(1000),a2 varchar(1000),a3 varchar(1000),a4 varchar(1000),a5 varchar(1000),a6 varchar(1000),a7 varchar(1000),a8 varchar(1000),a9 varchar(1000),a10 varchar(1000),a11 varchar(1000),a12 varchar(1000)) ENGINE=InnoDB   ROW_FORMAT=COMPACT CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t select repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000),repeat("a",1000);
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

在上面的例子中，建表的语句中构建了12个1000个字符的列，因为12*1000<65533所以在server层检查通过。但是因为定义的行格式是COMPACT，当列的数据超过768字节的时候会将前768字节放在数据页中，剩余的数据才放入溢出页中，所以整个插入的数据行是大于8126K的，在引擎层就被拒绝了。

ps 关于MySQL记录长度限制的讨论可以参见如下的几篇文字:

[1].【MySQL经典案例分析】关于数据行溢出由浅至深的探讨

[2]. 技术分析 | MySQL TEXT 字段的限制

[3]. 技术分享 | MySQL 字段长度限制的计算方法

3.2 MySQL中的UTF8和Latin1

为了能够支持中文以及一些其他的非英文文字，开发者经常使用utf8字符集。但是在MySQL中的utf8字符集存在一个问题，它最大的长度是三字节最大能编码的 Unicode 字符是 0xffff，仅仅只能支持Unicode 中的基本多文种平面（BMP）[11]。如果字符不在BMP里面，则会被截断并且造成乱码。例如当前有下面这样一张表：

CREATE TABLE `test_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

字符集设定的是utf8，然后插入字符"

上一篇：第十一周-翻译：Clustered Index Design Considerations


下一篇：全文搜索中的Mysql德语口音不敏感搜索