全部產品
Search
文件中心

MaxCompute:資料類型轉換

更新時間:Jun 19, 2024

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表示不可以轉換,不涉及表示不需要轉換。不支援的顯式類型轉換會失敗並報錯退出。

  • 樣本資料:

    說明

    本文中使用樣本涉及user表的樣本,都可使用此樣本資料。

    create table if not exists user (
      user_name string,
    	user_id bigint,
    	age bigint   
    );
    insert into user values ('zhangsan',111,20);
  • 使用樣本:

    SELECT CAST(user_id AS DOUBLE) AS new_id from user;
    SELECT CAST('2015-10-01 00:00:00' AS DATETIME) AS new_date;
    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

  • 部分類型之間不可以通過顯式的類型轉換,但可以通過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<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

BOOLEAN

不涉及

N

N

N

N

N

TINYINT

N

不涉及

Y

Y

Y

Y

SMALLINT

N

N

不涉及

Y

Y

Y

INT

N

N

Y

不涉及

Y

Y

BIGINT

N

N

N

N

不涉及

Y

FLOAT

N

N

N

N

Y

不涉及

From/To

DOUBLE

DECIMAL

STRING

VARCHAR

TIMESTAMP

BINARY

DOUBLE

不涉及

Y

Y

Y

N

N

DECIMAL

N

不涉及

Y

Y

N

N

STRING

Y

Y

不涉及

Y

N

N

VARCHAR

Y

Y

Y

不涉及

N

N

TIMESTAMP

N

N

Y

Y

不涉及

N

BINARY

N

N

N

N

N

不涉及

其中,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

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格式。

MaxCompute提供了TO_DATE函數,用於將不滿足日期格式的STRING類型資料轉換為DATETIME類型。詳情請參見TO_DATE