AnalyticDB MySQL版 支持通过 cast 函数来转换数据类型。本文介绍如何使用 cast 函数。

语法 

cast(expr AS type)
其中：
  • expr ：源数据，如字符串 'China' 。
  • type ：目标数据类型，例如 varbinary 。

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。
    例如，将VARCHAR类型的数据 a 转换为BOOLEAN类型，语句如下： 
    SELECT cast('a' AS boolean);
    此时会返回： 
    NULL
  • 源DOUBLE类型的数据转换为BOOLEAN类型时，会出现如下几种情况：
    • 非0.0数值会被转换成1。
    • 0.0会被转换成0。
    例如，将源DOUBLE的数据类型 4.3 转换为BOOLEAN类型，语句如下： 
    SELECT cast(4.3 AS boolean);
    返回结果如下： 
    +----------------------+
| cast(4.3 AS boolean) |
+----------------------+
|                    1 |
+----------------------+
  • 源DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT或BIGINT类型的数据，转换为BOOLEAN类型时，会出现如下几种情况：
    • 非0数值会被转换成1。
    • 0会被转换成0。
    例如，将源INT的数据类型 5 转换为BOOLEAN类型，语句如下： 
    SELECT cast(5 AS boolean);
    返回结果如下： 
    +--------------------+
| cast(5 AS boolean) |
+--------------------+
|                  1 |
+--------------------+

示例

例如，将INT类型的数据 1 转换为BOOLEAN，语句如下： 
SELECT cast('1' AS boolean);
返回结果如下： 
+----------------------+
| cast('1' 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

注意事项

  • 若源类型数值超出了目标类型支持的数值范围，类型转换时会报错。
    例如，将BIGINT类型的数据 99999999 转换成SMALLINT类型，语句如下： 
    SELECT cast(99999999 as smallint);
    由于 99999999 不在SMALLINT支持的数值范围内，此时会返回NULL。 
    NULL
  • 在如下类型转换的场景中，转换后数据精度会丢失。
    • DOUBLE类型转换为FLOAT类型。
      将源DOUBLE类型的数据 1.23456789 转换为FLOAT类型，语句如下： 
      SELECT cast(1.23456789 AS float);
      返回结果如下： 
      +---------------------------+
| cast(1.23456789 AS float) |
+---------------------------+
|                 1.2345679 |
+---------------------------+
    • DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT或TINYINT类型间互相转换。
      例如，将源FLOAT类型的数据 1.1342 转换为INT类型，语句如下： 
      SELECT cast(1.1342 AS int);
      返回结果如下： 
      +---------------------+
| cast(1.1342 AS int) |
+---------------------+
|                   1 |
+---------------------+
  • 若源VARCHAR类型的数据不是数值，转换为DECIMAL(m,d)或FLOAT类型时会返回NULL。
    例如，将源VARCHAR类型的数据 China 转换为DECIMAL(m,d)类型，语句如下： 
    SELECT cast( 'China' AS decimal(5,2));
    返回结果如下： 
    NULL
  • 若源VARCHAR类型的数据不是数值，转换为INT/INTEGER、SMALLINT或TINYINT类型后的结果均为0。
    例如，将源VARCHAR类型的数据 China 转换为SMALLINT类型，语句如下： 
    SELECT cast( 'China' AS smallint);
    返回结果如下： 
    +----------------------------+
| cast( 'China' AS smallint) |
+----------------------------+
|                          0 |
+----------------------------+
  • 若源JSON类型的数据不是数值，转换为DECIMAL(m,d)、FLOAT、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 |
+---------------------+
  • 在如下类型转换的场景中，转换后数据精度会丢失。
    • DOUBLE类型转换为BIGINT类型。
      将源DOUBLE类型的数据 1.23456789 转换为BIGINT类型，语句如下： 
      SELECT cast(1.23456789 AS bigint);
      返回结果如下： 
      +----------------------------+
| cast(1.23456789 AS bigint) |
+----------------------------+
|                          1 |
+----------------------------+
    • DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT或TINYINT类型的数据转换为BIGINT类型。
      例如，将DECIMAL类型的数据 1.1 转换为BIGINT类型，语句如下： 
      SELECT cast(1.1 AS bigint);
      返回结果如下： 
      +---------------------+
| cast(1.1 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              |
+-------------------+
  • 若目标数据类型包含日期和时间数据，但源数据缺少对应的信息，那么将根据如下规则自动补充数据：
    • 若源数据中缺失时间信息，转换后的时间默认为 00:00:00 。
      例如，将TIMSTAMP类型的数据 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                            |
+-------------------------------------+
    • 若源数据中缺失日期信息，默认转换后的日期为执行该查询时该客户端的系统日期。
      例如，将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('CHINA' AS varbinary);
返回结果如下： 
+--------------------------------------------------------+
| cast('CHINA' AS varbinary)                             |
+--------------------------------------------------------+
| 0x4348494E41                                           |
+--------------------------------------------------------+

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类型。
  • ARRAY
  • 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                         |
+---------------------------+