强制转换函数和运算符

强制转换函数和运算符

名称	描述
BINARY	将字符串转换为二进制字符串
CAST()	将值强制转换为特定类型
CONVERT()	将值强制转换为特定类型

强制转换函数和运算符可将值从一种数据类型转换为另一种数据类型。

带有USING子句的CONVERT()提供了一种在不同字符之间转换数据的方法：

CONVERT(expr USING transcoding_name)

在MySQL中，转码名称与相应的字符集名称相同。

例子：

SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8_table (utf8_column)
    SELECT CONVERT(latin1_column USING utf8) FROM latin1_table;

 要在不同字符集之间转换字符串，也可以用不带USING的CONVERT()或 CAST()：

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

例子：

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

如果您刚刚显示了CHARACTER SET charset_name，则生成的字符集和排序规则为charset_name，默认排序规则为charset_name。如果省略CHARACTER SET charset_name，则生成的字符集和排序规则由确定默认连接字符集和排序规则的character_set_connection和collation_connection系统变量定义(请参见第10.4节“连接字符集和排序规则”)。

在CONVERT()或CAST()调用中不允许COLLATE子句，但您可以将其应用于 function 结果。例如，这是合法的：

SELECT CAST('test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

但这是非法的：

SELECT CAST('test' AS CHAR CHARACTER SET utf8 COLLATE utf8_bin);

通常，您不能以不区分大小写的方式比较BLOB 值或其他二进制字符串，因为二进制字符串使用binary字符集，而该字符集与字母大小写概念无关。要执行不区分大小写的比较，请首先使用 CONVERT()或 CAST()函数将值转换为非二进制字符串。结果字符串的比较使用其排序规则。例如，如果转换结果字符集具有不区分大小写的排序规则，则 LIKE操作不区分大小写。对于以下操作，这是正确的，因为默认 latin1排序规则（latin1_swedish_ci）不区分大小写：

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

要为转换后的字符串指定特定的排序规则，请在CONVERT()调用之后使用一个COLLATE子句 ：

SELECT 'A' LIKE CONVERT(blob_col USING latin1) COLLATE latin1_german1_ci
  FROM tbl_name;

要使用不同的字符集，请在前面的语句中用其名称代替latin1（并类似地使用不同的排序规则）。

CONVERT()和 CAST()可以更普遍地用于比较以不同字符集表示的字符串。例如，比较这些字符串会导致错误，因为它们具有不同的字符集：

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 '='

将其中一个字符串转换为与另一个字符串兼容的字符集可以使使比较无错误地发生：

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

对于字符串文字，另一种指定字符集的方法是使用字符集介绍程序。 _latin1 and _latin2在前面的例子中是导引器的实例。与CAST（）或CONVERT（）等转换函数不同，它将字符串从一个字符集转换为另一个字符集时，创建者指定字符串文字具有特定的字符集，而不涉及转换。有关更多信息，请参见 第10.3.8节“字符集简介”。

字符集转换在二进制字符串的字母大写转换之前也很有用。 LOWER()和UPPER()直接应用于二进制字符串时无效，因为字母大小写的概念不适用。要执行二进制字符串的字母大小写转换，请首先使用适合于存储在字符串中的数据的字符集将其转换为非二进制字符串：

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

请注意，如果使用BINARY， CAST()或  CONVERT()转换索引列，则MySQL可能无法有效使用索引。

强制转换功能对于在CREATE TABLE ... SELECT语句中创建具有特定类型的列很有用 ：

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=latin1

强制转换功能对于按ENUM词汇顺序对列进行排序很有用 。通常，ENUM使用内部数值对列进行排序。将值强制转换 CHAR会导致词法排序：

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

如果您将结果用作更复杂表达式(如 CONCAT('Date: ',CAST(NOW() AS DATE)))的一部分，CAST()也会更改结果。

对于时间值，几乎不需要使用CAST()来提取不同格式的数据。而是使用功能，如EXTRACT()，DATE_FORMAT()或TIME_FORMAT()。见第 12.7 节，“日期和 Time 函数”。

要将字符串转换为数字，通常只需要在数字上下文中使用字符串值即可：

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

十六进制和位文字也是如此，默认情况下是二进制字符串：

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

在表达式求值期间，将算术运算中使用的字符串转换为浮点数。

字符串上下文中使用的数字将转换为字符串：

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

有关将数字隐式转换为字符串的信息，请参见第12.2节“表达式求值中的类型转换”。

MySQL支持带符号和无符号64位值的算术运算。对于其中一个操作数是无符号整数的数字运算符（例如 +或 -），默认情况下结果是无符号的（请参见第12.6.1节“算术运算符”）。要覆盖此内容，请使用SIGNEDor 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

如果任何一个操作数都是浮点值，则结果是浮点值，并且不受前面的规则的影响。（在这种情况下，DECIMAL列值被视为浮点值。）

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

SQL模式影响转换操作的结果（请参见 第5.1.10节“服务器SQL模式”）。例子：

• 对于转换“ 零 ”日期字符串的日期， CONVERT()和 CAST()返回 NULL，以及启用 NO_ZERO_DATESQL模式时产生警告。

• 对于整数减法，如果启用了 NO_UNSIGNED_SUBTRACTIONSQL模式，则即使任何操作数都为无符号，也会对减法结果进行符号处理。

以下列表描述了可用的强制转换函数和运算符：

• BINARY expr

BINARY operator 将表达式转换为二进制 string（具有二进制字符集和二进制排序规则的字符串）。二进制的一个常见用法是强制使用数字字节值逐字节进行字符串比较，而不是逐字符进行比较。二元运算符也会导致比较中的尾随空格有意义。有关二进制字符集的二进制排序规则与非二进制字符集的二进制排序规则之间的差异的信息，see Section 10.8.5, “The binary Collation Compared to _bin Collations”.

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0
mysql> SELECT 'a' = 'a ';
        -> 1
mysql> SELECT BINARY 'a' = 'a ';
        -> 0

相比之下，BINARY会影响整个操作；可以在两个操作数之前给出相同的结果。

要将字符串表达式转换为二进制字符串，这些结构是等效的：

BINARY expr
CAST(expr AS BINARY)
CONVERT(expr USING BINARY)

如果值是字符串文字，则可以使用_binary字符集介绍程序将其指定为二进制字符串，而无需执行任何转换 ：

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT _binary 'a' = 'A';
        -> 0

表达式中BINARY的运算符与BINARY字符列定义中的属性在效果上有所不同 。。使用BINARY属性定义的字符列 分配有表默认字符集和该字符集的二进制（_bin）归类。每个非二进制字符集都有一个_bin排序规则。例如，如果表默认字符集为utf8，则这两个列定义是等效的：

CHAR(10) BINARY
CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin

在CHAR、VARCHAR或TEXT列的定义中使用字符集binary会将该列视为相应的二进制字符串数据类型。例如，以下定义对是等效的：

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

• CAST(expr AS type)

CAST()函数采用任何类型的表达式，并产生指定类型的结果值，类似于 CONVERT()。有关更多信息，请参见 CONVERT()。

CAST() 是标准的SQL语法。

• CONVERT(expr,type)， CONVERT(expr USING transcoding_name)

CONVERT()函数采用任何类型的表达式，并产生指定类型的结果值。

此处对 CONVERT(expr, type)语法的讨论也适用于CAST(expr AS type)，这是等效的。

CONVERT(... USING ...)是标准的SQL语法。不带USING形式 CONVERT()是ODBC语法。

CONVERT()与 USING可以在不同字符集之间转换数据。在MySQL中，转码名称与相应的字符集名称相同。例如，以下语句将'abc'默认字符集中的字符串转换为字符集中的相应字符串 utf8：

SELECT CONVERT('abc' USING utf8);

CONVERT()不带 USING，和 CAST()使用一个表达式和一个 type值指定结果类型。这些type值是允许的：

• BINARY[(N)]

产生具有BINARY数据类型的字符串 。如果给出了可选长度N，则BINARY(N)会使强制转换使用不超过参数的N个字节。短于N字节的值用0x00字节填充到N的长度。

• HAR[(N)] [charset_info]

产生具有CHAR数据类型的字符串 。如果给出了可选长度N，则CHAR(N)会使强制转换使用不超过参数的N个字符。对于短于N个字符的值，不会发生填充。如果没有charset_info子句，CHAR会生成带有默认字符集的 string。要显式指定字符集，允许使用以下charset_info值：

• CHARACTER SET charset_name：产生具有给定字符集的字符串。

• ASCII：CHARACTER SET latin1的简写 。

• UNICODE：CHARACTER SET ucs2的简写 。

在所有情况下，字符串都具有字符集默认排序规则。

• DATE

产生一个DATE值。

• DATETIME

产生一个DATETIME值。

• DECIMAL[(M[,D])]

产生一个DECIMAL值。如果给出了可选M和 D值，则它们指定最大位数（精度）和小数点后的位数（小数位数）。

• JSON

产生一个JSON值。有关在JSON和其他类型之间进行值转换的规则的详细信息 ，请参见 JSON值的比较和排序。

• NCHAR[(N)]

与CHAR相似，但会产生带有国家字符集的字符串。请参见 第10.3.7节“国家字符集”。

与CHAR不同，NCHAR 不允许指定结尾字符集信息。

• SIGNED [INTEGER]

产生一个有符号的整数值。

• TIME

产生一个TIME值。

• UNSIGNED [INTEGER]

产生一个无符号整数值。