几个字符集和排序规则系统变量与客户端与服务器的交互有关。其中一些在前面的章节中已经提到过：Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections:

在处理客户端与服务器之间的连接流量时，还涉及额外的字符集和排序规则系统变量。每个客户端都有会话特定的与连接相关的字符集和排序规则系统变量。这些会话系统变量的值在连接时初始化，但可以在会话期间更改。Additional character set and collation system variables are involved in handling traffic for the connection between a client and the server. Every client has session-specific connection-related character set and collation system variables. These session system variable values are initialized at connect time, but can be changed within the session.

关于客户端连接的字符集和排序规则处理的几个问题，可以通过系统变量来回答：Several questions about character set and collation handling for client connections can be answered in terms of system variables:

要查看当前会话适用的字符集和排序规则系统变量的值，请使用以下语句：To see the values of the character set and collation system variables that apply to the current session, use this statement:

SELECT * FROM performance_schema.session_variables
WHERE VARIABLE_NAME IN (
  'character_set_client', 'character_set_connection',
  'character_set_results', 'collation_connection'
) ORDER BY VARIABLE_NAME;

下面的更简单的语句也显示了连接变量，但同时包括其他相关变量。这些语句对于查看所有字符集和排序规则系统变量非常有用：The following simpler statements also display the connection variables, but include other related variables as well. They can be useful to see all character set and collation system variables:

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

客户端可以对这些变量的设置进行微调，或者依赖默认值（在这种情况下，可以跳过本节的其余部分）。如果不使用默认值，则必须为每个与服务器的连接更改字符设置。Clients can fine-tune the settings for these variables, or depend on the defaults (in which case, you can skip the rest of this section). If you do not use the defaults, you must change the character settings for each connection to the server.

character_set_client 系统变量不能设置为某些字符集：The character_set_client system variable cannot be set to certain character sets:

ucs2
utf16
utf16le
utf32

尝试将任何这些字符集用作客户端字符集会产生错误：Attempting to use any of those character sets as the client character set produces an error:

mysql> SET character_set_client = 'ucs2';
ERROR 1231 (42000): Variable 'character_set_client'
can't be set to the value of 'ucs2'

如果在以下任何情况下使用这些字符集，都将发生相同的错误，这些情况都会尝试将 character_set_client 设置为指定的字符集：The same error occurs if any of those character sets are used in the following contexts, all of which result in an attempt to set character_set_client to the named character set:

当客户端连接到服务器时，它会指明希望用于与服务器通信的字符集。（实际上，客户端指明该字符集的默认校对规则，服务器可以从中确定字符集。）服务器使用此信息将 character_set_client、character_set_results、character_set_connection 系统变量设置为该字符集，并将 collation_connection 设置为该字符集的默认校对规则。实际上，服务器执行等同于 SET NAMES 的操作。When a client connects to the server, it indicates which character set it wants to use for communication with the server. (Actually, the client indicates the default collation for that character set, from which the server can determine the character set.) The server uses this information to set the character_set_client, character_set_results, character_set_connection system variables to the character set, and collation_connection to the character set default collation. In effect, the server performs the equivalent of a SET NAMES operation.

如果服务器不支持请求的字符集或排序规则，则会回退使用服务器的字符集和排序规则来配置连接。有关此回退行为的详细信息，请参见 连接字符集错误处理。If the server does not support the requested character set or collation, it falls back to using the server character set and collation to configure the connection. For additional detail about this fallback behavior, see Connection Character Set Error Handling.

以下客户端程序决定默认使用的字符集方式如下：mysql、mysqladmin、mysqlcheck、mysqlimport 以及 mysqlshow。The mysql, mysqladmin, mysqlcheck, mysqlimport, and mysqlshow client programs determine the default character set to use as follows:

使用 mysql 客户端时，如果想使用不同于默认的字符集，可以在每次连接服务器时显式执行 SET NAMES 语句（参见 客户端程序连接字符集配置）。为了更轻松地实现相同的效果，可以在选项文件中指定字符集。例如，以下选项文件设置将在每次调用 mysql 时，将三个与连接相关的字符集系统变量更改为 koi8r：With the mysql client, to use a character set different from the default, you could explicitly execute a SET NAMES statement every time you connect to the server (see Client Program Connection Character Set Configuration). To accomplish the same result more easily, specify the character set in your option file. For example, the following option file setting changes the three connection-related character set system variables set to koi8r each time you invoke mysql:

[mysql]
default-character-set=koi8r

如果您使用启用了自动重新连接（不推荐）的 mysql 客户端，最好使用 charset 命令，而不是 SET NAMES。例如：If you are using the mysql client with auto-reconnect enabled (which is not recommended), it is preferable to use the charset command rather than SET NAMES. For example:

mysql> charset koi8r
Charset changed

charset 命令会执行一个 SET NAMES 语句，同时还会更改 mysql 在连接中断后重新连接时使用的默认字符集。The charset command issues a SET NAMES statement, and also changes the default character set that mysql uses when it reconnects after the connection has dropped.

在配置客户端程序时，您还必须考虑它们执行的环境。请参阅 第 12.5 节，“配置应用程序字符集和排序规则”。When configuration client programs, you must also consider the environment within which they execute. See Section 12.5, “Configuring Application Character Set and Collation”.

建立连接后，客户端可以更改当前会话的字符集和排序规则系统变量。可以使用 SET 语句单独更改这些变量，但有两个更方便的语句可以作为一个组影响连接相关的字符集系统变量：After a connection has been established, clients can change the character set and collation system variables for the current session. These variables can be changed individually using SET statements, but two more convenient statements affect the connection-related character set system variables as a group:

示例：假设 column1 被定义为 CHAR(5) CHARACTER SET latin2。如果你没有执行 SET NAMES 或 SET CHARACTER SET，那么对于 SELECT column1 FROM t，服务器会使用客户端连接时指定的字符集返回 column1 的所有值。另一方面，如果你在执行 SELECT 语句之前执行 SET NAMES 'latin1' 或 SET CHARACTER SET 'latin1'，服务器会在返回结果之前将 latin2 值转换为 latin1。对于不在两个字符集中的字符，转换可能会丢失数据。Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET 'latin1' before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy for characters that are not in both character sets.

尝试使用不适当的连接字符集或排序规则可能会产生错误，或者导致服务器回退到其默认的字符集和排序规则以用于特定连接。本节描述了在配置连接字符集时可能出现的问题。这些问题可能在建立连接时发生，也可能在已建立连接中更改字符集时发生。Attempts to use an inappropriate connection character set or collation can produce an error, or cause the server to fall back to its default character set and collation for a given connection. This section describes problems that can occur when configuring the connection character set. These problems can occur when establishing a connection or when changing the character set within an established connection.

连接时错误处理Connect-Time Error Handling

某些字符集不能用作客户端字符集；请参见 不允许的客户端字符集。如果您指定了一个有效但不允许作为客户端字符集的字符集，服务器将返回错误：Some character sets cannot be used as the client character set; see Impermissible Client Character Sets. If you specify a character set that is valid but not permitted as a client character set, the server returns an error:

$> mysql --default-character-set=ucs2
ERROR 1231 (42000): Variable 'character_set_client' can't be set to
the value of 'ucs2'

如果你指定了客户端无法识别的字符集，它会产生一个错误：If you specify a character set that the client does not recognize, it produces an error:

$> mysql --default-character-set=bogus
mysql: Character set 'bogus' is not a compiled character set and is
not specified in the '/usr/local/mysql/share/charsets/Index.xml' file
ERROR 2019 (HY000): Can't initialize character set bogus
(path: /usr/local/mysql/share/charsets/)

如果你指定了客户端识别但服务器不识别的字符集，服务器将回退到其默认的字符集和排序规则。假设服务器配置为使用 latin1 和 latin1_swedish_ci 作为默认值，并且不将 gb18030 识别为有效字符集。一个指定了 --default-character-set=gb18030 的客户端能够连接到服务器，但最终的字符集并不是客户端想要的：If you specify a character set that the client recognizes but the server does not, the server falls back to its default character set and collation. Suppose that the server is configured to use latin1 and latin1_swedish_ci as its defaults, and that it does not recognize gb18030 as a valid character set. A client that specifies --default-character-set=gb18030 is able to connect to the server, but the resulting character set is not what the client wants:

mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
...
| character_set_results    | latin1 |
...
+--------------------------+--------+
mysql> SHOW SESSION VARIABLES LIKE 'collation_connection';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
+----------------------+-------------------+

您可以看到连接系统变量已设置为反映字符集和排序规则为 latin1 和 latin1_swedish_ci。这是因为服务器无法满足客户端的字符集请求，因此回退到其默认值。You can see that the connection system variables have been set to reflect a character set and collation of latin1 and latin1_swedish_ci. This occurs because the server cannot satisfy the client character set request and falls back to its defaults.

在这种情况下，客户端无法使用其想要的字符集，因为服务器不支持该字符集。客户端必须要么愿意使用不同的字符集，要么连接到支持所需字符集的其他服务器。In this case, the client cannot use the character set that it wants because the server does not support it. The client must either be willing to use a different character set, or connect to a different server that supports the desired character set.

当客户端告诉服务器使用服务器识别的字符集，但该字符集在客户端的默认排序规则在服务器端未知时，也会出现同样的问题。The same problem occurs when the client tells the server to use a character set that the server recognizes, but the default collation for that character set on the client side is not known on the server side.

运行时错误处理Runtime Error Handling

在已建立的连接中，客户端可以通过SET NAMES或SET CHARACTER SET请求更改连接字符集和排序规则。Within an established connection, the client can request a change of connection character set and collation with SET NAMES or SET CHARACTER SET.

某些字符集不能用作客户端字符集；请参见 不允许的客户端字符集。如果您指定了一个有效但不允许作为客户端字符集的字符集，服务器将返回错误：Some character sets cannot be used as the client character set; see Impermissible Client Character Sets. If you specify a character set that is valid but not permitted as a client character set, the server returns an error:

mysql> SET NAMES 'ucs2';
ERROR 1231 (42000): Variable 'character_set_client' can't be set to
the value of 'ucs2'

如果服务器无法识别字符集（或整理规则），它会产生错误：If the server does not recognize the character set (or the collation), it produces an error:

mysql> SET NAMES 'bogus';
ERROR 1115 (42000): Unknown character set: 'bogus'

mysql> SET NAMES 'utf8mb4' COLLATE 'bogus';
ERROR 1273 (HY000): Unknown collation: 'bogus'

SELECT @@character_set_client;