MySQL :: MySQL 8.4 参考手册 :: 14.10 强制转换函数和运算符

8.4 中文镜像

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

打开官方英文原文 ↗

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.10 强制转换函数和运算符

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 88%

14.10 类型转换函数与运算符14.10 Cast Functions and Operators

表 14.15 类型转换函数和操作符Table 14.15 Cast Functions and Operators

名称Name	说明Description	已弃用Deprecated
`BINARYBINARY`	将字符串转换为二进制字符串Cast a string to a binary string	是Yes
`CAST()`	将值转换为某种类型Cast a value as a certain type
`CONVERT()`	将值转换为某种类型Cast a value as a certain type

类型转换函数和运算符可以将值从一种数据类型转换为另一种数据类型。Cast functions and operators enable conversion of values from one data type to another.

类型转换函数和操作符说明Cast Function and Operator Descriptions

BINARY expr
该BINARY operator converts the expression to a binary string (a string that has the binary字符集和binary排序规则)。常见的用法是BINARY强制字符字符串比较按字节而非按字符进行，使用数字字节值进行比较。该BINARY运算符还会导致比较中尾随空格被视为重要。有关binary字符集的排序规则与非二进制字符集的binary排序规则之间差异的信息，请参见_bin第 12.8.5 节，“二进制排序规则与_bin 排序规则的比较”一些条件适用于所有空间类型转换，某些条件仅适用于转换结果为特定空间类型时。有关诸如.The BINARY operator converts the expression to a binary string (a string that has the binary character set and binary collation). A common use for BINARY is to force a character string comparison to be done byte by byte using numeric byte values rather than character by character. The BINARY operator also causes trailing spaces in comparisons to be significant. For information about the differences between the binary collation of the binary character set and the _bin collations of nonbinary character sets, see Section 12.8.5, “The binary Collation Compared to _bin Collations”.
BINARY 操作符已被弃用；你应当预期其将在未来版本的 MySQL 中被移除。请改用 CAST(... AS BINARY)。The BINARY operator is deprecated; you should expect its removal in a future version of MySQL. Use CAST(... AS BINARY) instead.
```
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;
        -> OK
mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0
```
在比较中，BINARY 影响整个操作；它可以在任意一个操作数之前使用，结果相同。In a comparison, BINARY affects the entire operation; it can be given before either operand with the same result.
将字符串表达式转换为二进制字符串，这些构造是等价的：To convert a string expression to a binary string, these constructs are equivalent:
```
CONVERT(expr USING BINARY)
CAST(expr AS BINARY)
BINARY expr
```
如果某个值是字符串字面量，可以使用 _binary 字符集引入符将其指定为二进制字符串而无需转换：If a value is a string literal, it can be designated as a binary string without converting it by using the _binary character set introducer:
```
mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT _binary 'a' = 'A';
        -> 0
```
有关引入符的信息，请参见第 12.3.8 节，“字符集引入符”。For information about introducers, see Section 12.3.8, “Character Set Introducers”.
表达式中的 BINARY 运算符在效果上不同于字符列定义中的 BINARY 属性。对于定义了 BINARY 属性的字符列，MySQL 会分配表的默认字符集以及该字符集的二进制（_bin）排序规则。每个非二进制字符集都有一个 _bin 排序规则。例如，如果表的默认字符集是 utf8mb4，则这两个列定义是等效的：The BINARY operator in expressions differs in effect from the BINARY attribute in character column definitions. For a character column defined with the BINARY attribute, MySQL assigns the table default character set and the binary (_bin) collation of that character set. Every nonbinary character set has a _bin collation. For example, if the table default character set is utf8mb4, these two column definitions are equivalent:
```
CHAR(10) BINARY
CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
```
在 CHAR、VARCHAR 或 TEXT 列的定义中使用 CHARACTER SET binary 会导致该列被视为相应的二进制字符串数据类型。例如，以下成对的定义是等效的：The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as the corresponding binary string data type. For example, the following pairs of definitions are equivalent:
```
CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB
```
如果从 mysql 客户端中调用 BINARY，二进制字符串将根据 --binary-as-hex 的值以十六进制表示。有关该选项的更多信息，请参见第 6.5.1 节，“mysql — MySQL 命令行客户端”。If BINARY is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.
CAST(expr AS type [ARRAY])
CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])
timezone_specifier：[间隔] '+00:00' | 'UTC'timezone_specifier: [INTERVAL] '+00:00' | 'UTC'
使用 CAST(expr AS type 语法，CAST() 函数接受任何类型的表达式并生成指定类型的结果值。此操作也可以表示为 CONVERT(expr, type)，其作用相同。如果 expr 为 NULL，CAST() 返回 NULL。With CAST(expr AS type syntax, the CAST() function takes an expression of any type and produces a result value of the specified type. This operation may also be expressed as CONVERT(expr, type), which is equivalent. If expr is NULL, CAST() returns NULL.
允许使用以下 type 值：These type values are permitted:
- BINARY[(N)]
  生成一个具有VARBINARY数据类型的字符串，但当表达式expr为空（长度为零）时，结果类型为BINARY(0)。如果给出了可选长度N，BINARY(N)会使转换使用不超过N字节的参数值。短于N字节的值将用0x00字节填充至N长度。如果未给出可选长度N，MySQL 会根据表达式计算最大长度。如果提供或计算得出的长度超过内部阈值，结果类型为BLOB。如果长度仍然过长，结果类型为LONGBLOB。Produces a string with the VARBINARY data type, except that when the expression expr is empty (zero length), the result type is BINARY(0). If the optional length N is given, BINARY(N) causes the cast to use no more than N bytes of the argument. Values shorter than N bytes are padded with 0x00 bytes to a length of N. If the optional length N is not given, MySQL calculates the maximum length from the expression. If the supplied or calculated length is greater than an internal threshold, the result type is BLOB. If the length is still too long, the result type is LONGBLOB.
  有关将值转换为BINARY如何影响比较的描述，请参见第 13.3.3 节，“BINARY 和 VARBINARY 类型”。For a description of how casting to BINARY affects comparisons, see Section 13.3.3, “The BINARY and VARBINARY Types”.
- CHAR[(N)] [charset_info]
  生成一个具有 VARCHAR 数据类型的字符串，除非表达式 expr 为空（长度为零），在这种情况下，结果类型为 CHAR(0)。如果给出了可选长度 N，CHAR(N) 会导致类型转换最多使用参数的 N 个字符。对于短于 N 个字符的值，不会进行填充。如果未给出可选长度 N，MySQL 将根据表达式计算最大长度。如果提供的或计算出的长度大于内部阈值，结果类型为 TEXT。如果长度仍然过长，结果类型为 LONGTEXT。Produces a string with the VARCHAR data type, unless the expression expr is empty (zero length), in which case the result type is CHAR(0). If the optional length N is given, CHAR(N) causes the cast to use no more than N characters of the argument. No padding occurs for values shorter than N characters. If the optional length N is not given, MySQL calculates the maximum length from the expression. If the supplied or calculated length is greater than an internal threshold, the result type is TEXT. If the length is still too long, the result type is LONGTEXT.
  如果没有 charset_info 子句，CHAR 会生成使用默认字符集的字符串。要显式指定字符集，可以使用以下 charset_info 值：With no charset_info clause, CHAR produces a string with the default character set. To specify the character set explicitly, these charset_info values are permitted:
  - CHARACTER SET charset_name：生成使用指定字符集的字符串。CHARACTER SET charset_name: Produces a string with the given character set.
  - ASCII：是 CHARACTER SET latin1 的简写。ASCII: Shorthand for CHARACTER SET latin1.
  - UNICODE：是 CHARACTER SET ucs2 的简写。UNICODE: Shorthand for CHARACTER SET ucs2.
  在所有情况下，该字符串使用字符集默认的排序规则。In all cases, the string has the character set default collation.
- DATEDATE
  生成一个 DATE 值。Produces a DATE value.
- DATETIME[(M)]
  生成一个 DATETIME 值。如果提供了可选的 M 值，则指定秒的小数精度。Produces a DATETIME value. If the optional M value is given, it specifies the fractional seconds precision.
- DECIMAL[(M[,D])]
  生成一个 DECIMAL 值。如果提供可选的 M 和 D 值，它们指定最大数字数（精度）和小数点后的数字数（标度）。如果省略 D，则默认为 0。如果省略 M，则默认为 10。Produces a DECIMAL value. If the optional M and D values are given, they specify the maximum number of digits (the precision) and the number of digits following the decimal point (the scale). If D is omitted, 0 is assumed. If M is omitted, 10 is assumed.
- DOUBLE
  生成一个 DOUBLE 结果。Produces a DOUBLE result.
- FLOAT[(p)]
  如果未指定精度 p，则生成类型为 FLOAT 的结果。如果提供了 p 且 0 <= p <= 24，则结果类型为 FLOAT。如果 25 <= p <= 53，则结果类型为 DOUBLE。如果 p < 0 或 p > 53，则返回错误。If the precision p is not specified, produces a result of type FLOAT. If p is provided and 0 <= < p <= 24, the result is of type FLOAT. If 25 <= p <= 53, the result is of type DOUBLE. If p < 0 or p > 53, an error is returned.
- JSON
  生成一个 JSON 值。有关 JSON 与其他类型之间值转换规则的详细信息，请参阅 JSON 值的比较和排序。Produces a JSON value. For details on the rules for conversion of values between JSON and other types, see Comparison and Ordering of JSON Values.
- NCHAR[(N)]
  像 CHAR，但生成使用国家字符集的字符串。参见第 12.3.7 节，“国家字符集”。Like CHAR, but produces a string with the national character set. See Section 12.3.7, “The National Character Set”.
  与 CHAR 不同，NCHAR 不允许指定尾随字符集信息。Unlike CHAR, NCHAR does not permit trailing character set information to be specified.
- REAL
  生成类型为 REAL 的结果。如果启用了 REAL_AS_FLOAT SQL 模式，这实际上是 FLOAT; 否则结果类型为 DOUBLE。Produces a result of type REAL. This is actually FLOAT if the REAL_AS_FLOAT SQL mode is enabled; otherwise the result is of type DOUBLE.
- SIGNED [INTEGER]
  生成一个有符号的 BIGINT 值。Produces a signed BIGINT value.
- spatial_type
  CAST() 和 CONVERT() 支持将几何值从一种空间类型转换为另一种空间类型，适用于某些空间类型组合。详情请参见空间类型的转换操作。CAST() and CONVERT() support casting geometry values from one spatial type to another, for certain combinations of spatial types. For details, see Cast Operations on Spatial Types.
- TIME[(M)]
  生成一个TIME值。如果提供了可选的M值，则它指定小数秒的精度。Produces a TIME value. If the optional M value is given, it specifies the fractional seconds precision.
- UNSIGNED [INTEGER]
  生成一个无符号BIGINT值。Produces an unsigned BIGINT value.
- YEARYEAR
  生成一个YEAR值。以下规则用于YEAR的转换：Produces a YEAR value. These rules govern conversion to YEAR as follows:
  - 对于范围在 1901 到 2155（含）之间的四位数字，或可以解释为该范围内四位数字的字符串，返回对应的YEAR值。For a four-digit number in the range 1901-2155 inclusive, or for a string which can be interpreted as a four-digit number in this range, return the corresponding YEAR value.
  - 对于由一位或两位数字组成的数字，或者可以被解释为此类数字的字符串，返回一个 YEAR 值，如下所示：For a number consisting of one or two digits, or for a string which can be interpreted as such a number, return a YEAR value as follows:
    如果数字在 1 到 69（含）范围内，添加 2000 并返回总和。If the number is in the range 1-69 inclusive, add 2000 and return the sum.
    如果数字在 70 到 99（含）范围内，添加 1900 并返回总和。If the number is in the range 70-99 inclusive, add 1900 and return the sum.
  - 对于计算结果为 0 的字符串，返回 2000。For a string which evaluates to 0, return 2000.
  - 对于数字 0，返回 0。For the number 0, return 0.
  - 对于 DATE、DATETIME 或 TIMESTAMP 值，返回值的 YEAR 部分。对于 TIME 值，返回当前年份。For a DATE, DATETIME, or TIMESTAMP value, return the YEAR portion of the value. For a TIME value, return the current year.
    如果您不指定 TIME 参数的类型，可能会得到与预期不同的结果，如下所示：If you do not specify the type of a TIME argument, you may get a different result from what you expect, as shown here:
    mysql> SELECT CAST("11:35:00" AS YEAR), CAST(TIME "11:35:00" AS YEAR); +--------------------------+-------------------------------+ | CAST("11:35:00" AS YEAR) | CAST(TIME "11:35:00" AS YEAR) | +--------------------------+-------------------------------+ | 2011 | 2021 | +--------------------------+-------------------------------+
  - 如果参数的类型是 DECIMAL、DOUBLE、DECIMAL 或 REAL，则将值四舍五入到最接近的整数，然后根据整数值的规则尝试将该值转换为 YEAR，如下所示：If the argument is of type DECIMAL, DOUBLE, DECIMAL, or REAL, round the value to the nearest integer, then attempt to cast the value to YEAR using the rules for integer values, as shown here:
    mysql> SELECT CAST(1944.35 AS YEAR), CAST(1944.50 AS YEAR); +-----------------------+-----------------------+ | CAST(1944.35 AS YEAR) | CAST(1944.50 AS YEAR) | +-----------------------+-----------------------+ | 1944 | 1945 | +-----------------------+-----------------------+ mysql> SELECT CAST(66.35 AS YEAR), CAST(66.50 AS YEAR); +---------------------+---------------------+ | CAST(66.35 AS YEAR) | CAST(66.50 AS YEAR) | +---------------------+---------------------+ | 2066 | 2067 | +---------------------+---------------------+
  - 类型为 GEOMETRY 的参数不能转换为 YEAR。An argument of type GEOMETRY cannot be converted to YEAR.
  - 对于无法成功转换为 YEAR 的值，返回 NULL。For a value that cannot be successfully converted to YEAR, return NULL.
  包含非数字字符的字符串值，在转换之前必须被截断，会引发警告，如下所示：A string value containing non-numeric characters which must be truncated prior to conversion raises a warning, as shown here:
```
mysql> SELECT CAST("1979aaa" AS YEAR);
+-------------------------+
| CAST("1979aaa" AS YEAR) |
+-------------------------+
|                    1979 |
+-------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect YEAR value: '1979aaa' |
+---------+------+-------------------------------------------+
```
InnoDB 允许在 JSON 数组上使用额外的 ARRAY 关键字来创建多值索引，作为 CREATE INDEX、CREATE TABLE 和 ALTER TABLE 语句的一部分。除了在这些语句中用于创建多值索引时，ARRAY 不被支持，在这种情况下是必须的。被索引的列必须是 JSON 类型的列。使用 ARRAY 时，AS 关键字后面的 type 可以指定 CAST() 支持的任何类型，但 BINARY、JSON 和 YEAR 除外。有关语法信息和示例，以及其他相关信息，请参阅多值索引。InnoDB allows the use of an additional ARRAY keyword for creating a multi-valued index on a JSON array as part of CREATE INDEX, CREATE TABLE, and ALTER TABLE statements. ARRAY is not supported except when used to create a multi-valued index in one of these statements, in which case it is required. The column being indexed must be a column of type JSON. With ARRAY, the type following the AS keyword may specify any of the types supported by CAST(), with the exceptions of BINARY, JSON, and YEAR. For syntax information and examples, as well as other relevant information, see Multi-Valued Indexes.
Note
CONVERT() 与 CAST() 不同，不支持多值索引创建或 ARRAY 关键字。CONVERT(), unlike CAST(), does not support multi-valued index creation or the ARRAY keyword.
CAST() 支持使用 AT TIMEZONE 操作符将 TIMESTAMP 值作为 UTC 时间进行检索。唯一支持的时区是 UTC；可以指定为 '+00:00' 或 'UTC'。此语法唯一支持的返回类型是 DATETIME，可选的精度范围为 0 到 6（含）。CAST() supports retrieval of a TIMESTAMP value as being in UTC, using the AT TIMEZONE operator. The only supported time zone is UTC; this can be specified as either of '+00:00' or 'UTC'. The only return type supported by this syntax is DATETIME, with an optional precision specifier in the range of 0 to 6, inclusive.
TIMESTAMP 值也支持使用时区偏移。TIMESTAMP values that use timezone offsets are also supported.
```
mysql> SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EDT                |
+--------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE tz (c TIMESTAMP);
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO tz VALUES
    ->     ROW(CURRENT_TIMESTAMP),
    ->     ROW('2020-07-28 14:50:15+1:00');
Query OK, 1 row affected (0.08 sec)

mysql> TABLE tz;
+---------------------+
| c                   |
+---------------------+
| 2020-07-28 09:22:41 |
| 2020-07-28 09:50:15 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT CAST(c AT TIME ZONE '+00:00' AS DATETIME) AS u FROM tz;
+---------------------+
| u                   |
+---------------------+
| 2020-07-28 13:22:41 |
| 2020-07-28 13:50:15 |
+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT CAST(c AT TIME ZONE 'UTC' AS DATETIME(2)) AS u FROM tz;
+------------------------+
| u                      |
+------------------------+
| 2020-07-28 13:22:41.00 |
| 2020-07-28 13:50:15.00 |
+------------------------+
2 rows in set (0.00 sec)
```
如果你在这种形式的 CAST() 中使用 'UTC' 作为时区说明符，而服务器引发类似未知或不正确的时区：'UTC' 的错误，你可能需要安装 MySQL 时区表（见填充时区表）。If you use 'UTC' as the time zone specifier with this form of CAST(), and the server raises an error such as Unknown or incorrect time zone: 'UTC', you may need to install the MySQL time zone tables (see Populating the Time Zone Tables).
AT TIME ZONE 不支持 ARRAY 关键字，并且不被 CONVERT() 函数支持。AT TIME ZONE does not support the ARRAY keyword, and is not supported by the CONVERT() function.
CONVERT(expr USING transcoding_name)
CONVERT(expr,type)
CONVERT(expr USING transcoding_name) 是标准 SQL 语法。非 USING 形式的 CONVERT() 是 ODBC 语法。不管使用哪种语法，如果 expr 为 NULL，该函数都会返回 NULL。CONVERT(expr USING transcoding_name) is standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax. Regardless of the syntax used, the function returns NULL if expr is NULL.
CONVERT(expr USING transcoding_name) 用于在不同字符集之间转换数据。在 MySQL 中，转码名称与相应的字符集名称相同。例如，下面的语句将默认字符集中的字符串 'abc' 转换为 utf8mb4 字符集中的对应字符串：CONVERT(expr USING transcoding_name) converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8mb4 character set:
```
SELECT CONVERT('abc' USING utf8mb4);
```
CONVERT(expr, type) 语法（不带 USING）接受一个表达式和一个指定结果类型的 type 值，并生成该指定类型的结果值。此操作也可以表示为 CAST(expr AS type)，两者等效。有关更多信息，请参阅 CAST() 的描述。CONVERT(expr, type) syntax (without USING) takes an expression and a type value specifying a result type, and produces a result value of the specified type. This operation may also be expressed as CAST(expr AS type), which is equivalent. For more information, see the description of CAST().

字符集转换Character Set Conversions

CONVERT() 使用 USING 子句在字符集之间转换数据：CONVERT() with a USING clause converts data between character sets:

CONVERT(expr USING transcoding_name)

在 MySQL 中，转码名称与相应的字符集名称相同。In MySQL, transcoding names are the same as the corresponding character set names.

示例：Examples:

SELECT CONVERT('test' USING utf8mb4);
SELECT CONVERT(_latin1'Müller' USING utf8mb4);
INSERT INTO utf8mb4_table (utf8mb4_column)
    SELECT CONVERT(latin1_column USING utf8mb4) FROM latin1_table;

要在字符集之间转换字符串，你也可以使用 CONVERT(expr, type) 语法（不带 USING），或者 CAST(expr AS type)，它们是等价的：To convert strings between character sets, you can also use CONVERT(expr, type) syntax (without USING), or CAST(expr AS type), which is equivalent:

CONVERT(string, CHAR[(N)] CHARACTER SET charset_name)
CAST(string AS CHAR[(N)] CHARACTER SET charset_name)

示例：Examples:

SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);

如果你像上面那样指定 CHARACTER SET charset_name，结果的字符集和排序规则就是 charset_name 以及 charset_name 的默认排序规则。如果你省略 CHARACTER SET charset_name，结果的字符集和排序规则由 character_set_connection 和 collation_connection 系统变量定义，这些系统变量决定默认的连接字符集和排序规则（参见第 12.4 节，“连接字符集和排序规则”）。If you specify CHARACTER SET charset_name as just shown, the character set and collation of the result are charset_name and the default collation of charset_name. If you omit CHARACTER SET charset_name, the character set and collation of the result 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”).

在 CONVERT() 或 CAST() 调用中不允许使用 COLLATE 子句，但你可以将其应用到函数结果。例如，以下是合法的：A COLLATE clause is not permitted within a CONVERT() or CAST() call, but you can apply it to the function result. For example, these are legal:

SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;

但是这些是非法的：But these are illegal:

SELECT CONVERT('test' USING utf8mb4 COLLATE utf8mb4_bin);
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

对于字符串字面量，指定字符集的另一种方式是使用字符集引入符。前面的示例中的 _latin1 和 _latin2 就是引入符的实例。与将字符串从一种字符集转换为另一种字符集的转换函数（例如 CAST() 或 CONVERT()）不同，引入符是将字符串字面量指定为某个特定字符集，而不进行转换。更多信息，请参见第 12.3.8 节，“字符集引入符”。For string literals, another way to specify the character set is to use a character set introducer. _latin1 and _latin2 in the preceding example are instances of introducers. Unlike conversion functions such as CAST(), or CONVERT(), which convert a string from one character set to another, an introducer designates a string literal as having a particular character set, with no conversion involved. For more information, see Section 12.3.8, “Character Set Introducers”.

字符串比较的字符集转换Character Set Conversions for String Comparisons

通常情况下，您不能以不区分大小写的方式比较BLOB值或其他二进制字符串，因为二进制字符串使用binary字符集，该字符集没有与字母大小写相关的排序规则。要执行不区分大小写的比较，首先使用CONVERT()或CAST()函数将值转换为非二进制字符串。所得字符串的比较使用其排序规则。例如，如果转换结果的排序规则不区分大小写，则LIKE操作也不区分大小写。由于默认utf8mb4排序规则(utf8mb4_0900_ai_ci)不区分大小写，因此以下操作也是如此：Normally, you cannot compare a BLOB value or other binary string in case-insensitive fashion because binary strings use the binary character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, first use the CONVERT() or CAST() function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the conversion result collation is not case-sensitive, a LIKE operation is not case-sensitive. That is true for the following operation because the default utf8mb4 collation (utf8mb4_0900_ai_ci) is not case-sensitive:

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4)
  FROM tbl_name;

要为转换后的字符串指定特定的排序规则，请在 CONVERT() 调用之后使用 COLLATE 子句：To specify a particular collation for the converted string, use a COLLATE clause following the CONVERT() call:

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4) COLLATE utf8mb4_unicode_ci
  FROM tbl_name;

要使用不同的字符集，请在前面的语句中将其名称替换为 utf8mb4（同样地，要使用不同的排序规则也类似）。To use a different character set, substitute its name for utf8mb4 in the preceding statements (and similarly to use a different collation).

CONVERT() 和 CAST() 可以更一般地用于比较以不同字符集表示的字符串。例如，这些字符串的比较会导致错误，因为它们具有不同的字符集：CONVERT() and CAST() can be used more generally for comparing strings represented in different character sets. For example, a comparison of these strings results in an error because they have different character sets:

mysql> SET @s1 = _latin1 'abc', @s2 = _latin2 'abc';
mysql> SELECT @s1 = @s2;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (latin2_general_ci,IMPLICIT) for operation '='

将其中一个字符串转换为与另一个兼容的字符集可以使比较顺利进行而不会出错：Converting one of the strings to a character set compatible with the other enables the comparison to occur without error:

mysql> SELECT @s1 = CONVERT(@s2 USING latin1);
+---------------------------------+
| @s1 = CONVERT(@s2 USING latin1) |
+---------------------------------+
|                               1 |
+---------------------------------+

字符集转换在对二进制字符串进行字母大小写转换之前也很有用。LOWER() 和 UPPER() 直接应用于二进制字符串时无效，因为字母大小写的概念不适用。要对二进制字符串进行字母大小写转换，首先需要使用适合字符串中存储数据的字符集将其转换为非二进制字符串：Character set conversion is also useful preceding lettercase conversion of binary strings. LOWER() and UPPER() are ineffective when applied directly to binary strings because the concept of lettercase does not apply. To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string:

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
+-------------+------------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
+-------------+------------------------------------+
| New York    | new york                           |
+-------------+------------------------------------+

请注意，如果您对索引列使用 BINARY、CAST() 或 CONVERT()，MySQL 可能无法高效地使用索引。Be aware that if you apply BINARY, CAST(), or CONVERT() to an indexed column, MySQL may not be able to use the index efficiently.

空间类型的转换操作Cast Operations on Spatial Types

CAST() 和 CONVERT() 支持将几何值从一种空间类型转换为另一种空间类型，针对某些空间类型组合。下列表显示了允许的类型组合，其中 “MySQL 扩展” 表示在 MySQL 中实现的、超出 SQL/MM 标准定义的类型转换：CAST() and CONVERT() support casting geometry values from one spatial type to another, for certain combinations of spatial types. The following list shows the permitted type combinations, where “MySQL extension” designates casts implemented in MySQL beyond those defined in the SQL/MM standard:

从 Point 到：From Point to:
- MultiPoint
- GeometryCollection
从 LineString 到：From LineString to:
- Polygon（MySQL 扩展）Polygon (MySQL extension)
- MultiPoint（MySQL 扩展）MultiPoint (MySQL extension)
- MultiLineStringMultiLineString
- GeometryCollection
从 Polygon 转换为:From Polygon to:
- LineString（MySQL 扩展）LineString (MySQL extension)
- MultiLineString（MySQL 扩展）MultiLineString (MySQL extension)
- MultiPolygonMultiPolygon
- GeometryCollection
从 MultiPoint 转换为:From MultiPoint to:
- PointPoint
- LineString（MySQL 扩展）LineString (MySQL extension)
- GeometryCollection
从 MultiLineString 转换为:From MultiLineString to:
- LineStringLineString
- Polygon（MySQL 扩展）Polygon (MySQL extension)
- MultiPolygon（MySQL 扩展）MultiPolygon (MySQL extension)
- GeometryCollection
从 MultiPolygon 转换为:From MultiPolygon to:
- PolygonPolygon
- MultiLineString（MySQL 扩展）MultiLineString (MySQL extension)
- GeometryCollection
从 GeometryCollection 转换为:From GeometryCollection to:
- PointPoint
- LineStringLineString
- PolygonPolygon
- MultiPoint
- MultiLineStringMultiLineString
- MultiPolygonMultiPolygon

在空间类型转换中，GeometryCollection 和 GeomCollection 是同一种结果类型的同义词。In spatial casts, GeometryCollection and GeomCollection are synonyms for the same result type.

正确形成的几何体“等术语的信息，请参见”第 13.4.4 节，“几何体的正确性和有效性”匹配.Some conditions apply to all spatial type casts, and some conditions apply only when the cast result is to have a particular spatial type. For information about terms such as “well-formed geometry,” see Section 13.4.4, “Geometry Well-Formedness and Validity”.

空间转换的一般条件General Conditions for Spatial Casts

这些条件适用于所有空间类型转换，无论结果类型如何：These conditions apply to all spatial casts regardless of the result type:

类型转换的结果与要转换的表达式具有相同的 SRS。The result of a cast is in the same SRS as that of the expression to cast.
空间类型之间的转换不会改变坐标值或顺序。Casting between spatial types does not change coordinate values or order.
如果要转换的表达式是 NULL，则函数结果为 NULL。If the expression to cast is NULL, the function result is NULL.
使用带有 RETURNING 子句并指定空间类型的 JSON_VALUE() 函数进行空间类型转换是不允许的。Casting to spatial types using the JSON_VALUE() function with a RETURNING clause specifying a spatial type is not permitted.
不允许将表达式转换为空间类型的 ARRAY。Casting to an ARRAY of spatial types is not permitted.
如果空间类型组合允许，但要转换的表达式不是语法上格式良好的几何图形，将发生 ER_GIS_INVALID_DATA 错误。If the spatial type combination is permitted but the expression to cast is not a syntactically well-formed geometry, an ER_GIS_INVALID_DATA error occurs.
如果空间类型组合被允许，但要转换的表达式是在未定义空间参考系统（SRS）中语法上格式正确的几何体，则会发生 ER_SRS_NOT_FOUND 错误。If the spatial type combination is permitted but the expression to cast is a syntactically well-formed geometry in an undefined spatial reference system (SRS), an ER_SRS_NOT_FOUND error occurs.
如果要转换的表达式具有地理空间参考系统（SRS），但其经度或纬度超出范围，将发生错误：If the expression to cast has a geographic SRS but has a longitude or latitude that is out of range, an error occurs:
- 如果经度值不在范围（−180, 180]内，将发生ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE错误。If a longitude value is not in the range (−180, 180], an ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE error occurs.
- 如果纬度值不在范围[−90, 90]内，将发生ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE错误。If a latitude value is not in the range [−90, 90], an ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE error occurs.
显示的范围以度为单位。如果 SRS 使用其他单位，则范围使用其单位对应的数值。由于浮点运算，精确的范围限制会有轻微偏差。Ranges shown are in degrees. If an SRS uses another unit, the range uses the corresponding values in its unit. The exact range limits deviate slightly due to floating-point arithmetic.

转换为点的条件Conditions for Casts to Point

当转换结果类型为 Point 时，适用以下条件：When the cast result type is Point, these conditions apply:

如果要转换的表达式是类型为 Point 的格式正确的几何图形，函数结果就是该 Point。If the expression to cast is a well-formed geometry of type Point, the function result is that Point.
如果要转换的表达式是类型为 MultiPoint 的格式正确的几何图形，并且只包含一个 Point，函数结果就是该 Point。如果表达式包含多个 Point，则会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type MultiPoint containing a single Point, the function result is that Point. If the expression contains more than one Point, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为 GeometryCollection 的格式正确的几何体，并且仅包含一个 Point，则函数结果就是该 Point。如果表达式为空、包含多个 Point，或包含其他几何类型，则会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type GeometryCollection containing only a single Point, the function result is that Point. If the expression is empty, contains more than one Point, or contains other geometry types, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为除 Point、MultiPoint、GeometryCollection 之外的格式良好的几何类型，则会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type other than Point, MultiPoint, GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY error occurs.

转换为 LineString 的条件Conditions for Casts to LineString

当转换结果类型为 LineString 时，适用以下条件：When the cast result type is LineString, these conditions apply:

如果要转换的表达式是类型为 LineString 的格式良好的几何类型，则函数结果为该 LineString。If the expression to cast is a well-formed geometry of type LineString, the function result is that LineString.
如果要强制转换的表达式是类型为Polygon的格式正确的几何图形且没有内环，则函数结果为包含外环点的LineString，顺序保持不变。如果表达式有内环，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。If the expression to cast is a well-formed geometry of type Polygon that has no inner rings, the function result is a LineString containing the points of the outer ring in the same order. If the expression has inner rings, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是一个格式良好的 MultiPoint 类型几何图形，且至少包含两个点，则函数返回一个 LineString，其中包含 MultiPoint 中按表达式出现顺序排列的点。如果表达式仅包含一个 Point，则会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type MultiPoint containing at least two points, the function result is a LineString containing the points of the MultiPoint in the order they appear in the expression. If the expression contains only one Point, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为 MultiLineString 的格式良好的几何体，并且只包含一个 LineString，则函数结果为该 LineString。如果表达式包含多个 LineString，将会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type MultiLineString containing a single LineString, the function result is that LineString. If the expression contains more than one LineString, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是一个类型为 GeometryCollection 的格式正确的几何体，并且只包含一个 LineString，函数的结果就是该 LineString。如果表达式为空、包含多个 LineString，或者包含其他几何类型，则会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type GeometryCollection, containing only a single LineString, the function result is that LineString. If the expression is empty, contains more than one LineString, or contains other geometry types, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是一个类型不为 LineString、Polygon、MultiPoint、MultiLineString 或 GeometryCollection 的格式正确的几何对象，将会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type other than LineString, Polygon, MultiPoint, MultiLineString, or GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY error occurs.

转换为 Polygon 的条件Conditions for Casts to Polygon

当转换结果类型为 Polygon 时，适用以下条件：When the cast result type is Polygon, these conditions apply:

如果要转换的表达式是一个类型为 LineString 的格式正确的几何体并且是一个环（即起点和终点相同），则该函数的结果是一个 Polygon，其外环由 LineString 的点按相同顺序组成。如果表达式不是一个环，则会出现 ER_INVALID_CAST_TO_GEOMETRY 错误。如果环的方向不正确（外环必须为逆时针方向），则会出现 ER_INVALID_CAST_POLYGON_RING_DIRECTION 错误。If the expression to cast is a well-formed geometry of type LineString that is a ring (that is, the start and end points are the same), the function result is a Polygon with an outer ring consisting of the points of the LineString in the same order. If the expression is not a ring, an ER_INVALID_CAST_TO_GEOMETRY error occurs. If the ring is not in the correct order (the exterior ring must be counter-clockwise), an ER_INVALID_CAST_POLYGON_RING_DIRECTION error occurs.
如果要转换的表达式是类型为Polygon的格式良好的几何体，则函数的结果就是该Polygon。If the expression to cast is a well-formed geometry of type Polygon, the function result is that Polygon.
如果要转换的表达式是类型为MultiLineString且所有元素都是环的格式正确的几何图形，则该函数的结果是一个Polygon，其中第一个LineString作为外环，任何附加的LineString值作为内环。如果表达式的任何元素不是环，将发生ER_INVALID_CAST_TO_GEOMETRY错误。如果任何环的顺序不正确（外环必须为逆时针方向，内环必须为顺时针方向），将发生ER_INVALID_CAST_POLYGON_RING_DIRECTION错误。If the expression to cast is a well-formed geometry of type MultiLineString where all elements are rings, the function result is a Polygon with the first LineString as outer ring and any additional LineString values as inner rings. If any element of the expression is not a ring, an ER_INVALID_CAST_TO_GEOMETRY error occurs. If any ring is not in the correct order (the exterior ring must be counter-clockwise, interior rings must be clockwise), an ER_INVALID_CAST_POLYGON_RING_DIRECTION error occurs.
如果要转换的表达式是类型为MultiPolygon的良构几何体，且只包含一个Polygon，则函数结果为该Polygon。如果表达式包含多个Polygon，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。If the expression to cast is a well-formed geometry of type MultiPolygon containing a single Polygon, the function result is that Polygon. If the expression contains more than one Polygon, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为 GeometryCollection 的格式正确的几何体，并且仅包含一个 Polygon，则函数结果就是该 Polygon。如果表达式为空、包含多个 Polygon，或包含其他几何类型，则会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type GeometryCollection containing only a single Polygon, the function result is that Polygon. If the expression is empty, contains more than one Polygon, or contains other geometry types, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为 LineString、Polygon、MultiLineString、MultiPolygon 或 GeometryCollection 之外的良构几何，将发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type other than LineString, Polygon, MultiLineString, MultiPolygon, or GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY error occurs.

转换为 MultiPoint 的条件Conditions for Casts to MultiPoint

当转换结果类型为 MultiPoint 时，适用以下条件：When the cast result type is MultiPoint, these conditions apply:

如果要转换的表达式是类型为 Point 的良构几何，则函数结果是一个 MultiPoint，其中仅包含该 Point 作为其唯一元素。If the expression to cast is a well-formed geometry of type Point, the function result is a MultiPoint containing that Point as its sole element.
如果要转换的表达式是类型为 LineString 的格式良好的几何体，则函数结果是一个 MultiPoint，其中包含 LineString 中的点，顺序保持不变。If the expression to cast is a well-formed geometry of type LineString, the function result is a MultiPoint containing the points of the LineString in the same order.
如果要转换的表达式是类型为 MultiPoint 的格式良好的几何体，则函数结果就是该 MultiPoint。If the expression to cast is a well-formed geometry of type MultiPoint, the function result is that MultiPoint.
如果要转换的表达式是类型为GeometryCollection的良构几何体，且仅包含点，则函数结果是一个包含这些点的MultiPoint。如果GeometryCollection为空或包含其他几何类型，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。If the expression to cast is a well-formed geometry of type GeometryCollection containing only points, the function result is a MultiPoint containing those points. If the GeometryCollection is empty or contains other geometry types, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是除 Point、LineString、MultiPoint 或 GeometryCollection 之外的格式良好的几何类型，将会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type other than Point, LineString, MultiPoint, or GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY error occurs.

转换为 MultiLineString 的条件Conditions for Casts to MultiLineString

当转换结果类型为 MultiLineString 时，适用以下条件：When the cast result type is MultiLineString, these conditions apply:

如果要转换的表达式是格式良好的 LineString 类型几何，函数结果将是包含该 LineString 作为唯一元素的 MultiLineString。If the expression to cast is a well-formed geometry of type LineString, the function result is a MultiLineString containing that LineString as its sole element.
如果要转换的表达式是类型为Polygon的格式正确的几何体，则函数结果是一个MultiLineString，其第一个元素包含Polygon的外环，任何内环按它们在表达式中出现的顺序作为附加元素。If the expression to cast is a well-formed geometry of type Polygon, the function result is a MultiLineString containing the outer ring of the Polygon as its first element and any inner rings as additional elements in the order they appear in the expression.
如果要转换的表达式是类型为MultiLineString的格式正确的几何体，则函数结果就是该MultiLineString。If the expression to cast is a well-formed geometry of type MultiLineString, the function result is that MultiLineString.
如果要转换的表达式是类型为MultiPolygon的格式良好的几何体，并且仅包含没有内环的多边形，则函数结果为包含表达式中按顺序出现的多边形环的MultiLineString。如果表达式包含任何带有内环的多边形，则会发生ER_WRONG_PARAMETERS_TO_STORED_FCT错误。If the expression to cast is a well-formed geometry of type MultiPolygon containing only polygons without inner rings, the function result is a MultiLineString containing the polygon rings in the order they appear in the expression. If the expression contains any polygons with inner rings, an ER_WRONG_PARAMETERS_TO_STORED_FCT error occurs.
如果要转换的表达式是一个类型为GeometryCollection的格式正确的几何体，并且只包含线串，则该函数的结果是包含这些线串的MultiLineString。如果表达式为空或包含其他几何类型，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。If the expression to cast is a well-formed geometry of type GeometryCollection containing only linestrings, the function result is a MultiLineString containing those linestrings. If the expression is empty or contains other geometry types, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为 LineString、Polygon、MultiLineString、MultiPolygon 或 GeometryCollection 之外的良构几何，将发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type other than LineString, Polygon, MultiLineString, MultiPolygon, or GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY error occurs.

转换为多边形集合的条件Conditions for Casts to MultiPolygon

当转换结果类型为MultiPolygon时，适用以下条件：When the cast result type is MultiPolygon, these conditions apply:

如果要进行转换的表达式是类型为Polygon的格式良好的几何体，则函数结果是一个MultiPolygon，其中包含该Polygon作为其唯一元素。If the expression to cast is a well-formed geometry of type Polygon, the function result is a MultiPolygon containing the Polygon as its sole element.
如果要转换的表达式是类型为MultiLineString的良构几何，其中所有元素都是环，则函数结果是一个MultiPolygon，其中每个表达式元素包含一个仅有外环的Polygon。如果有任何元素不是环，将会发生ER_INVALID_CAST_TO_GEOMETRY错误。如果有任何环的方向不正确（外环必须为逆时针方向），将会发生ER_INVALID_CAST_POLYGON_RING_DIRECTION错误。If the expression to cast is a well-formed geometry of type MultiLineString where all elements are rings, the function result is a MultiPolygon containing a Polygon with only an outer ring for each element of the expression. If any element is not a ring, an ER_INVALID_CAST_TO_GEOMETRY error occurs. If any ring is not in the correct order (exterior ring must be counter-clockwise), an ER_INVALID_CAST_POLYGON_RING_DIRECTION error occurs.
如果要转换的表达式是类型为MultiPolygon的格式正确的几何图形，则函数结果为该MultiPolygon。If the expression to cast is a well-formed geometry of type MultiPolygon, the function result is that MultiPolygon.
如果要转换的表达式是类型为GeometryCollection的格式正确的几何图形，并且仅包含多边形，则函数结果为包含这些多边形的MultiPolygon。如果表达式为空或包含其他类型的几何图形，则会发生ER_INVALID_CAST_TO_GEOMETRY错误。If the expression to cast is a well-formed geometry of type GeometryCollection containing only polygons, the function result is a MultiPolygon containing those polygons. If the expression is empty or contains other geometry types, an ER_INVALID_CAST_TO_GEOMETRY error occurs.
如果要转换的表达式是类型为 Polygon、MultiLineString、MultiPolygon 或 GeometryCollection 之外的良构几何，将会发生 ER_INVALID_CAST_TO_GEOMETRY 错误。If the expression to cast is a well-formed geometry of type other than Polygon, MultiLineString, MultiPolygon, or GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY error occurs.

转换为 GeometryCollection 的条件Conditions for Casts to GeometryCollection

当转换的结果类型为 GeometryCollection 时，适用以下条件：When the cast result type is GeometryCollection, these conditions apply:

GeometryCollection 和 GeomCollection 是同一结果类型的同义词。GeometryCollection and GeomCollection are synonyms for the same result type.
如果要转换的表达式是类型为Point的格式良好的几何体，则函数结果是一个GeometryCollection，其中包含该Point作为唯一元素。If the expression to cast is a well-formed geometry of type Point, the function result is a GeometryCollection containing that Point as its sole element.
如果要转换的表达式是类型为LineString的格式良好的几何体，则函数结果是一个GeometryCollection，其中包含该LineString作为唯一元素。If the expression to cast is a well-formed geometry of type LineString, the function result is a GeometryCollection containing that LineString as its sole element.
如果要转换的表达式是类型为Polygon的格式良好的几何体，则函数结果是一个GeometryCollection，其中包含该Polygon作为唯一元素。If the expression to cast is a well-formed geometry of type Polygon, the function result is a GeometryCollection containing that Polygon as its sole element.
如果要转换的表达式是类型为 MultiPoint 的格式良好的几何体，则函数结果是一个 GeometryCollection，其中包含表达式中出现顺序的点。If the expression to cast is a well-formed geometry of type MultiPoint, the function result is a GeometryCollection containing the points in the order they appear in the expression.
如果要转换的表达式是类型为 MultiLineString 的格式良好的几何体，则函数结果是一个 GeometryCollection，其中包含表达式中出现顺序的线串。If the expression to cast is a well-formed geometry of type MultiLineString, the function result is a GeometryCollection containing the linestrings in the order they appear in the expression.
如果要转换的表达式是类型为 MultiPolygon 的格式良好的几何体，则函数结果是一个 GeometryCollection，其中包含表达式中出现顺序的 MultiPolygon 元素。If the expression to cast is a well-formed geometry of type MultiPolygon, the function result is a GeometryCollection containing the elements of the MultiPolygon in the order they appear in the expression.
如果要转换的表达式是类型为GeometryCollection的格式正确的几何体，则函数的结果是该GeometryCollection。If the expression to cast is a well-formed geometry of type GeometryCollection, the function result is that GeometryCollection.

类型转换操作的其他用途Other Uses for Cast Operations

类型转换函数对于在CREATE TABLE ... SELECT语句中创建具有特定类型的列非常有用：The cast functions are useful for creating a column with a specific type in a CREATE TABLE ... SELECT statement:

mysql> CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE) AS c1;
mysql> SHOW CREATE TABLE new_table\G
*************************** 1. row ***************************
       Table: new_table
Create Table: CREATE TABLE `new_table` (
  `c1` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

CAST 函数对于按字典顺序对 ENUM 列进行排序非常有用。通常，ENUM 列的排序是使用内部的数值进行的。将值转换为 CHAR 会得到字典顺序排序：The cast functions are useful for sorting ENUM columns in lexical order. Normally, sorting of ENUM columns occurs using the internal numeric values. Casting the values to CHAR results in a lexical sort:

SELECT enum_col FROM tbl_name 
  ORDER BY CAST(enum_col AS CHAR);

CAST() 如果作为更复杂表达式的一部分使用，例如 CONCAT('Date: ',CAST(NOW() AS DATE))，结果也会发生变化。CAST() also changes the result if you use it as part of a more complex expression such as CONCAT('Date: ',CAST(NOW() AS DATE)).

对于时间值，几乎不需要使用 CAST() 来以不同的格式提取数据。相反，可以使用诸如 EXTRACT()、DATE_FORMAT() 或 TIME_FORMAT() 之类的函数。参见第 14.7 节，“日期和时间函数”。For temporal values, there is little need to use CAST() to extract data in different formats. Instead, use a function such as EXTRACT(), DATE_FORMAT(), or TIME_FORMAT(). See Section 14.7, “Date and Time Functions”.

要将字符串转换为数字，通常只需在数值上下文中使用字符串值即可：To cast a string to a number, it normally suffices to use the string value in numeric context:

mysql> SELECT 1+'1';
       -> 2

这对于十六进制和位字面量也同样适用，它们默认是二进制字符串：That is also true for hexadecimal and bit literals, which are binary strings by default:

mysql> SELECT X'41', X'41'+0;
        -> 'A', 65
mysql> SELECT b'1100001', b'1100001'+0;
        -> 'a', 97

一个在算术运算中使用的字符串将在表达式求值过程中被转换为浮点数。A string used in an arithmetic operation is converted to a floating-point number during expression evaluation.

一个在字符串上下文中使用的数字会被转换为字符串：A number used in string context is converted to a string:

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

关于数字隐式转换为字符串的信息，请参见第 14.3 节，“表达式求值中的类型转换”。For information about implicit conversion of numbers to strings, see Section 14.3, “Type Conversion in Expression Evaluation”.

MySQL 支持对有符号和无符号 64 位数值进行算术运算。对于数值运算符（例如 + 或 -），如果其中一个操作数是无符号整数，则结果默认是无符号（参见第 14.6.1 节，“算术运算符”）。要覆盖此默认行为，可以使用 SIGNED 或 UNSIGNED 类型转换运算符，将数值分别转换为有符号或无符号 64 位整数。MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 14.6.1, “Arithmetic Operators”). To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT 1 - 2;
        -> -1
mysql> SELECT CAST(1 - 2 AS UNSIGNED);
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED);
        -> -1

如果任一操作数是浮点数值，则结果为浮点数值，并且不受前述规则的影响。（在此上下文中，DECIMAL 列值被视为浮点数值。）If either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

SQL 模式会影响转换操作的结果（参见第 7.1.11 节，“服务器 SQL 模式”）。示例：The SQL mode affects the result of conversion operations (see Section 7.1.11, “Server SQL Modes”). Examples:

对于将“零”日期字符串转换为日期，CONVERT() 和 CAST() 在启用 NO_ZERO_DATE SQL 模式时返回 NULL 并产生警告。For conversion of a “zero” date string to a date, CONVERT() and CAST() return NULL and produce a warning when the NO_ZERO_DATE SQL mode is enabled.
对于整数减法，如果启用 NO_UNSIGNED_SUBTRACTION SQL 模式，即使任一操作数为无符号数，减法结果也是带符号的。For integer subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the subtraction result is signed even if any operand is unsigned.

首页上一级下一节