如何使用cast函数转换数据类型 - 云原生数据仓库AnalyticDB

AnalyticDB MySQL版支持cast函数。本文介绍如何使用cast函数将一种数据类型转换为另一种数据类型。

语法

CAST (expr AS type)

参数说明：

expr：表达式，必填。
type：目标数据类型，必填。

CAST AS BOOLEAN

CAST (expr AS BOOLEAN)

命令说明

将expr转换为BOOLEAN类型。

输入值类型

DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
VARCHAR
JSON

转换规则

原数据类型	目标数据类型	转换规则
VARCHAR JSON	BOOLEAN	原数据为true或1，会被转换成1。原数据为false或0，会被转换成0。原数据为其他，转换时返回NULL。
DOUBLE	BOOLEAN	原数据不为0.0，会被转换成1。原数据为0.0，会被转换成0。
DECIMAL(m,d) FLOAT INT/INTEGER SMALLINT TINYINT BIGINT	BOOLEAN	原数据不为0，会被转换成1。原数据为0，会被转换成0。

示例

示例1

将INT类型的数据1转换为BOOLEAN，语句如下：

SELECT CAST('1' AS BOOLEAN);

返回结果如下：

+----------------------+
| CAST('1' AS BOOLEAN) |
+----------------------+
|                    1 |
+----------------------+

示例2

将VARCHAR类型的数据a转换为BOOLEAN类型，语句如下：

SELECT CAST('a' AS BOOLEAN);

返回结果如下：

NULL

示例3

将DOUBLE的数据类型4.3转换为BOOLEAN类型，语句如下：

SELECT CAST(4.3 AS BOOLEAN);

返回结果如下：

+----------------------+
| CAST(4.3 AS BOOLEAN) |
+----------------------+
|                    1 |
+----------------------+

示例4

将INT的数据类型5转换为BOOLEAN类型，语句如下：

SELECT CAST(5 AS BOOLEAN);

返回结果如下：

+--------------------+
| CAST(5 AS BOOLEAN) |
+--------------------+
|                  1 |
+--------------------+

CAST AS DECIMAL(m,d)|FLOAT|INT|INTEGER|SMALLINT|TINYINT

CAST (expr AS [DECIMAL(m,d)|FLOAT|INT|INTEGER|SMALLINT|TINYINT])

命令说明

将expr转换为DECIMAL(m,d)、FLOAT、INT、INTEGER、SMALLINT或TINYINT类型。

输入值类型

DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
VARCHAR
JSON

转换规则

表达式expr的数值超过目标数据类型的数值范围时，类型转换会返回null。
例如，将BIGINT类型的数据99999999转换成SMALLINT类型，语句如下：
```
SELECT CAST(99999999 as SMALLINT);
```
由于99999999不在SMALLINT支持的数值范围内，此时会返回NULL。
```
NULL
```

将高精度数据类型转换为低精度的数据类型，会丢失精度。

例如，将DOUBLE类型的数据1.23456789转换为FLOAT类型，语句如下：

SELECT CAST(1.23456789 AS FLOAT);

返回结果如下：

+---------------------------+
| CAST(1.23456789 AS FLOAT) |
+---------------------------+
|                 1.2345679 |
+---------------------------+

将定点数（DECIMAL(m,d)）、浮点数（DOUBLE、FLOAT）转换为整数（INT/INTEGER、SMALLINT或TINYINT），会丢失精度。
例如，将FLOAT类型的数据1.1342转换为INT类型，语句如下：
```
SELECT CAST(1.1342 AS INT);
```
返回结果如下：
```
+---------------------+
| CAST(1.1342 AS INT) |
+---------------------+
|                   1 |
+---------------------+
```
如果表达式expr是VARCHAR类型，且不是数值，转换为DECIMAL(m,d)或FLOAT类型时会返回NULL。
例如，将VARCHAR类型的数据China转换为DECIMAL(m,d)类型，语句如下：
```
SELECT cast( 'China' AS decimal(5,2));
```
返回结果如下：
```
NULL
```
如果表达式expr是VARCHAR类型，且不是数值，转换为INT/INTEGER、SMALLINT或TINYINT类型，返回结果为0。
例如，将VARCHAR类型的数据China转换为SMALLINT类型，语句如下：
```
SELECT CAST( 'China' AS SMALLINT);
```
返回结果如下：
```
+----------------------------+
| CAST( 'China' AS SMALLINT) |
+----------------------------+
|                          0 |
+----------------------------+
```
如果表达式expr是JSON类型，且不是数值，转换为INT/INTEGER、SMALLINT或TINYINT类型，返回报错。
例如，将VARCHAR类型的数据[1,2,3]先转换为JSON类型，再转换为SMALLINT类型，语句如下：
```
SELECT CAST(CAST('[1,2,3]' AS JSON) AS SMALLINT);
```
返回错误如下：
```
ERROR 1815 (HY000): [20034, 2021091814103119216818804803453190138] : Cannot cast json to smallint
```

示例

将BIGINT类型的数据2001012转换为FLOAT类型，语句如下：

SELECT CAST(2001012 AS FLOAT);

返回结果如下：

+-------------------------+
| CAST(2001012 AS FLOAT)  |
+-------------------------+
|               2001012.0 |
+-------------------------+

CAST AS BIGINT

CAST (expr AS BIGINT)

命令说明

将expr转换为BIGINT类型。

输入值类型

BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR

转换规则

VARCHAR类型的数据不是数值时，转换为BIGINT类型，返回结果为0。
例如，将VARCHAR类型的数据a转换为BIGINT，语句如下：
```
SELECT CAST('a' AS BIGINT);
```
返回结果如下：
```
+---------------------+
| CAST('a' AS BIGINT) |
+---------------------+
|                   0 |
+---------------------+
```

FLOAT、DOUBLE、DECIMAL(m,d)类型转换为BIGINT类型，精度会丢失。

例如，将DOUBLE类型的数据1.23456789转换为BIGINT类型，语句如下：

SELECT CAST(1.23456789 AS BIGINT);

返回结果如下：

+----------------------------+
| CAST(1.23456789 AS BIGINT) |
+----------------------------+
|                          1 |
+----------------------------+

JSON类型的数据不是数值时，转换为BIGINT类型，会返回NULL。
例如，将JSON类型的数据{}转换为BIGINT类型，语句如下：
```
SELECT CAST(JSON'{}'AS BIGINT);
```
返回结果如下：
```
NULL
```

示例

将DATE类型的数据2021-09-18转换为BIGINT类型，语句如下：

SELECT CAST(DATE '2021-09-18' AS BIGINT);

返回结果如下：

+-----------------------------------+
| CAST(DATE '2021-09-18' AS BIGINT) |
+-----------------------------------+
|                          20210918 |
+-----------------------------------+

将JSON类型的数据-1转换为BIGINT类型，语句如下：

SELECT CAST(JSON '-1' AS BIGINT);

返回结果如下：

+---------------------------+
| CAST(JSON '-1' AS BIGINT) |
+---------------------------+
|                        -1 |
+---------------------------+

将DOUBLE类型的数据FLOOR(4/5)转换为BIGINT类型，语句如下：

SELECT CAST(FLOOR(4/5) as BIGINT);

返回结果如下：

+---------------------------+
|CAST(FLOOR(4/5) as BIGINT) |
+---------------------------+
|                        0  |
+---------------------------+

CAST AS DOUBLE

CAST (expr AS DOUBLE)

命令说明

将expr转换为DOUBLE类型。

输入值类型

BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
JSON

转换规则

若VARCHAR类型的数据不是数值，转换为DOUBLE类型，返回结果为0.0。
例如，将VARCHAR类型的数据China转换为DOUBLE类型，语句如下：
```
SELECT CAST( 'China' AS DOUBLE);
```
返回结果如下：
```
+--------------------------+
| CAST( 'China' AS DOUBLE) |
+--------------------------+
|                      0.0 |
+--------------------------+
```
若JSON类型的数据不是数值，转换为DOUBLE时会返回NULL。
例如，将JSON类型的数据{}转换为DOUBLE类型，语句如下：
```
SELECT CAST(JSON '{}' AS DOUBLE);
```
返回结果如下：
```
NULL
```

示例

将DATE类型的数据2021-09-17转换为DOUBLE类型，语句如下：

SELECT CAST(DATE '2021-09-17' AS DOUBLE);

返回结果如下：

+------------------------------------+
| CAST(DATE '2021-09-17' AS DOUBLE)  |
+------------------------------------+
|                        2.0210917E7 |
+------------------------------------+

CAST AS DATE|DATETIME|TIMESTAMP|TIME

CAST (expr AS DATE|DATETIME|TIMESTAMP|TIME)

命令说明

将expr转换为DATE、DATETIME、TIMESTAMP或TIME类型。

输入值类型

DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
JSON

转换规则

若VARCHAR类型或BIGINT类型的数据不符合时间类型格式，转换后的结果为NULL。
例如，将VARCHAR类型的数据a转换为TIME类型，语句如下：
```
SELECT CAST('a' AS TIME);
```
由于格式不匹配会返回NULL，结果如下：
```
+-------------------+
| CAST('a' AS TIME) |
+-------------------+
| NULL              |
+-------------------+
```
若目标数据类型包含日期和时间数据，但数据缺少对应的信息，那么将根据如下规则自动补充数据：
- 若expr中缺失时间信息，转换后的时间默认为00:00:00。
  例如，将TIMESTAMP类型的数据2001-1-22转换为TIME类型，语句如下：
```
SELECT CAST(TIMESTAMP '2001-1-22' AS TIME);
```
  由于缺少时间信息，默认返回00:00:00，结果如下：
```
+-------------------------------------+
| CAST(TIMESTAMP '2001-1-22' AS TIME) |
+-------------------------------------+
| 00:00:00                            |
+-------------------------------------+
```
- 若expr中缺失日期信息，默认转换后的日期为执行查询时客户端的系统日期。
  例如，将TIME类型的数据00:00:00转换为DATE类型，语句如下：
```
SELECT CAST(TIME '00:00:00' AS DATE);
```
  由于缺少日期信息，默认执行查询时的系统日期，结果如下：
```
+-------------------------------+
| CAST(TIME '00:00:00' AS DATE) |
+-------------------------------+
| 2021-09-14                    |
+-------------------------------+
```

示例

将BIGINT类型的数据20010122000000转换成DATE类型，语句如下：

SELECT CAST(20010122000000 AS DATE);

返回结果如下：

+------------------------------+
| CAST(20010122000000 AS DATE) |
+------------------------------+
| 2001-01-22                   |
+------------------------------+

CAST AS VARBINARY

CAST (expr AS VARBINARY)

命令说明

将expr转换为VARBINARY类型。

输入值类型

BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
JSON

示例

将VARCHAR类型的数据CHINA转换为VARBINARY类型，语句如下：

SELECT CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR);

返回结果如下：

+--------------------------------------------------------+
| CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR)            |
+--------------------------------------------------------+
| CHINA                                                  |
+--------------------------------------------------------+

CAST AS VARCHAR

CAST (expr AS VARCHAR)

命令说明

将expr转换为VARCHAR类型。

输入值类型

BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARBINARY
说明
仅3.1.4及以上版本的AnalyticDB MySQL版集群，支持通过CAST函数将VARBINARY类型转换为VARCHAR类型。
MAP
JSON

示例

将TIMESTAMP类型的数据2001-1-22 00:00:00 转换为VARCHAR类型，语句如下：

SELECT CAST(TIMESTAMP '2001-1-22 00:00:00' AS VARCHAR);

返回结果如下：

+-------------------------------------------------+
| CAST(TIMESTAMP '2001-1-22 00:00:00' AS VARCHAR) |
+-------------------------------------------------+
| 2001-01-22 00:00:00                             |
+-------------------------------------------------+

CAST AS ARRAY

CAST (expr AS ARRAY<element_type>)

命令说明

将expr转换为ARRAY类型，其中ARRAY数据由指定数据类型<element_type>构成。

输入值类型

expr：VARCHAR或JSON类型。
<element_type>：TINYINT、SMALLINT、INT/INTEGER或FLOAT类型。

转换规则

如果VARCHAR数据或JSON数据不符合ARRAY格式，转换时会报错。

例如，将VARCHAR类型的数据{}}转换ARRAY数据，其中ARRAY数据由FLOAT类型构成，语句如下：

SELECT CAST('{}}' AS ARRAY<float>);

由于格式不匹配，会提示如下错误：

ERROR 1815 (HY000): [30013, 2021091815372119216818804803453204662] : Value cannot be cast to array(real)

示例

将JSON类型的数据[1,2,3]转换ARRAY数据，其中ARRAY数据由INT类型构成，语句如下：

SELECT CAST( JSON '[1,2,3]' AS ARRAY<int>);

返回结果如下：

+-------------------------------------+
| CAST( JSON '[1,2,3]' AS ARRAY<int> |
+-------------------------------------+
| [1,2,3]                             |
+-------------------------------------+

CAST AS MAP

CAST (expr AS MAP<element_type_1,element_type_2>)

命令说明

将expr转换为MAP数据，该MAP会将<element_type_1>类型的数据映射为<element_type_2>类型。

输入值类型

expr：VARCHAR类型。
<element_type_1>：BOOLEAN、DECIMAL、DOUBLE、FLOAT、BIGINT、INT/INTEGER、SMALLINT、TINYINT、VARCHAR类型。
<element_type_2>：BOOLEAN、DECIMAL、DOUBLE、FLOAT、BIGINT、INT/INTEGER、SMALLINT、TINYINT、VARCHAR、ARRAY、JSON、MAP类型。

转换规则

如果VARCHAR类型的数据不符合MAP格式，转换时会报错。

例如，将VARCHAR类型的数据[a,b,c]转换为MAP类型，语句如下：

SELECT CAST('[a,b,c]' AS MAP<varchar,varchar>);

此时会返回如下错误：

ERROR 1815 (HY000): [30013, 2021091815562519216818804803453207833] : Value cannot be cast to map(varchar,varchar)

示例

将VARCHAR类型的数据{"1":"a"}转换为MAP数据，该MAP会将一个VARCHAR类型数据映射为VARCHAR，语句如下：

SELECT CAST('{"1":"a"}' AS MAP<varchar,varchar>);

返回结果如下：

+-------------------------------------------+
| CAST('{"1":"a"}' AS MAP<varchar,varchar>) |
+-------------------------------------------+
| {"1":"a"}                                 |
+-------------------------------------------+

CAST AS JSON

CAST (expr AS JSON)

命令说明

将expr转换为JSON类型。

源数据类型

BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
VARCHAR
ARRAY

转换规则

如果VARCHAR类型的数据不符合JSON格式，转换时会返回NULL。

例如，将VARCHAR类型的数据{}}转换成JSON类型，语句如下：

SELECT CAST('{}}' AS JSON);

返回结果如下：

NULL

示例

将VARCHAR类型的数据{}转换为JSON类型，语句如下：

SELECT CAST('{}' AS JSON);

返回结果如下：

+--------------------+
| CAST('{}' AS JSON) |
+--------------------+
| {}                 |
+--------------------+

将BIGINT类型的数据0转换成JSON类型，语句如下：

SELECT CAST( BIGINT '0' AS JSON);

返回结果如下：

+---------------------------+
| CAST( BIGINT '0' AS JSON) |
+---------------------------+
| 0                         |
+---------------------------+