CAST函数

CAST函数

更新时间:

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