全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
MaxCompute

类型转换

更新时间:2017-09-30 19:05:56

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 表示不可以转换, 表示不需要转换。

示例如下:

  1. select cast(user_id as double) as new_id from user;
  2. 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 double decimal string varchar timestamp binary
boolean to T F F F F F F F F F F F
tinyint to F T T T T T T T T T F F
smallint to F F T T T T T T T T F F
int to F F F T T T T T T T F F
bigint to F F F F T T T T T T F F
float to F F F F F T T T T T F F
double to F F F F F F T T T T F F
decimal to F F F F F F F T T T F F
string to F F F F F F T T T T F F
varchar to F F F F F F T T T T F F
timestamp to F F F F F F F F T T T F
binary to F F F F F F F F F F F T

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

注意:

  • MaxCompute2.0 新增了 DECIMAL 类型与 Datetime 的常量定义方式,100BD 就是数值为 100 的 DECIMAL,Datetime ‘2017-11-11 00:00:00’就是 Datetime 类型的常量。常量定义的方便之处在于可以直接用到 values 子句和 values 表中。

  • 旧版 MaxCompute 中,因为历史原因,Double 可以隐式的转换为 Bigint,这个转换潜在可能有数据丢失,一般数据库系统都不允许。

常见用法如下所示:

  1. select user_id+age+'12345',
  2. concat(user_name,user_id,age)
  3. 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 的使用方式,如下所示:

  1. source like pattern;
  2. source rlike pattern;

两者在隐式类型转换中的注意事项,如下所示:

  • LIKE 和 RLIKE 的 source 和 pattern 参数均仅接受 String 类型。

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

IN 的使用方式,如下所示:

  1. 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 的格式。

类似于下面的转换尝试,将会失败导致异常,如下所示:

  1. cast("2013/12/31 02/34/34" as datetime)
  2. cast("20131231023434" as datetime)
  3. cast("2013-12-31 2:34:34" as datetime)

“dd”部分的阈值上限取决于月份实际拥有的天数,如果超出对应月份实际拥有的天数,将会导致异常退出,如下所示:

  1. cast("2013-02-29 12:12:12" as datetime) -- 异常返回,20132月没有29
  2. cast("2013-11-31 12:12:12" as datetime) -- 异常返回,201311月没有31

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

本文导读目录