MySQL的函数和运算符 - 转换函数和运算符(1)

数据库杂货铺 2021-07-11

837

转换函数和运算符

名称	说明
BINARY	把字符串转换为二进制字符串
CAST()	将值转换为特定类型
CONVERT()	将值转换为特定类型

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

转换函数和运算符描述

l BINARY expr

BINARY 运算符将表达式转换为二进制字符串(具有 binary 字符集和 binary 排序规则的字符串)。BINARY 的一个常见用法是强制逐字节而不是逐字符进行字符串比较。BINARY 运算符还会使比较中的尾随空格具有重要意义。

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

表达式中的 BINARY 运算符与字符列定义中的 BINARY 属性的效果不同。对于使用 BINARY 属性定义的字符列，MySQL 给表分配默认字符集和该字符集的二进制(_bin)排序规则。每个非二进制字符集都有一个 _bin 排序规则。例如，如果表的默认字符集是 utf8mb4，这两个列的定义是等价的：

CHAR(10) BINARY
CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

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

CHAR(10) CHARACTER SET binary
BINARY(10)

VARCHAR(10) CHARACTER SET binary
VARBINARY(10)

TEXT CHARACTER SET binary
BLOB

如果从 mysql 客户端调用 BINARY，则二进制字符串使用十六进制表示法显示，具体取决于 --binary-as-hex 的值。

l CAST(expr AS type [ARRAY])

CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])

timezone_specifier: [INTERVAL] '+00:00' | 'UTC'

使用 CAST(expr AS type [ARRAY]) 语法，CAST() 函数接受某些类型的表达式，并产生指定类型的结果值。这个操作也可以表示为 CONVERT(expr, type)，这是等价的。

允许的 type 值：

■ BINARY[(N)]

产生一个具有 VARBINARY 数据类型的字符串，除非表达式 expr 为空(零长度)，结果类型为 BINARY(0)。如果给出了可选长度 N, BINARY(N) 将导致转换使用不超过 N 个字节的参数。小于 N 字节的值将用 0x00 填充到 N 字节长度。如果可选的长度 N 没有给出，MySQL 将从表达式中计算最大长度。如果提供的或计算的长度大于内部阈值，则结果类型为 BLOB。如果长度仍然太长，则结果类型为 LONGBLOB。

■ CHAR[(N)] [charset_info]

生成具有 VARCHAR 数据类型的字符串。除非表达式 expr 是空的(零长度)，结果类型是 CHAR(0)。如果给出了可选的长度 N， CHAR(N) 将导致转换使用不超过 N 个字符的参数。小于 N 个字符的值不进行填充。如果可选的长度 N 没有给出，MySQL 从表达式中计算最大长度。如果提供的或计算的长度大于内部阈值，则结果类型为 TEXT。如果长度仍然太长，则结果类型为 LONGTEXT。

如果没有 charset_info 子句，CHAR 将生成带有默认字符集的字符串。要显式指定字符集，允许使用以下 charset_info 值：

○ CHARACTER SET charset_name: 生成具有给定字符集的字符串。

○ ASCII: CHARACTER SET latin1 的缩写。

○ UNICODE: CHARACTER SET ucs2 的缩写。

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

■ DATE

生成 DATE 值。

■ DATETIME[(M)]

生成 DATETIME 值。如果给出了可选的 M 值，它将指定小数秒精度。

■ DECIMAL[(M[,D])]

产生 DECIMAL 值。如果给出了可选的 M 和 D 值，它们将指定最大位数(精度)和小数点后的位数(标度)。如果省略 D，则默认为 0。如果省略 M，则默认为 10。

■ DOUBLE

产生 DOUBLE 结果。MySQL 8.0.17 新增。

■ FLOAT[(p)]

如果未指定精度 p，则产生 FLOAT 类型的结果。如果提供了 p 并且 0 <= < p <= 24，则结果为 FLOAT 类型。如果 25 <= p <= 53，结果是 DOUBLE 类型。如果 p < 0 或 p > 53，则返回错误。MySQL 8.0.17 新增。

■ JSON

生成 JSON 值。

■ NCHAR[(N)]

类似于 CHAR，但生成具有国家字符集的字符串。

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

■ REAL

生成类型为 REAL 的结果。如果启用 REAL_AS_FLOAT SQL 模式，这实际上是 FLOAT，否则结果是 DOUBLE 类型。

■ SIGNED [INTEGER]

产生带符号的 BIGINT 值。

■ spatial_type

从 MySQL 8.0.24 开始，CAST() 和 CONVERT() 支持将几何值从一种空间类型转换为另一种空间类型，用于空间类型的某些组合。

■ TIME[(M)]

生成 TIME 值。如果给出了可选的 M 值，它将指定小数秒精度。

■ UNSIGNED [INTEGER]

产生一个无符号 BIGINT 值。

■ YEAR

产生 YEAR 值。MySQL 8.0.22 新增。以下规则规定如何转换为 YEAR：

○ 对于 1901-2155 范围内的四位数字，或者对于可以被解释为此范围内的四位数字的字符串，返回相应的 YEAR 值。

○ 对于由一个或两个数字组成的数字，或者可以被解释为这样数字的字符串，按照以下规则返回 YEAR 值：

● 如果数字在 1-69 的范围内，加 2000 并返回和。

● 如果数字在 70-99 的范围内，加 1900 并返回和。

○ 对于计算结果为 0 的字符串，返回 2000。

○ 对于数字 0，返回 0。

○ 对于 DATE、DATETIME 或 TIMESTAMP 值，返回值的 YEAR 部分。对于 TIME 值，返回当前年份。

如果没有指定 TIME 参数的类型，可能会得到与你期望的不同的结果，如下所示：

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，如下所示：

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。

○ 对于无法成功转换为 YEAR 的值，返回 NULL。

包含非数字字符的字符串值必须在转换前被截断，这样会引发警告：

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

在 MySQL 8.0.17 及更高版本中，InnoDB 允许使用额外的 ARRAY 关键字，作为 CREATE INDEX、CREATE TABLE 和 ALTER TABLE 语句的一部分，在 JSON 数组上创建多值索引。除这些情况之外，不支持 ARRAY。被索引的列必须是 JSON 类型的列。对于 ARRAY, AS 关键字后面的类型可以指定 CAST() 支持的任何类型，除了 BINARY、JSON 和 YEAR。

注意

与 CAST() 不同，CONVERT() 不支持多值索引创建或 ARRAY 关键字。

从 MySQL 8.0.22 开始，CAST() 支持检索 UTC 格式的 TIMESTAMP 值，使用 AT TIMEZONE 操作符。唯一支持的时区是 UTC，可以指定为 '+00:00' 或 'UTC'。此语法支持的唯一返回类型是 DATETIME，其可选精度说明符的范围是 0 到 6(包括6)。

也支持使用时区偏移量的 TIMESTAMP 值。

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)

如果使用 'UTC' 作为时区指示符调用 CAST()，并且服务器引发形如此类错误：Unknown or incorrect time zone: 'UTC'，则可能需要安装 MySQL 时区表。

AT TIME ZONE 不支持 ARRAY 关键字，CONVERT() 函数也不支持。

● CONVERT(expr USING transcoding_name)

CONVERT(expr,type)

CONVERT(expr USING transcoding_name) 是标准的 SQL 语法。CONVERT() 没有 USING 的使用形式是 ODBC 语法。

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

SELECT CONVERT('abc' USING utf8mb4);

CONVERT(expr, type) 语法 (不使用 USING) 接受一个表达式和指定结果类型的 type 值，并生成指定类型的结果值。这个操作也可以表示为 CAST(expr AS type)，这是等价的。

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 转换函数和运算符(1)

评论