MaxCompute用户指南：SQL：类型转换

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;
3. 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 进行转换。
• 
  隐式类型转换规则是有发生作用域的。在某些作用域中，只有一部分规则可以生效。详情请参见隐式类型转换的作用域。

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


关系运算符包括：=，<>，<，<=，>，>=，ISNULL，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-3102: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)      -- 异常返回，2013年2月没有29日  
3.     cast("2013-11-31 12:12:12" as datetime)      -- 异常返回，2013年11月没有31日

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