12.11 转换函数和运算符_MySQL 5.6 参考手册

扩展 MySQL 8.0 / 第 6 章向 MySQL 添加函数 / 12.11 转换函数和运算符

12.11 转换函数和运算符

表 12.15 转换函数和运算符

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

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

Cast 函数和运算符说明

BINARY expr
运算符将BINARY表达式转换为二进制字符串（具有 binary字符集和 binary排序规则的字符串）。for 的一个常见用途 BINARY是强制使用数字字节值而不是逐个字符地逐字节进行字符串比较。BINARY运算符还会导致比较中的尾随空格很重要。有关字符集排序规则与非二进制字符集排序规则之间差异的信息， binary请参阅第 10.8.5 节，“二进制排序规则与 _bin 排序规则的比较”。 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影响整个操作；它可以在具有相同结果的任一操作数之前给出。
要将字符串表达式转换为二进制字符串，这些结构是等效的：
```
CONVERT(expr USING BINARY)
CAST(expr AS BINARY)
BINARY expr
```
如果一个值是一个字符串字面量，它可以指定为一个二进制字符串而不用使用 _binary字符集引入器转换它：
```
mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT _binary 'a' = 'A';
        -> 0
```
有关介绍人的信息，请参阅第 10.3.8 节，“字符集介绍人”。
表达式中的BINARY运算符在效果上不同于 BINARY字符列定义中的属性。对于用 BINARY属性定义的字符列，MySQL 分配表默认字符集和该字符集的二进制 ( _bin) 排序规则。每个非二进制字符集都有一个_bin 排序规则。例如，如果表默认字符集是utf8，则这两个列定义是等效的：
```
CHAR(10) BINARY
CHAR(10) CHARACTER SET utf8 COLLATE utf8_bin
```
在、或列CHARACTER SET binary的定义中使用会导致该列被视为相应的二进制字符串数据类型。例如，下面的定义对是等价的： CHARVARCHARTEXT
```
CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB
```
如果BINARY从mysql客户端中调用，则二进制字符串使用十六进制表示法显示，具体取决于--binary-as-hex. 有关该选项的更多信息，请参阅第 4.5.1 节，“mysql — MySQL 命令行客户端”。
CAST(expr AS type)
CAST(expr AS type采用任何类型的表达式并生成指定类型的结果值。这个操作也可以表示为，是等价的。 CONVERT(expr, type)
这些type值是允许的：
- BINARY[(N)]
  生成具有 BINARY数据类型的字符串。如果给出了可选长度N，则导致转换使用不超过参数的字节数。短于字节的值用字节填充到长度为. BINARY(N)NN0x00N
  有关转换为如何 BINARY影响比较的描述，请参阅第 11.3.3 节，“BINARY 和 VARBINARY 类型”。
- 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值，它们指定最大位数（精度）和小数点后的位数（比例）。
- NCHAR[(N)]
  与类似CHAR，但会生成具有国家字符集的字符串。请参阅第 10.3.7 节，“国家字符集”。
  与不同CHAR，NCHAR 不允许指定尾随字符集信息。
- SIGNED [INTEGER]
  产生一个带符号的整数值。
- TIME
  产生一个TIME值。
- UNSIGNED [INTEGER]
  产生一个无符号整数值。
CONVERT(expr USING transcoding_name)
CONVERT(expr,type)
CONVERT(expr USING transcoding_name) 是标准的 SQL 语法。的非USING 形式CONVERT()是 ODBC 语法。
CONVERT(expr USING transcoding_name) 在不同字符集之间转换数据。在MySQL中，转码名称与对应的字符集名称相同。例如，此语句将'abc'默认字符集中的字符串转换为字符集中对应的字符串 utf8：
```
SELECT CONVERT('abc' USING utf8);
```
CONVERT(expr, type)syntax (without USING) 采用表达式和 type指定结果类型的值，并生成指定类型的结果值。这个操作也可以表示为，是等价的。有关详细信息，请参阅的说明。 CAST(expr AS type)CAST()

字符集转换

CONVERT()with USING子句在字符集之间转换数据：

CONVERT(expr USING transcoding_name)

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

例子：

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

要在字符集之间转换字符串，您还可以使用语法（不带）或，它们是等效的： CONVERT(expr, type)USINGCAST(expr AS type)

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

如果您像刚才显示的那样指定，则结果的字符集和排序规则是默认排序规则。如果省略，则结果的字符集和排序规则由确定默认连接字符集和排序规则的和系统变量定义（请参阅第 10.4 节，“连接字符集和排序规则”）。 CHARACTER SET charset_namecharset_namecharset_nameCHARACTER SET charset_namecharacter_set_connectioncollation_connection

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

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

但这些是非法的：

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

对于字符串文字，另一种指定字符集的方法是使用字符集介绍器。在前面的示例中是介绍人的实例_latin1 。_latin2与 CAST(), 或 CONVERT()等将字符串从一种字符集转换为另一种字符集的转换函数不同，引入符将字符串文字指定为具有特定字符集，不涉及转换。有关详细信息，请参阅第 10.3.8 节，“字符集介绍人”。

字符串比较的字符集转换

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

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

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

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 |
+---------------------------------+

字符集转换在二进制字符串的字母大小写转换之前也很有用。 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 可能无法有效地使用索引。

Cast 操作的其他用途

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

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

对于时间值，几乎不需要使用 CAST()不同格式的数据来提取。相反，请使用诸如 EXTRACT()、 DATE_FORMAT()或之类的函数TIME_FORMAT()。请参阅第 12.7 节，“日期和时间函数”。

要将字符串转换为数字，通常在数字上下文中使用字符串值就足够了：

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.3 节，“表达式求值中的类型转换”。

MySQL 支持带符号和无符号 64 位值的算术。对于其中一个操作数是无符号整数的数字运算符（例如 +or ），默认情况下结果是无符号的（请参阅第 12.6.1 节，“算术运算符”）。要覆盖它，请使用or cast 运算符分别将值转换为有符号或无符号的 64 位整数。 -SIGNEDUNSIGNED

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()并在启用 SQL 模式时CAST()返回 NULL并产生警告。NO_ZERO_DATE
对于整数减法，如果 NO_UNSIGNED_SUBTRACTION 启用了SQL模式，即使任何操作数是无符号的，减法结果也是有符号的。