MySQL 有许多返回字符串的运算符和函数。本节回答的问题是：此类字符串的字符集和校对规则是什么？MySQL has many operators and functions that return a string. This section answers the question: What is the character set and collation of such a string?

对于接受字符串输入并返回字符串结果输出的简单函数，输出的字符集和排序规则与主要输入值相同。例如，UPPER(X) 返回具有与 X 相同字符集和排序规则的字符串。INSTR()、LCASE()、LOWER()、LTRIM()、MID()、REPEAT()、REPLACE()、REVERSE()、RIGHT()、RPAD()、RTRIM()、SOUNDEX()、SUBSTRING()、TRIM()、UCASE() 和 UPPER() 同样适用。For simple functions that take string input and return a string result as output, the output's character set and collation are the same as those of the principal input value. For example, UPPER(X) returns a string with the same character string and collation as X. The same applies for INSTR(), LCASE(), LOWER(), LTRIM(), MID(), REPEAT(), REPLACE(), REVERSE(), RIGHT(), RPAD(), RTRIM(), SOUNDEX(), SUBSTRING(), TRIM(), UCASE(), and UPPER().

如果字符串输入或函数结果是二进制字符串，则该字符串具有 binary 字符集和排序规则。这可以通过使用 CHARSET() 和 COLLATION() 函数来检查，这两个函数对于二进制字符串参数都会返回 binary：If a string input or function result is a binary string, the string has the binary character set and collation. This can be checked by using the CHARSET() and COLLATION() functions, both of which return binary for a binary string argument:

mysql> SELECT CHARSET(BINARY 'a'), COLLATION(BINARY 'a');
+---------------------+-----------------------+
| CHARSET(BINARY 'a') | COLLATION(BINARY 'a') |
+---------------------+-----------------------+
| binary              | binary                |
+---------------------+-----------------------+

对于将多个字符串输入组合并返回单个字符串输出的操作，标准 SQL 的“聚合规则”适用于确定结果的排序规则：For operations that combine multiple string inputs and return a single string output, the “aggregation rules” of standard SQL apply for determining the collation of the result:

例如，对于 CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END，结果的排序规则是 X。同样适用于 UNION、||、CONCAT()、ELT()、GREATEST()、IF()，以及 LEAST()。For example, with CASE ... WHEN a THEN b WHEN b THEN c COLLATE X END, the resulting collation is X. The same applies for UNION, ||, CONCAT(), ELT(), GREATEST(), IF(), and LEAST().

对于将数据转换为字符的操作，操作结果字符串的字符集和排序规则由 character_set_connection 和 collation_connection 系统变量定义，这些变量决定默认的连接字符集和排序规则（参见 第 12.4 节，“连接字符集和排序规则”）。这仅适用于 BIN_TO_UUID()、CAST()、CONV()、FORMAT()、HEX() 和 SPACE()。For operations that convert to character data, the character set and collation of the strings that result from the operations are defined by the character_set_connection and collation_connection system variables that determine the default connection character set and collation (see Section 12.4, “Connection Character Sets and Collations”). This applies only to BIN_TO_UUID(), CAST(), CONV(), FORMAT(), HEX(), and SPACE().

前述原则的一个例外是虚拟生成列的表达式。在这些表达式中，无论连接字符集如何，BIN_TO_UUID()、CONV() 或 HEX() 的结果都会使用表的字符集。An exception to the preceding principle occurs for expressions for virtual generated columns. In such expressions, the table character set is used for BIN_TO_UUID(), CONV(), or HEX() results, regardless of connection character set.

如果对字符串函数返回的结果的字符集或排序规则有任何疑问，请使用 CHARSET() 或 COLLATION() 函数来查明：If there is any question about the character set or collation of the result returned by a string function, use the CHARSET() or COLLATION() function to find out:

mysql> SELECT USER(), CHARSET(USER()), COLLATION(USER());
+----------------+-----------------+--------------------+
| USER()         | CHARSET(USER()) | COLLATION(USER())  |
+----------------+-----------------+--------------------+
| test@localhost | utf8mb3         | utf8mb3_general_ci |
+----------------+-----------------+--------------------+
mysql> SELECT CHARSET(COMPRESS('abc')), COLLATION(COMPRESS('abc'));
+--------------------------+----------------------------+
| CHARSET(COMPRESS('abc')) | COLLATION(COMPRESS('abc')) |
+--------------------------+----------------------------+
| binary                   | binary                     |
+--------------------------+----------------------------+