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

显式类型转换

显式类型转换是用CAST将一种数据类型的值转换为另一种类型的值的行为,在MaxCompute SQL中支持的显式类型转换,如下表所示。
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 表示不可以转换,– 表示不需要转换。

示例如下:
select cast(user_id as DOUBLE) as new_id from user;
select cast('2015-10-01 00:00:00' as datetime) as new_date from user;
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,详情请参见STRING类型与DATETIME类型之间的转换
  • 部分类型之间不可以通过显式地类型转换,但可以通过SQL内建函数进行转换,例如从BOOLEAN类型转换到STRING类型,可使用函数to_char,详情请参见TO_CHAR ,而to_date函数同样支持从STRING类型到DATETIME类型的转换,详情请参见TO_DATE
  • 关于CAST的介绍请参见其他函数
  • DECIMAL超出值域,CAST STRING TO 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 DOUBLE DECIMAL STRING VARCHAR TIMESTAMP BINARY
BOOLEAN to Y N N N N N N N N N N N
TINYINT to N Y Y Y Y Y Y Y Y Y N N
SMALLINT to N N Y Y Y Y Y Y Y Y N N
INT to N N Y Y Y Y Y Y Y Y N
BIGINT to N N N N Y Y Y Y Y Y N N
FLOAT to N N N N Y Y Y Y Y Y N
DOUBLE to N N N N N N Y Y Y Y N N
DECIMAL to N N N N N N N Y Y Y N N
STRING to N N N N N N Y Y Y Y N N
VARCHAR to N N N N Y Y Y Y N N
TIMESTAMP to N N N N N N N N Y Y Y N
BINARY to N N N N N N N N N N N Y

其中,Y表示可以转换,N表示不可以转换,– 表示不需要转换。

说明
  • MaxCompute2.0新增了DECIMAL类型与DATETIME的常量定义方式,100BD就是数值为100的DECIMAL,DATETIME2017-11-11 00:00:00就是DATETIME类型的常量。常量定义的方便之处在于可以直接用到values子句和values表中。
  • 旧版MaxCompute中,因为历史原因,DOUBLE可以隐式的转换为BIGINT,这个转换潜在有数据丢失风险,一般数据库系统都不允许,建议您谨慎操作。
常见用法如下所示:
select user_id+age+'12345',
           concat(user_name,user_id,age)
  from user;
说明
  • 不支持的隐式类型转换会导致异常。
  • 如果在执行时转换失败,也会导致异常。
  • 由于隐式类型转换是MaxCompute依据上下文使用环境自动进行的类型转换,因此推荐您在类型不匹配时,显式的用CAST进行转换。
  • 隐式类型转换规则是有发生作用域的。在某些作用域中,只有一部分规则可以生效。详情请参见隐式类型转换的作用域。
  • 关系运算符作用下的隐式转换:

    关系运算符包括=、<>、<、<=、>、>=、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的隐式转换规则:
      • 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的格式。
类似于下面的转换尝试,将会失败导致异常,如下所示:
cast("2013/12/31 02/34/34" as datetime)  
cast("20131231023434" as datetime)  
cast("2013-12-31 2:34:34" as datetime)
部分的阈值上限取决于月份实际拥有的天数,如果超出对应月份实际拥有的天数,将会导致异常退出,如下所示:
cast("2013-02-29 12:12:12" as datetime)      -- 异常返回,2013年2月没有29日  
cast("2013-11-31 12:12:12" as datetime)      -- 异常返回,2013年11月没有31日

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