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

显式类型转换

显式类型转换是用CAST将一种数据类型的值转换为另一种类型的值的行为,在MaxCompute SQL中支持的显式类型转换,如下表所示:
From/To Bigint Double String Datetime Boolean Decimal
Bigint Y Y N N Y
Double Y Y N N Y
String Y Y Y N Y
Datetime N N Y N N
Boolean N N N N N
Decimal Y Y Y N N -

其中,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;
说明
  • 将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的介绍请参见CAST
  • DECIMAL超出值域,CAST STRING TO DECIMAL可能会出现最高位溢出报错,最低位溢出截断等情况。

隐式类型转换及其作用域

隐式类型转换是指在运行时,由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。MaxCompute支持的隐式类型转换规则,如下表所示:
boolean tinyint smallint int bigint float string varchar timestamp binary
boolean to Y N N N N N N N N N
tinyint to N Y Y Y Y Y Y Y N N
smallint to N N Y Y Y Y Y Y N N
int to N N Y Y Y Y Y Y N
bigint to N N N N Y Y Y Y N N
float to N N N N Y Y Y Y N
double to N N N N N N Y Y N N
decimal to N N N N N N Y Y N N
string tof N N N N N N Y Y N N
varchar to N N N N Y Y N N
timestamp to N N N N N N Y Y Y N
binary to N N N N N N N N N Y

其中,T表示可以转换,F表示不可以转换。

说明
  • 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)
dd部分的阈值上限取决于月份实际拥有的天数,如果超出对应月份实际拥有的天数,将会导致异常退出,如下所示:
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