12.11 Cast Functions and OperatorsCast函数和运算符

Table 12.15 Cast Functions and OperatorsCast函数和运算符

Name名称Description描述
BINARYCast a string to a binary string将字符串转换为二进制字符串
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.强制转换函数和运算符支持将值从一种数据类型转换为另一种数据类型。

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

CONVERT(expr USING transcoding_name)

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

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;

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(expr, type)语法（不使用），或CAST(expr AS type)，这相当于：

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);

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. 如果如图所示指定 CHARACTER SET charset_name，则结果的字符集和排序规则是charset_name和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 10.4, “Connection Character Sets and Collations”).如果省略了CHARACTER SET charset_name，则结果的字符集和排序规则由character_set_connection和character_set_connection系统变量定义，这些变量确定默认的连接字符集和排序规则（请参阅第10.4节，“连接字符集和排序规则”）。

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:CONVERT()或CAST()调用中不允许使用COLLATE子句，但可以将其应用于函数结果。例如，这些是合法的：

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);

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. 通常，不能以不区分大小写的方式比较BLOB值或其他二进制字符串，因为二进制字符串使用二进制字符集，而二进制字符集与lettercase概念没有排序规则。To perform a case-insensitive comparison, first use the CONVERT() or CAST() function to convert the value to a nonbinary string. 要执行不区分大小写的比较，请首先使用CONVERT()或CAST()函数将值转换为非二进制字符串。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. 例如，如果转换结果排序规则不区分大小写，则LIKE操作不区分大小写。That is true for the following operation because the default utf8mb4 collation (utf8mb4_0900_ai_ci) is not case-sensitive:这对于以下操作是正确的，因为默认的utf8mb4排序规则（utf8mb4_0900_ai_ci）不区分大小写：

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

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

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

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

CONVERT() and CAST() can be used more generally for comparing strings represented in different character sets. CONVERT()和CAST()通常用于比较不同字符集中表示的字符串。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 |
+---------------------------------+

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. 前面例子中的_latin1和_latin2是引导器的例子。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. 与CAST()或CONVERT()等转换函数将字符串从一个字符集转换为另一个字符集不同，引导器将字符串文字指定为具有特定的字符集，而不涉及转换。For more information, see Section 10.3.8, “Character Set Introducers”.有关更多信息，请参阅第10.3.8节，“字符集引导器”。

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. LOWER()和UPPER()在直接应用于二进制字符串时是无效的，因为lettercase的概念不适用。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                           |
+-------------+------------------------------------+

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

The cast functions are useful for creating a column with a specific type in a CREATE TABLE ... SELECT statement:在CREATE TABLE ... SELECT语句中，若要创建具有特定类型的列，则CAST函数很有用：

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

The cast functions are useful for sorting ENUM columns in lexical order. CAST函数用于按词法顺序对ENUM列进行排序。Normally, sorting of ENUM columns occurs using the internal numeric values. 通常，使用内部数值对ENUM列进行排序。Casting the values to CHAR results in a lexical sort:将值强制转换为CHAR将导致词汇排序：

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

CAST() also changes the result if you use it as part of a more complex expression such as CONCAT('Date: ',CAST(NOW() AS DATE)).如果将其用作更复杂表达式（如CONCAT('Date: ',CAST(NOW() AS DATE))）的一部分，CAST()还会更改结果。

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

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'

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

MySQL supports arithmetic with both signed and unsigned 64-bit values. MySQL支持有符号和无符号64位值的算术。For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). 对于其中一个操作数是无符号整数的数字运算符（如+或-），默认情况下结果是无符号的（请参阅第12.6.1节，“算术运算符”）。To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.若要重写此值，请使用SIGNED或UNSIGNED强制转换运算符将值分别强制转换为有符号或无符号64位整数。

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

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.)（在此上下文中，DECIMAL列值被视为浮点值。）

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

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

The following list describes the available cast functions and operators:下表描述了可用的强制转换函数和运算符：