数据类型转换

MaxCompute SQL允许数据类型之间的转换,类型转换方式包括显式类型转换和隐式类型转换。

显式类型转换

显式类型转换是通过CAST函数将一种数据类型的值转换为另一种类型的值,在MaxCompute SQL中支持的显式类型转换,如下表所示。 关于CAST的介绍请参见CAST

From/To

BIGINT

DOUBLE

STRING

DATETIME

BOOLEAN

DECIMAL

FLOAT

BIGINT

不涉及

Y

Y

N

Y

Y

Y

DOUBLE

Y

不涉及

Y

N

Y

Y

Y

STRING

Y

Y

不涉及

Y

Y

Y

Y

DATETIME

N

N

Y

不涉及

N

N

N

BOOLEAN

Y

Y

Y

N

不涉及

Y

Y

DECIMAL

Y

Y

Y

N

Y

不涉及

Y

FLOAT

Y

Y

Y

N

Y

Y

不涉及

其中,Y表示可以转换,N表示不可以转换,不涉及表示不需要转换。不支持的显式类型转换会失败并报错退出。

  • 示例数据:

    说明

    本文中使用示例涉及user表的示例,都可使用此示例数据。

    create table if not exists user (
      user_name string,
    	user_id bigint,
    	age bigint   
    );
    insert into user values ('zhangsan',111,20);
  • 使用示例:

    SELECT CAST(user_id AS DOUBLE) AS new_id from user;
    SELECT CAST('2015-10-01 00:00:00' AS DATETIME) AS new_date;
    SELECT CAST(ARRAY(1,2,3) AS ARRAY<STRING>);
    SELECT CONCAT_WS(',', CAST(ARRAY(1, 2) AS ARRAY<STRING>));

使用说明和限制

  • 将DOUBLE类型转为BIGINT类型时,小数部分会被截断,例如CAST(1.6 AS BIGINT) = 1

  • 满足DOUBLE格式的STRING类型转换为BIGINT时,会先将STRING转换为DOUBLE,再将DOUBLE转换为BIGINT,因此,小数部分会被截断,例如CAST(“1.6” AS BIGINT) = 1

  • 满足BIGINT格式的STRING类型可以被转换为DOUBLE类型,小数点后保留一位,例如CAST(“1” AS DOUBLE) = 1.0

  • 日期类型转换时采用默认格式yyyy-mm-dd hh:mi:ss

  • 部分类型之间不可以通过显式的类型转换,但可以通过SQL内建函数进行转换,例如从BOOLEAN类型转换到STRING类型,可使用函数TO_CHAR,详情请参见TO_CHAR。而TO_DATE函数同样支持从STRING类型到DATETIME类型的转换,详情请参见TO_DATE

  • DECIMAL超出值域,CAST STRING TO DECIMAL可能会出现最高位溢出报错、最低位溢出截断等情况。

  • DECIMAL类型显示转换为DOUBLE、FLOAT等类型会产生精度损失,对于精度有要求的场景,例如计算金额、费率等,建议使用DECIMAL类型。

  • MaxCompute支持复杂类型的类型转换功能。其中复杂类型的隐式类型转换要求子类型能够隐式转换,而显示转换要求子类型能够显示转换。STRUCT类型转换不要求字段名称一致,但是要求字段的数量一致,且对应的字段能够隐式或显示转换。例如:

    • ARRAY<BIGINT>能隐式转换或显示转换为ARRAY<STRING>

    • ARRAY<BIGINT>能显示转换为ARRAY<INT>,但是不能隐式转换。

    • ARRAY<BIGINT>不能隐式转换或显示转换为ARRAY<DATETIME>

    • STRUCT<a:BIGINT,b:INT>能隐式转换为STRUCT<col1:STRING,col2:BIGINT>,但是不能隐式或显示转换为STRUCT<a:STRING>

隐式类型转换及其作用域

隐式类型转换是指在运行时,由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。MaxCompute支持的隐式类型转换规则,如下表所示。

From/To

BOOLEAN

TINYINT

SMALLINT

INT

BIGINT

FLOAT

BOOLEAN

不涉及

N

N

N

N

N

TINYINT

N

不涉及

Y

Y

Y

Y

SMALLINT

N

N

不涉及

Y

Y

Y

INT

N

N

Y

不涉及

Y

Y

BIGINT

N

N

N

N

不涉及

Y

FLOAT

N

N

N

N

Y

不涉及

From/To

DOUBLE

DECIMAL

STRING

VARCHAR

TIMESTAMP

BINARY

DOUBLE

不涉及

Y

Y

Y

N

N

DECIMAL

N

不涉及

Y

Y

N

N

STRING

Y

Y

不涉及

Y

N

N

VARCHAR

Y

Y

Y

不涉及

N

N

TIMESTAMP

N

N

Y

Y

不涉及

N

BINARY

N

N

N

N

N

不涉及

其中,Y表示可以转换,N表示不可以转换,不涉及表示不需要转换。不支持的隐式类型转换会导致异常。如果在执行时转换失败,也会导致异常。

说明
  • MaxCompute 2.0新增了DECIMAL类型与DATETIME的常量定义方式,100BD是数值为100的DECIMAL,2017-11-11 00:00:00是DATETIME类型的常量。VALUES子句和VALUES表中可以直接使用常量定义。

  • 由于隐式类型转换是MaxCompute依据上下文使用环境自动进行的类型转换,因此推荐您在类型不匹配时,显式地用CAST进行转换。

  • 隐式类型转换规则是有发生作用域的。在某些作用域中,只有一部分规则可以生效。

示例

SELECT user_id+age+'12345', CONCAT(user_name,user_id,age) FROM user;

不同运算符作用下的隐式转换如下:

  • 关系运算符作用下的隐式转换

    关系运算符包括=、<>、<、<=、>、>=、IS NULL、IS NOT NULL、LIKE、RLIKE、IN。由于LIKE、RLIKE、IN的隐式类型转换规则不同于其他关系运算符,将单独对其进行说明。此处的说明不包含这三种特殊的关系运算符。

    当不同类型的数据共同参与关系运算时,按照下述原则进行隐式类型转换。

    From/To

    BIGINT

    DOUBLE

    STRING

    DATETIME

    BOOLEAN

    DECIMAL

    BIGINT

    不涉及

    DOUBLE

    DOUBLE

    N

    N

    DECIMAL

    DOUBLE

    DOUBLE

    不涉及

    DOUBLE

    N

    N

    DECIMAL

    STRING

    DOUBLE

    DOUBLE

    不涉及

    DATETIME

    N

    DECIMAL

    DATETIME

    N

    N

    DATETIME

    不涉及

    N

    N

    BOOLEAN

    N

    N

    N

    N

    不涉及

    N

    DECIMAL

    DECIMAL

    DECIMAL

    DECIMAL

    N

    N

    不涉及

    说明
    • 如果进行比较的两个类型间不能进行隐式类型转换,则该关系运算不能完成,报错退出。

    • 关系运算符的更多详情,请参见运算符

  • 特殊的关系运算符作用下的隐式转换

    特殊的关系运算符包括LIKE、RLIKE、IN

    • LIKE和RLIKE的使用方式,如下所示。

      source LIKE pattern;  
      source RLIKE pattern;
      说明
      • LIKE和RLIKE的source和pattern参数均仅接受STRING类型。

      • 其他类型不允许参与运算,也不能进行到STRING类型的隐式类型转换。

    • IN的使用方式如下。

      key IN (value1, value2, …)
      说明
      • IN右侧的VALUE值列表中的数据类型必须保持一致。

      • 当KEY与VALUES之间比较时,如果数据类型包含BIGINT、DOUBLE、STRING,建议统一转换为DOUBLE类型;如果数据类型包含DATETIME、STRING,建议统一转换为DATETIME类型。除此之外不允许其他类型之间的转换。

  • 算术运算符作用下的隐式转换

    算术运算符包括+、-、*、/、%,其隐式转换规则如下:

    • 只有STRING、BIGINT、DOUBLE和DECIMAL才能参与算术运算。

    • STRING在参与运算前会进行隐式类型转换到DOUBLE。

    • BIGINT和DOUBLE共同参与计算时,会将BIGINT隐式转换为DOUBLE。

    • 日期型和布尔型不允许参与算数运算。

  • 逻辑运算符作用下的隐式转换

    逻辑运算符包括and、or、not,其隐式转换规则,如下:

    • 只有BOOLEAN才能参与逻辑运算。

    • 其他类型不允许参与逻辑运算,也不允许其他类型的隐式类型转换。

内建函数涉及到的隐式转换

MaxCompute SQL提供了大量的系统函数,方便您对任意行的一列或多列进行计算,输出任意种类的数据类型。其隐式转换规则如下:

  • 在调用函数时,如果输入参数的数据类型与函数定义的参数数据类型不一致,把输入参数的数据类型转换为函数定义的数据类型。

  • 每个MaxCompute SQL内建函数的参数对于允许的隐式类型转换的要求不同,详情请参见内建函数概述

CASE WHEN作用下的隐式转换

CASE WHEN的详情介绍请参见CASE WHEN表达式。它的隐式转换规则,如下:

  • 如果返回类型只有BIGINT、DOUBLE,统一转换为DOUBLE。

  • 如果返回类型中有STRING类型,统一转换为STRING,如果不能转换(如BOOLEAN类型)则报错。

  • 除此之外不允许其他类型之间的转换。

STRING与DATETIME类型之间的转换

MaxCompute支持STRING类型和DATETIME类型之间的相互转换。转换时使用的格式为yyyy-mm-dd hh:mi:ss

单位

字符串(忽略大小写)

有效值域

yyyy

0001~9999

mm

01~12

dd

01~28|29|30|31

hh

00~23

mi

00~59

ss

00~59

说明
  • 各个单位的值域中,如果首位为0,不可省略。例如2014-1-9 12:12:12为非法的DATETIME格式,无法从STRING类型数据转换为DATETIME类型,必须写为2014-01-09 12:12:12

  • 只有符合上述格式描述的STRING类型才能够转换为DATETIME类型,例如CAST(“2013-12-31 02:34:34” AS DATETIME),将会把STRING类型2013-12-31 02:34:34 转换为DATETIME类型。同理,DATETIME转换为STRING时,默认转换为yyyy-mm-dd hh:mi:ss格式。

MaxCompute提供了TO_DATE函数,用于将不满足日期格式的STRING类型数据转换为DATETIME类型。详情请参见TO_DATE