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
  • 示例
    例如,将INT类型的数据1转换为BOOLEAN,语句如下:
    SELECT cast('1' AS boolean);
    返回结果如下:
    +----------------------+
    | cast('1' AS boolean) |
    +----------------------+
    |                    1 |
    +----------------------+
  • 注意事项
    • 源VARCHAR类型或JSON类型的数据转换为BOOLEAN类型时,会出现如下几种情况:
      • 源数据为true或1,会被转换成1。
      • 源数据为false或0,会被转换成0。
      • 源数据为其他,转换时会报错。
      例如,将VARCHAR类型的数据a转换为BOOLEAN类型,语句如下:
      SELECT cast('a' AS boolean);
      此时会返回如下错误:
      ERROR 1815 (HY000): [30013, 2021091710344119216818804803453912763] : Cannot cast 'a' to BOOLEAN
    • 源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 |
      +--------------------+

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类型的数据2001012转换为FLOAT类型,语句如下:
    SELECT cast( 2001012 AS float);
    返回结果如下:
    +-------------------------+
    | cast( 2001012 AS float) |
    +-------------------------+
    |               2001012.0 |
    +-------------------------+
  • 注意事项
    • 若源类型数值超出了目标类型支持的数值范围,类型转换时会报错。
      例如,将BIGINT类型的数据99999999转换成SMALLINT类型,语句如下:
      SELECT cast(99999999 as smallint);
      由于99999999不在SMALLINT支持的数值范围内,此时会返回如下错误:
      ERROR 1815 (HY000): [31002, 2021091417365619216818804803453260208] : Out of range for smallint: 99999999
    • 在如下类型转换的场景中,转换后数据精度会丢失:
      • 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类型时会报错。
      例如,将源VARCHAR类型的数据China转换为DECIMAL(m,d)类型,语句如下:
      SELECT cast( 'China' AS decimal(5,2));
      此时会返回如下错误:
      ERROR 1815 (HY000): [30013, 2021091715273019216818804803453961857] : Cannot cast VARCHAR 'China' to DECIMAL(5, 2). Value is not a number.
    • 若源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

CAST AS BIGINT

cast (expr AS bigint)
  • 命令说明:将源数据expr转换为BIGINT类型。
  • 源数据类型
    • BOOLEAN
    • DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
    • DOUBLE
    • DATE、DATETIME、TIMESTAMP、TIME
    • VARCHAR
  • 示例
    • 将源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 |
      +---------------------------+
  • 注意事项
    • 若源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类型时会报错。
      例如,将源JSON类型的数据{}转换为BIGINT类型,语句如下:
      SELECT cast(json'{}'AS bigint);
      此时会返回如下报错:
      ERROR 1815 (HY000): [30013, 2021091815393219216818804803453205032] : Cannot cast '{}' to bigint

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
  • 示例
    将DATE类型的数据2021-09-17转换为DOUBLE类型,语句如下:
    SELECT cast( date '2021-09-17' AS double);
    返回结果如下:
    +------------------------------------+
    | cast( date '2021-09-17' AS double) |
    +------------------------------------+
    |                        2.0210917E7 |
    +------------------------------------+
  • 注意事项
    • 若源VARCHAR类型的数据不是数值,转换为DOUBLE类型后的结果均为0.0。
      例如,将源VARCHAR类型的数据China转换为DOUBLE类型,语句如下:
      SELECT cast( 'China' AS double);
      返回结果如下:
      +--------------------------+
      | cast( 'China' AS double) |
      +--------------------------+
      |                      0.0 |
      +--------------------------+
    • 若源JSON类型的数据不是数值,转换为DOUBLE时会报错。
      例如,将源JSON类型的数据{}转换为DOUBLE类型,语句如下:
      SELECT cast(json '{}' AS double);
      此时会返回如下错误:
      ERROR 1815 (HY000): [30013, 2021091816172219216818804803453211359] : Cannot cast '{}' to double

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
  • 示例
    例如,将BIGINT类型的数据20010122000000转换成DATE类型,语句如下:
    SELECT cast(20010122000000 AS date);
    返回结果如下:
    +------------------------------+
    | cast(20010122000000 AS date) |
    +------------------------------+
    | 2001-01-22                   |
    +------------------------------+
  • 注意事项
    • 若源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                    |
        +-------------------------------+

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类型。
  • 示例
    将源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]                             |
    +-------------------------------------+
  • 注意事项:若源VARCHAR数据或JSON数据不符合ARRAY格式,转换时会报错。
    例如,将源VARCHAR类型的数据{}}转换ARRAY数据,其中ARRAY数据由FLOAT类型构成,语句如下:
    SELECT cast('{}}' AS array<float>);
    由于格式不匹配,会提示如下错误:
    ERROR 1815 (HY000): [30013, 2021091815372119216818804803453204662] : Value cannot be cast to array(real)

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、BITGINT、INT/INTEGER、SMALLINT、TINYINT、VARCHAR类型。
    • <element_type_2>:BOOLEAN、DECIMAL、DOUBLE、FLOAT、BITGINT、INT/INTEGER、SMALLINT、TINYINT、VARCHAR、ARRAY、JSON、MAP类型。
  • 示例
    将源VRACHAR类型的数据{"1":"a"}转换为MAP数据,该MAP会将一个VARCHAR数据映射为VARCHAR类型,语句如下:
    SELECT cast('{"1":"a"}' AS map<varchar,varchar>);
    返回结果如下:
    +-------------------------------------------+
    | cast('{"1":"a"}' AS map<varchar,varchar>) |
    +-------------------------------------------+
    | {"1":"a"}                                 |
    +-------------------------------------------+
  • 注意事项:若源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)

CAST AS JSON

cast (expr AS json)
  • 命令说明:将源数据expr转换为JSON类型。
  • 源数据类型
    • BOOLEAN
    • DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
    • BIGINT
    • DOUBLE
    • VARCHAR
    • ARRAY
  • 示例
    • 将VARCHAR类型的数据{}转换为JSON类型,语句如下:
      SELECT cast('{}' AS json);
      返回结果如下:
      +--------------------+
      | cast('{}' AS json) |
      +--------------------+
      | {}                 |
      +--------------------+
    • 将BIGINT类型的数据0转换成JSON类型,语句如下:
      SELECT cast( bigint '0' AS json);
      返回结果如下:
      +---------------------------+
      | cast( bigint '0' AS json) |
      +---------------------------+
      | 0                         |
      +---------------------------+
  • 注意事项:若源VARCHAR类型的数据不符合JSON格式,转换时会报错。
    例如,将VARCHAR类型的数据{}}转换成JSON类型,语句如下:
    SELECT cast('{}}' AS json);
    此时会提示如下错误:
    ERROR 1815 (HY000): [30014, 2021091417335219216818804803453259705] : Cannot convert '{}}' to JSON