sql数据类型映射与数据类型转换

–odps sql
––
–author:宋文理
–create time:2023-03-09 09:36:05
––
– MaxCompute与Hive、Oracle、MySQL的数据类型映射表，如下所示。

MaxCompute数据类型 Hive数据类型 Oracle数据类型 MySQL数据类型
BOOLEAN BOOLEAN 无(说明一) 无(说明二)
TINYINT TINYINT NUMBER(3,0) TINYINT
SMALLINT SMALLINT NUMBER(5,0) SMALLINT
INT INT NUMBER(7,0) MEDIUMINT
INT INT NUMBER(10,0) INT
BIGINT BIGINT NUMBER(20,0) BIGINT
FLOAT FLOAT BINARY_FLOAT FLOAT
DOUBLE DOUBLE BINARY_DOUBLE DOUBLE
DECIMAL DECIMAL NUMBER(P,S) DECIMAL或NUMERIC
STRING STRING VARCHAR或VARCHAR2或CHAR或NCHAR或NVARCHAR3 VARCHAR或CHAR
VARCHAR VARCHAR VARCHAR或VARCHAR2或CHAR或NCHAR或NVARCHAR3 VARCHAR
STRING CHAR CHAR CHAR
BINARY BINARY RAW BINARY或VARBINARY
TIMESTAMP TIMESTAMP TIMESTAMP(N) TIMESTAMP
DATETIME DATE DATE DATETIME
ARRAY ARRAY 不支持 不支持
MAP MAP<key,value> 不支持 不支持
STRUCT STRUCT 不支持 不支持
不支持 UNION 不支持 不支持
说明一 使用过程中用CHAR(1)、INTEGER或NUMBER(1)等进行替代，1为真，0为假。
说明二 使用过程用TINYINT(1)替代。

– 数据类型转换如下
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表示不可以转换，不涉及表示不需要转换。不支持的显式类型转换会失败并报错退出。

示例
SELECT CAST(user_id AS DOUBLE) AS new_id;
SELECT CAST(‘2015-10-01 00:00:00’ AS DATETIME) AS new_date;
SELECT CAST(ARRAY(1,2,3) AS ARRAY);
SELECT CONCAT_WS(‘,’, CAST(ARRAY(1, 2) AS ARRAY));
使用说明和限制
将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能隐式转换或显示转换为ARRAY。
ARRAY能显示转换为ARRAY，但是不能隐式转换。
ARRAY不能隐式转换或显示转换为ARRAY。
STRUCT<a:BIGINT,b:INT>能隐式转换为STRUCTcol1:STRING,col2:BIGINT，但是不能隐式或显示转换为STRUCT<a:STRING>。
隐式类型转换及其作用域
隐式类型转换是指在运行时，由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。
MaxCompute支持的隐式类型转换规则，如下表所示。
From/To BOOLEAN TINYINT SMALLINT INT BIGINT FLOAT
BOOLEAN Y N N N N N
TINYINT N Y Y Y Y Y
SMALLINT N N Y Y Y Y
INT N N Y Y Y Y
BIGINT N N N N Y Y
FLOAT N N N N Y Y

From/To DOUBLE DECIMAL STRING VARCHAR TIMESTAMP BINARY
DOUBLE Y Y Y Y N N
DECIMAL N Y Y Y N N
STRING Y Y Y Y N N
VARCHAR Y Y N N 不涉及 不涉及
TIMESTAMP N N Y Y Y N
BINARY N N N N N Y
其中，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 00019999
月 mm 0112
日 dd 0128|29|30|31
时 hh 0023
分 mi 0059
秒 ss 0059
说明
各个单位的值域中，如果首位为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格式。