强制转换函数和运算符

表强制转换函数和运算符

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

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

CONVERT()一个USING子句可在不同字符集之间转换数据：

CONVERT(expr USING transcoding_name)

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

例子：

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()不使用USING或CAST()：

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

例子：

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

如果您仅指定显示，则字符集和结果校验规则为，默认校验规则为。如果省略，则结果的字符集和校验规则由和变量定义，这些变量和系统变量确定默认的连接字符集和校验规则（请参见“连接字符集和校验规则”）。CHARACTER SET charset_namecharset_namecharset_nameCHARACTER SET charset_namecharacter_set_connectioncollation_connection

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

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;

但是这些是非法的：

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

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

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

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

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

要使用其他字符集，请utf8mb4在前面的语句中用其名称代替（并类似地使用其他校验规则）。

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和_latin2在前面的例子中是导引器的实例。与诸如CAST()，或的CONVERT()将字符串从一个字符集转换为另一字符集的转换函数不同，介绍程序将字符串文字指定为具有特定字符集，而无需进行转换。有关更多信息，请参见“字符集简介”。

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

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可能无法有效使用索引。

强制转换功能对于在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=utf8mb4

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

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

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

对于时间值，几乎不需要使用CAST()提取不同格式的数据。相反，使用功能，例如EXTRACT()，DATE_FORMAT()或TIME_FORMAT()。请参见“日期和时间函数”。

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

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'

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

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

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

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

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

用于将“零”日期字符串转换为日期，CONVERT()并在启用SQL模式时CAST()返回NULL并产生警告NO_ZERO_DATE。
对于整数减法，如果NO_UNSIGNED_SUBTRACTION启用了 SQL模式，则即使任何操作数都为无符号，减法结果也将被签名。

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

BINARYexpr
BINARY操作者将所表达的二进制串（即具有一个字符串binary的字符集和binary归类）。通常的用途BINARY是强制使用数字字节值而不是一个字符一个字节地进行字符串比较。该BINARY运营商还导致比较尾随空格是显著。有关之间的差异信息binary的核对binary字符集和_bin的非二进制字符集的校验规则，请参见“加密二进制日志文件”。
```
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校验规则。例如，如果表默认字符集为utf8mb4，则这两个列定义是等效的：
```
CHAR(10) BINARY
CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
```
使用CHARACTER SET binary中的定义CHAR，VARCHAR或TEXT柱导致列与相应的二进制字符串的数据类型进行处理。例如，以下对定义是等效的：
```
CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB
```
CAST(expr AS type[ARRAY])
该CAST()函数采用任何类型的表达式，并产生指定类型的结果值，类似于CONVERT()。有关更多信息，请参见CONVERT()。
在MySQL 8.0.17和以后，InnoDB允许使用一个附加的ARRAY关键字，用于创建一个上的多值索引JSON阵列的一部分CREATE INDEX，CREATE TABLE和ALTER TABLE语句。ARRAY不支持，除非用于在这些语句之一中创建多值索引时（在这种情况下是必需的）。被索引的列必须是类型的列JSON。（CONVERT()不不支持多值索引创建或ARRAY关键字。）使用ARRAY时，type以下的AS关键字可以指定任何所支持的类型的CAST()，用的例外BINARY和JSON。有关语法信息和示例以及其他相关信息，请参见多值索引。
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'默认字符集中的字符串转换为字符集中的相应字符串utf8mb4：
```
SELECT CONVERT('abc' USING utf8mb4);
```
CONVERT()不带，USING并CAST()使用一个表达式和一个type值指定结果类型。这些type值是允许的：
- BINARY[(N)]
  产生具有BINARY数据类型的字符串。有关如何影响比较的描述，请参见“ BINARY和VARBINARY类型”。如果N给出了可选的长度，则导致强制类型转换使用不超过参数字节的字节数。小于字节的值用字节填充，长度为。BINARY(N)NN0x00N
- CHAR[(N)][charset_info]
  产生具有CHAR数据类型的字符串。如果N给出了可选的长度，则导致强制类型转换最多使用参数字符。小于字符的值不会出现填充。CHAR(N)NN
  如果没有charset_info子句，则CHAR生成具有默认字符集的字符串。要明确指定字符集，可以使用以下charset_info值：
  - CHARACTER SET charset_name：产生具有给定字符集的字符串。
  - ASCII：的简写CHARACTER SET latin1。
  - UNICODE：的简写CHARACTER SET ucs2。
  在所有情况下，字符串都具有字符集默认校验规则。
- DATE
  产生一个DATE值。
- DATETIME
  产生一个DATETIME值。
- DECIMAL[(M[,D])]
  产生一个DECIMAL值。如果给出了可选值M和D值，则它们指定最大位数（精度）和小数点后的位数（小数位数）。
- DOUBLE
  产生DOUBLE结果。在MySQL 8.0.17中添加。
- FLOAT[(p)]
  如果p未指定精度，则产生type的结果FLOAT。如果p提供并且0 <=<<p= 24，则结果为类型FLOAT。如果25 <=p<= 53，则结果为类型DOUBLE。如果p<0或p> 53，则返回错误。在MySQL 8.0.17中添加。
- JSON
  产生一个JSON值。有关在JSON和其他类型之间进行值转换的规则的详细信息，请参见 JSON值的比较和排序。
- NCHAR[(N)]
  与相似CHAR，但会产生带有国家字符集的字符串。请参见“国家字符集”。
  与不同CHAR，NCHAR不允许指定尾随字符集信息。
- REAL
  产生type的结果REAL。这实际上是FLOAT，如果REAL_AS_FLOAT启用了SQL模式；否则结果为类型DOUBLE。
- SIGNED[INTEGER]
  产生一个有符号的整数值。
- TIME
  产生一个TIME值。
- UNSIGNED[INTEGER]
  产生一个无符号整数值。