如下所示，在 MySQL 中，有8个与字符集设置相关的选项。如果没有仔细阅读 MySQL 字符集设置相关官档，很难知道这些配置的用途。此外，对某些选项，除非有进一步的测试，否则很难知道 MySQL 是如何使用它们的。
mysql> show variables like 'character_set%';
+--------------------------+-------------------------------------+
| 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 | utf8mb3 |
| character_sets_dir | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+不要害怕——我将通过分组和解释来简化这些设置的目的。我还将使用示例来说明 MySQL 如何使用和处理这些字符集选项，以便用户可以减少数据丢失和错误的发生。最后，我将解释如何使用命令“set names”来组织必要的配置以使其相同。
将选项分组
我们将8个选项分为三组：
-
• G1.其他；
-
• G2.定义数据的字符集（表中的列）；
-
• G3. 在字符集处理过程中传输/解释。
G1.其他
G1.1 character_sets_dir: 显而易见，安装动态加载的字符集的目录。 G1.2 character_set_filesystem: 当 MySQL 需要处理文件时，它需要文件名，在打开文件之前，文件名将从character_set_client转化为character_set_filesystem。默认值为binary，这意味着不会发生转换。在大多数情况下，请将其保留为默认值binary，除非您确认需要更改。
G2.定义数据的字符集（表中的列）
RDBMS（例如 MySQL ）中的最小数据单位是表的一列。当列用于存储字符集合（即字符串、例如 varchar()、char()和text等）时，MySQL 需要知道这些字符属于哪个字符集，以便 MySQL 能够正确存储和解释它们。
G2中三个选项的唯一目的是帮助最终以分层方式定义列的字符集。这三个选项是character_set_system、character_set_server和character_set_database。
如果未指定服务器字符集和排序规则（character_set_server），则使用系统字符集和排序规则（character_set_system）作为默认值。
如果未指定数据库字符集和排序规则（character_set_database），则使用服务器字符集和排序规则（character_set_server）作为默认值。
如果CREATE database 语句中未指定数据库字符集和排序规则，则数据库字符集和排序规则（character_set_database）将用作默认值。如果CREATE TABLE语句中未指定表的字符集和排序规则，则数据库的字符集与排序规则将用作表的默认值。
如果CREATE TABLE语句中未指定字符串列的字符集和排序规则，则表的字符集与排序规则将用作表的字符串列的默认值。
换句话说，如果在CREATE TABLE语句中定义列的字符集和排序规则，则忽略上述所有设置。如：
mysql> CREATE TABLE t1 ( c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 VARCHAR(100) COLLATE utf8mb4_general_ci,
c3 VARCHAR(100) ) DEFAULT CHARACTER SET=latin1;
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME, CHARACTER_SET_NAME from information_schema.columns where table_schema='test' and table_name='t1';
+--------------+------------+-------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CHARACTER_SET_NAME |
+--------------+------------+-------------+--------------------+
| test | t1 | c1 | NULL |
| test | t1 | c2 | utf8mb4 |
| test | t1 | c3 | latin1 |
+--------------+------------+-------------+--------------------+在上面的示例中，我们没有为列c3指定字符集。因此，默认情况下，它将使用 latin1（在CREATE TABLE中定义）作为其字符集。然而，我们将列c2的排序规则指定为utf8mb4_general_ci，因此它使用utf8mb4作为其字符集，从而忽略了字符集latin1（这也是在CREATE TABLE中为表定义的）。
更多细节，阅读以下官档： https://dev.mysql.com/doc/refman/8.0/en/charset-syntax.html. 10.3.2 Server Character Set and Collation 10.3.3 Database Character Set and Collation 10.3.4 Table Character Set and Collation 10.3.5 Column Character Set and Collation
G3.在字符集处理过程中传输/解释
这个组里有三个选项： character_set_client 、 character_set_connection 和character_set_results 。
MySQL 使用前两个选项来写入表：服务器将 character_set_client 系统变量作为客户端发送语句的字符集。服务器将客户端发送的语句从 character_set_client 转化为character_set_connection。
最后一个用于从表中读取：character_set_results系统变量表示服务端向客户端返回查询结果的字符集。如果要服务端不执行结果集或错误消息的转换，请将character_set_results 设置为 NULL 或者 binary。
通过示例来进行说明
让我们看几个例子，以帮助我们进一步了解：
我们重用上面的表 t1 ，它的 c2 列字符集为 utf8mb4 ，c3 列字符集为 latin1。
正如你所看到的，在示例中，我将使用两个汉字，“王” 这是我的中文姓，“李” 这是我妻子的中文姓氏。
示例1
当 character_set_client 与 character_set_connection 相同时，没有转换。无论字符集编码为 latin1 还是 utf8mb4 ，汉字“王”的二进制编码为0xE78E8B，汉字“李”的二进制编码为0xE69D8E。
mysql> <strong>set names latin1;</strong>
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| latin1 | latin1 |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王") |
+------------------------------+
| 0xE78E8B |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李") |
+------------------------------+
| 0xE69D8E |
+------------------------------+
mysql> <strong>set names utf8mb4;</strong>
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| utf8mb4 | utf8mb4 |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王") |
+------------------------------+
| 0xE78E8B |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李") |
+------------------------------+
| 0xE69D8E |
+------------------------------+示例2
当它们设置不同时，MySQL 将从 character_set_client 转化为 character_set_connection。
示例2.1 从较小的字符集转化为较大的字符集
mysql> set character_set_client=latin1; set character_set_connection=utf8mb4;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| latin1 | utf8mb4 |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王") |
+------------------------------+
| 0xC3A7C5BDE280B9 |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李") |
+------------------------------+
| 0xC3A6C29DC5BD |
+------------------------------+为什么 “王”的二进制编码和“李”的二进制编码与示例1的不同？原因是 MySQL 完成了从character_set_client(latin1) 到 character_set_connection(utf8mb4) 的转换，意味着 MySQL 将使用 character_set_client(latin1) 解释字符串，然后使用 character_set_connection(utf8mb4) 解释输出。
验证一下：
mysql> select binary(convert(convert('王' using latin1) using utf8mb4));
+-------------------------------------------------------------------------------+
| binary(convert(convert('王' using latin1) using utf8mb4)) |
+-------------------------------------------------------------------------------+
| 0xC3A7C5BDE280B9 |
+-------------------------------------------------------------------------------+
mysql> select binary(convert(convert('李' using latin1) using utf8mb4));
+-------------------------------------------------------------------------------+
| binary(convert(convert('李' using latin1) using utf8mb4)) |
+-------------------------------------------------------------------------------+
| 0xC3A6C29DC5BD |
+-------------------------------------------------------------------------------+示例2.2 从较大的字符集转换为较小的字符集
mysql> set character_set_client=utf8mb4; set character_set_connection=latin1;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| utf8mb4 | latin1 |
+------------------------+----------------------------+
mysql> select binary("王");
+--------------------------+
| binary("?") |
+--------------------------+
| 0x3F |
+--------------------------+
mysql> select binary("李");
+--------------------------+
| binary("?") |
+--------------------------+
| 0x3F |
+--------------------------+同样的，MySQL 进行从character_set_client(utf8mb4) 到 character_set_connection(latin1) 字符集转换，由于从较大的字符集转换为较小的字符集，因此在某些情况下，转换会导致数据丢失，例如在本例中。不同的中文汉字“王”和“李” 返回了相同的二进制 0x3F ，这显然是错误的（数据丢失）。
以下是 MySQL 如何在数据丢失的情况下进行转换：
mysql> select binary(convert(convert('王' using utf8mb4) using latin1));
+-------------------------------------------------------------------------------+
| binary(convert(convert('王' using utf8mb4) using latin1)) |
+-------------------------------------------------------------------------------+
| 0x3F |
+-------------------------------------------------------------------------------+
mysql> select binary(convert(convert('李' using utf8mb4) using latin1));
+-------------------------------------------------------------------------------+
| binary(convert(convert('李' using utf8mb4) using latin1)) |
+-------------------------------------------------------------------------------+
|0x3F |
+-------------------------------------------------------------------------------+示例3
当列的字符集与character_set_connection 不同时，也会发生转换。
示例3.1 从较小的字符集转换为较大的字符集
正如我们从下面的示例中看到的，列 c2 期望的字符集是 utf8mb4 ，它与 character_set_connection(latin1) 不同，因此插入的实际数据是转换的结果（将字符串解释为latin1，然后将输出解释为目标字符集utf8mb4）："convert(convert(‘王’ using latin1) using utf8mb4)"，这样 utf8mb4 字符集下“王”的二进制为0xC3A7C5BDE280B9；同时，列c3预期的字符集与 character_set_connection(latin1)相同，因此不涉及转换，插入的数据是原始数据（将字符串解释为latin1），因此latin1字符集下“李”的二进制为0xE69D8E。
mysql> set names latin1;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| latin1 | latin1 |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王") |
+------------------------------+
| 0xE78E8B |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李") |
+------------------------------+
| 0xE69D8E |
+------------------------------+
mysql> insert into t1(c1,c2,c3) values(1,"王","李");
mysql> select * from t1 where c1=1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 王 | 李 |
+----+------+------+
mysql> select c1,binary(c2),binary(c3) from t1 where c1=1;
+----+------------------------+------------------------+
| c1 | binary(c2) | binary(c3) |
+----+------------------------+------------------------+
| 1 | 0xC3A7C5BDE280B9 | 0xE69D8E |
+----+------------------------+------------------------+还请注意，在这里，c2列是 utf8mb4 ，它与 character_set_results(latin1) 不同。因此，当读取数据时，MySQL 也会将其转换为结果。c3列与 character_set_results(latin1) 相同，因此不需要转换，但输出将被解释为 latin1。
mysql> select @@character_set_results;
+-------------------------+
| @@character_set_results |
+-------------------------+
| latin1 |
+-------------------------+
mysql> select * from t1 where c1=1;
+----+------+------+
| c1 | c2 | c3 |
+----+------+------+
| 1 | 王 | 李 |
+----+------+------+对于c2列，MySQL 将结果解释为 utf8mb4（列定义的字符集），然后将其转换为latin1(character_set_results 设定的值) ，如下所示：
mysql> select convert(convert(0xC3A7C5BDE280B9 using utf8mb4) using latin1);
+---------------------------------------------------------------+
| convert(convert(0xC3A7C5BDE280B9 using utf8mb4) using latin1) |
+---------------------------------------------------------------+
| 王 |
+---------------------------------------------------------------+对于c3列，MySQL 直接将结果解释为 latin1：
mysql> select convert(0xE69D8E using latin1);
+--------------------------------+
| convert(0xE69D8E using latin1) |
+--------------------------------+
| 李 |
+--------------------------------+示例3.2 从较大的字符集转换为较小的字符集
character_set_connection(utf8mb4) ，c2列的字符集为 utf8mb4，c3列的字符集为 latin1 。c2列正常，但对c3列， MySQL 会抛出异常error 1366 (HY000): Incorrect string value，因为它确定转换 (convert(convert(‘李’ using utf8mb4) using latin1))，将导致数据丢失，停止插入错误的数据。
mysql> set names utf8mb4 ;
mysql> select @@character_set_client,@@character_set_connection;
+------------------------+----------------------------+
| @@character_set_client | @@character_set_connection |
+------------------------+----------------------------+
| utf8mb4 | utf8mb4 |
+------------------------+----------------------------+
mysql> select binary("王");
+------------------------------+
| binary("王") |
+------------------------------+
| 0xE78E8B |
+------------------------------+
mysql> select binary("李");
+------------------------------+
| binary("李") |
+------------------------------+
| 0xE69D8E |
+------------------------------+
mysql> insert into t1(c1,c2,c3) values(2,"王","李");
<strong><em>ERROR 1366 (HY000): Incorrect string value: 'xE6x9Dx8E' for column 'c3' at row 1</em></strong>
mysql> select binary(convert(convert('李' using utf8mb4) using latin1));
+------------------------------------------------------------------------------+
| binary(convert(convert('李' using utf8mb4) using latin1)) |
+------------------------------------------------------------------------------+
| 0x3F |
+------------------------------------------------------------------------------+示例4
当您想在 InnoDB 存储引起中直接将较大的字符集转换为较小的字符集时，并不涉及参数设置，也会抛出同样的异常：
mysql> select c1, binary(c2), binary(c3) from t1;
+----+------------------------+------------------------+
| c1 | binary(c2) | binary(c3) |
+----+------------------------+------------------------+
| 1 | 0xC3A7C5BDE280B9 | 0xE69D8E |
| 3 | 0xE78E8B | 0x4131 |
+----+------------------------+------------------------+
mysql> ALTER TABLE t1 MODIFY c2 varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;
<strong><em>ERROR 1366 (HY000): Incorrect string value: 'xE7x8Ex8B' for column 'c2' at row 2</em></strong>示例5
还有一件事，正如您可能注意到的，在上面的示例中，我多次使用 set names 命令。这是使G3组中所有三个选项相同的方便方法，这样我们可以避免不必要的转换。
mysql> set names latin1;
mysql> show variables like '%character_set%';
+--------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------+
| <strong>character_set_client | latin1 </strong> |
| <strong>character_set_connection | latin1</strong> |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| <strong>character_set_results | latin1</strong> |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)
mysql> set names utf8mb4;
mysql> show variables like '%character_set%';
+--------------------------+-------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------+
| <strong>character_set_client | utf8mb4 </strong> |
| <strong>character_set_connection | utf8mb4</strong> |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| <strong>character_set_results | utf8mb4</strong> |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/percona-server/charsets/ |
+--------------------------+-------------------------------------+
8 rows in set (0.00 sec)总结
MySQL 将做潜在的转换，保证 character_set_client ，character_set_connection和列的字符集设置一致。
我们需要对转换小心谨慎，因为当MySQL从较大的字符集转换为较小的字符集时（如utf8mb4->latin1），可能会导致数据丢失或抛出异常 "ERROR 1366 (HY000) Incorrect string value"。
我们可以通过命令 set names 使所有三个G3配置（character_set_client、character_set_connection 和character_set_results）相同。
但是，当涉及到根据需要设置列的字符集时，在将列的字符集设置为不同于 character_set_connection 时，您可能需要重新思考。
原文始发于微信公众号（Mwkk）：[译文]MySQL 中如何配置字符集设置
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/100505.html
