mysql服务器连接排序_mysql8 参考手册--连接字符集和排序规则

一个“ connection ”是当它连接到服务器，开始在其内与服务器交互会话的客户端程序发出什么。客户端通过会话连接发送SQL语句，例如查询。服务器通过连接将响应(例如结果集或错误消息)发送回客户端。

连接字符集和整理系统变量

几个字符集和排序规则系统变量与客户端与服务器的交互有关。其中一些已在前面的部分中提到：

1、character_set_server 和collation_server系统变量指示服务器字符集和整理。

2、character_set_database 和collation_database 系统变量表示默认数据库的字符集和校对。

在处理客户端和服务器之间连接的流量时，还涉及其他字符集和排序规则系统变量。每个客户端都有特定于会话的与连接有关的字符集和整理系统变量。这些会话系统变量值在连接时初始化，但可以在会话中更改。

关于客户端连接的字符集和排序规则处理的几个问题可以通过系统变量来回答：

1、语句离开客户时所使用的字符集是什么？

服务器将 character_set_client系统变量作为字符集，客户端在其中发送语句。

注意

某些字符集不能用作客户端字符集。请参阅 不允许的客户字符集。

2、服务器在收到语句后应将其转换为什么字符集？

为了确定这一点，服务器使用 character_set_connection 和collation_connection 系统变量：

(1)、服务器将客户端发送的语句从转换 character_set_client为 character_set_connection。例外：对于具有介绍人的字符串文字，例如_utf8mb4或 _latin2，介绍人确定字符集。

(2)、collation_connection对于文字字符串的比较很重要。对于将字符串与列值进行比较， collation_connection 没关系，因为列具有自己的排序规则，排序规则具有更高的排序规则优先级(请参见 第10.8.4节“表达式中的排序规则可强制性”)。

3、在将查询结果发送回客户端之前，服务器应将其转换为什么字符集？

所述character_set_results 系统变量指示的字符集，其中所述服务器查询结果返回到客户端。这包括结果数据(例如列值)，结果元数据(例如列名)和错误消息。

要告诉服务器不对结果集或错误消息进行任何转换，请将设置 character_set_results为 NULL或binary：

SET character_set_results = NULL;

SET character_set_results = binary;

要查看适用于当前会话的字符集和排序规则系统变量的值，请使用以下语句：

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;

以下更简单的语句还显示连接变量，但也包括其他相关变量。它们对于查看所有字符集和归类系统变量很有用：

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

SHOW SESSION VARIABLES LIKE 'collation\_%';

客户端可以微调这些变量的设置，或者取决于默认值(在这种情况下，您可以跳过本节的其余部分)。如果不使用默认值，则必须更改与服务器的每个连接的字符设置。

不允许的客户字符集

character_set_client系统变量不能设置为特定字符集为：

ucs2

utf16

utf16le

utf32

尝试将任何这些字符集用作客户端字符集都会产生错误：

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为命名字符集：

1、MySQL客户端程序(例如mysql和mysqladmin)使用 --default-character-set=charset_name的命令选项 。

2、SET NAMES 'charset_name' 语句

3、SET CHARACTER SET 'charset_name' 语句

客户端程序连接字符集配置

当客户端连接到服务器时，它指示它要用于与服务器通信的字符集。(实际上，在客户端指示该字符集，从该服务器可确定的字符集的默认排序。)服务器使用该信息来设置 character_set_client， character_set_results， character_set_connection系统变量的字符集，并 collation_connection以字符集默认排序规则。实际上，服务器执行等效的SET NAMES 操作。

如果服务器不支持所请求的字符集或排序规则，则退回到使用服务器字符集和排序规则来配置连接的方式。有关此后备行为的更多详细信息，请参见 连接字符集错误处理。

mysql、mysqladmin、mysqlcheck、mysqlimport和mysqlshow客户端程序确定要使用的默认字符集，如下所示：

1、在没有其他信息的情况下，每个客户端通常使用已编译的默认字符集 utf8mb4。

2、每个客户端都可以基于操作系统设置(例如Unix系统上的LANGor LC_ALLlocale环境变量的值或Windows系统上的代码页设置)自动检测要使用的字符集 。对于从OS可以在其上使用语言环境的系统，客户端使用它来设置默认字符集，而不是使用内置的默认字符集。例如，设置LANG为 ru_RU.KOI8-R会导致使用 koi8r字符集。因此，用户可以在其环境中配置语言环境以供MySQL客户端使用。

如果没有完全匹配，则OS字符集将映射到最接近的MySQL字符集。如果客户端不支持匹配的字符集，它将使用编译后的默认值。例如，utf8 and utf-8映射到 utf8mb4，并且ucs2不作为连接字符集支持，因此它映射到已编译的默认值。

mysql_options()在连接到服务器之前， C应用程序可以通过以下方式调用基于OS设置的字符集自动检测功能 ：

mysql_options(mysql,

MYSQL_SET_CHARSET_NAME,

MYSQL_AUTODETECT_CHARSET_NAME);

3、每个客户端都支持一个 --default-character-set 选项，该选项使用户可以显式指定字符集，以覆盖客户端否则确定的任何默认设置。

注意

某些字符集不能用作客户端字符集。尝试与它们一起使用 --default-character-set 会产生错误。请参阅 不允许的客户字符集。

使用mysql客户端时，要使用与默认字符集不同的字符集，可以在SET NAMES每次连接到服务器时显式执行一条 语句(请参阅“ 客户端程序连接字符集配置”)。要更轻松地实现相同的结果，请在选项文件中指定字符集。例如，以下选项文件设置会在koi8r每次调用mysql时将三个与连接相关的字符集系统变量设置更改为：

[mysql]

default-character-set=koi8r

如果使用启用了自动重新连接的mysql客户端(不建议使用)，则最好使用charset命令而不是SET NAMES。例如：

mysql>charset koi8r

Charset changed

该charset命令发出一条 SET NAMES语句，并更改 连接断开后mysql重新连接时使用的默认字符集。

配置客户端程序时，还必须考虑它们在其中执行的环境。

连接字符集配置的SQL语句

建立连接后，客户端可以更改当前会话的字符集和整理系统变量。可以使用SET 语句分别更改这些变量 ，但是还有两个更方便的语句将与连接有关的字符集系统变量作为一组来影响：

SET NAMES 'charset_name' [COLLATE 'collation_name']

SET NAMES指示客户端将使用什么字符集将SQL语句发送到服务器。因此， SET NAMES 'cp1251'告诉服务器，“ 此客户端将来收到的消息以字符集表示 cp1251。”还指定服务器用于将结果发送回客户端的字符集。(例如，如果您使用SELECT产生结果集的语句，则它指示用于列值的字符 集。)

一条 SET NAMES 'charset_name' 语句等效于以下三个语句：

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET character_set_connection = charset_name;

设置 character_set_connection为 charset_name还隐式设置 collation_connection为的默认排序规则 charset_name。不必显式设置该排序规则。要指定用于的特定排序规则 collation_connection，请添加一个 COLLATE子句：

SET NAMES 'charset_name' COLLATE 'collation_name'

SET CHARACTER SET 'charset_name'

SET CHARACTER SET与相似，SET NAMES但character_set_connection 与和设置 collation_connection为 character_set_database和 collation_database(如前所述，它指示默认数据库的字符集和排序规则)。

一条SET CHARACTER SET charset_name语句等效于以下三个语句：

SET character_set_client = charset_name;

SET character_set_results = charset_name;

SET collation_connection = @@collation_database;

设置 collation_connection还隐式设置 character_set_connection为与排序规则关联的字符集(等效于execute SET character_set_connection = @@character_set_database)。无需character_set_connection 明确设置 。

注意

某些字符集不能用作客户端字符集。尝试将它们与一起使用SET NAMES或SET CHARACTER SET产生错误。请参阅 不允许的客户字符集。

示例：假设column1定义为 CHAR(5) CHARACTER SET latin2。如果您不说SET NAMES或 SET CHARACTER SET，则对于 SELECT column1 FROM t，服务器会发回所有值，以column1使用客户端在连接时指定的字符集。另一方面，如果您说SET NAMES 'latin1' 或SET CHARACTER SET 'latin1'在发出该SELECT语句之前，则服务器会将这些latin2值转换为 latin1恰好在将结果发送回之前。对于不在两个字符集中的字符，转换可能会造成损失。

连接字符集错误处理

尝试使用不合适的连接字符集或排序规则会产生错误，或导致服务器退回到给定连接的默认字符集和排序规则。本节介绍配置连接字符集时可能出现的问题。建立连接或更改已建立连接中的字符集时，可能会出现这些问题。

连接时间错误处理

某些字符集不能用作客户端字符集；某些字符集不能用作客户端字符集。请参阅 不允许的客户字符集。如果您指定一个有效的字符集但不允许作为客户端字符集，则服务器将返回错误：

shell> mysql --default-character-set=ucs2

ERROR 1231 (42000): Variable 'character_set_client' can't be set to

the value of 'ucs2'

如果指定了客户端无法识别的字符集，则会产生错误：

shell> 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可以连接到服务器，但是生成的字符集不是客户端想要的：

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。发生这种情况是因为服务器无法满足客户端字符集请求，并退回到其默认值。

在这种情况下，客户端无法使用所需的字符集，因为服务器不支持它。客户端必须要么愿意使用其他字符集，要么连接到支持所需字符集的其他服务器。

在更微妙的上下文中会发生相同的问题：当客户端告诉服务器使用服务器可以识别的字符集，但是服务器端不知道客户端上该字符集的默认排序规则。例如，当MySQL 8.0客户端希望使用utf8mb4客户端字符集连接到MySQL 5.7服务器时，就会发生这种情况。指定的客户端 --default-character-set=utf8mb4能够连接到服务器。但是，如前面的示例所示，服务器回退到其默认字符集和排序规则，而不是客户端请求的内容：

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 |

+----------------------+-------------------+

为什么会发生这种情况？毕竟，utf8mb48.0客户端和5.7服务器是已知的，因此它们都可以识别它。要了解此行为，必须了解，当客户端告诉服务器要使用哪个字符集时，它实际上告诉服务器该字符集的默认排序规则。因此，由于多种因素，发生上述现象：

1、utf8mb4MySQL 5.7和8.0 的默认排序规则有所不同(utf8mb4_general_ci对于5.7， utf8mb4_0900_ai_ci对于8.0)。

2、当8.0客户端请求字符集时 utf8mb4，它发送给服务器的是默认的8.0 utf8mb4排序规则；即utf8mb4_0900_ai_ci。

3、utf8mb4_0900_ai_ci 仅从MySQL 8.0开始实现，因此5.7服务器无法识别它。

4、由于5.7服务器无法识别 utf8mb4_0900_ai_ci，因此它无法满足客户端字符集请求，并退回到其默认字符集和排序规则(latin1和 latin1_swedish_ci)。

在这种情况下，客户端仍然可以utf8mb4通过SET NAMES 'utf8mb4'在连接后发出一条语句来使用 。产生的归类是5.7默认utf8mb4 归类。即utf8mb4_general_ci。如果客户端另外希望使用的排序规则 utf8mb4_0900_ai_ci，则它将无法实现，因为服务器无法识别该排序规则。客户端必须要么愿意使用其他 utf8mb4排序规则，要么从MySQL 8.0或更高版本连接到服务器。

运行时错误处理

在已建立的连接内，客户端可以请求更改连接字符集并使用 SET NAMES或 SET CHARACTER SET.排序规则。

某些字符集不能用作客户端字符集；某些字符集不能用作客户端字符集。请参阅 不允许的客户字符集。如果您指定一个有效的字符集但不允许作为客户端字符集，则服务器将返回错误：

mysql> SET NAMES 'ucs2';

ERROR 1231 (42000): Variable 'character_set_client' can't be set to

the value of 'ucs2'

如果服务器无法识别字符集(或排序规则)，则会产生错误：

mysql>SET NAMES 'bogus';

ERROR 1115 (42000): Unknown character set: 'bogus'

mysql>SET NAMES 'utf8mb4' COLLATE 'bogus';

ERROR 1273 (HY000): Unknown collation: 'bogus'提示

想要验证服务器是否遵循其请求的字符集的客户端，可以在连接并检查结果是否为预期的字符集后执行以下语句：