AnalyticDB MySQL版支援cast
函數。本文介紹如何使用cast
函數將一種資料類型轉換為另一種資料類型。
文法
CAST (expr AS type)
參數說明:
expr
:運算式,必填。type
:目標資料類型,必填。
CAST AS BOOLEAN
CAST (expr AS BOOLEAN)
命令說明
將expr
轉換為BOOLEAN類型。
輸入實值型別
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
VARCHAR
JSON
轉換規則
原資料類型 | 目標資料類型 | 轉換規則 |
| BOOLEAN |
|
DOUBLE | BOOLEAN |
|
| BOOLEAN |
|
樣本
樣本1
將INT類型的資料1
轉換為BOOLEAN,語句如下:
SELECT CAST('1' AS BOOLEAN);
返回結果如下:
+----------------------+
| CAST('1' AS BOOLEAN) |
+----------------------+
| 1 |
+----------------------+
樣本2
將VARCHAR類型的資料a
轉換為BOOLEAN類型,語句如下:
SELECT CAST('a' AS BOOLEAN);
返回結果如下:
NULL
樣本3
將DOUBLE的資料類型4.3
轉換為BOOLEAN類型,語句如下:
SELECT CAST(4.3 AS BOOLEAN);
返回結果如下:
+----------------------+
| CAST(4.3 AS BOOLEAN) |
+----------------------+
| 1 |
+----------------------+
樣本4
將INT的資料類型5
轉換為BOOLEAN類型,語句如下:
SELECT CAST(5 AS BOOLEAN);
返回結果如下:
+--------------------+
| CAST(5 AS BOOLEAN) |
+--------------------+
| 1 |
+--------------------+
CAST AS DECIMAL(m,d)|FLOAT|INT|INTEGER|SMALLINT|TINYINT
CAST (expr AS [DECIMAL(m,d)|FLOAT|INT|INTEGER|SMALLINT|TINYINT])
命令說明
將expr
轉換為DECIMAL(m,d)、FLOAT、INT、INTEGER、SMALLINT或TINYINT類型。
輸入實值型別
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
VARCHAR
JSON
轉換規則
運算式
expr
的數值超過目標資料類型的數值範圍時,類型轉換會返回null
。例如,將BIGINT類型的資料
99999999
轉換成SMALLINT類型,語句如下:SELECT CAST(99999999 as SMALLINT);
由於
99999999
不在SMALLINT支援的數值範圍內,此時會返回NULL。NULL
將高精度資料類型轉換為低精度的資料類型,會丟失精度。
例如,將DOUBLE類型的資料
1.23456789
轉換為FLOAT類型,語句如下:SELECT CAST(1.23456789 AS FLOAT);
返回結果如下:
+---------------------------+ | CAST(1.23456789 AS FLOAT) | +---------------------------+ | 1.2345679 | +---------------------------+
將定點數(DECIMAL(m,d))、浮點數(DOUBLE、FLOAT)轉換為整數(INT/INTEGER、SMALLINT或TINYINT),會丟失精度。
例如,將FLOAT類型的資料
1.1342
轉換為INT類型,語句如下:SELECT CAST(1.1342 AS INT);
返回結果如下:
+---------------------+ | CAST(1.1342 AS INT) | +---------------------+ | 1 | +---------------------+
如果運算式
expr
是VARCHAR類型,且不是數值,轉換為DECIMAL(m,d)或FLOAT類型時會返回NULL。例如,將VARCHAR類型的資料
China
轉換為DECIMAL(m,d)類型,語句如下:SELECT cast( 'China' AS decimal(5,2));
返回結果如下:
NULL
如果運算式
expr
是VARCHAR類型,且不是數值,轉換為INT/INTEGER、SMALLINT或TINYINT類型,返回結果為0。例如,將VARCHAR類型的資料
China
轉換為SMALLINT類型,語句如下:SELECT CAST( 'China' AS SMALLINT);
返回結果如下:
+----------------------------+ | CAST( 'China' AS SMALLINT) | +----------------------------+ | 0 | +----------------------------+
如果運算式
expr
是JSON類型,且不是數值,轉換為INT/INTEGER、SMALLINT或TINYINT類型,返回報錯。例如,將VARCHAR類型的資料
[1,2,3]
先轉換為JSON類型,再轉換為SMALLINT類型,語句如下:SELECT CAST(CAST('[1,2,3]' AS JSON) AS SMALLINT);
返回錯誤如下:
ERROR 1815 (HY000): [20034, 2021091814103119216818804803453190138] : Cannot cast json to smallint
樣本
將BIGINT類型的資料2001012
轉換為FLOAT類型,語句如下:
SELECT CAST(2001012 AS FLOAT);
返回結果如下:
+-------------------------+
| CAST(2001012 AS FLOAT) |
+-------------------------+
| 2001012.0 |
+-------------------------+
CAST AS BIGINT
CAST (expr AS BIGINT)
命令說明
將expr
轉換為BIGINT類型。
輸入實值型別
BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
轉換規則
VARCHAR類型的資料不是數值時,轉換為BIGINT類型,返回結果為0。
例如,將VARCHAR類型的資料
a
轉換為BIGINT,語句如下:SELECT CAST('a' AS BIGINT);
返回結果如下:
+---------------------+ | CAST('a' AS BIGINT) | +---------------------+ | 0 | +---------------------+
FLOAT、DOUBLE、DECIMAL(m,d)類型轉換為BIGINT類型,精度會丟失。
例如,將DOUBLE類型的資料
1.23456789
轉換為BIGINT類型,語句如下:SELECT CAST(1.23456789 AS BIGINT);
返回結果如下:
+----------------------------+ | CAST(1.23456789 AS BIGINT) | +----------------------------+ | 1 | +----------------------------+
JSON類型的資料不是數值時,轉換為BIGINT類型,會返回NULL。
例如,將JSON類型的資料
{}
轉換為BIGINT類型,語句如下:SELECT CAST(JSON'{}'AS BIGINT);
返回結果如下:
NULL
樣本
將DATE類型的資料
2021-09-18
轉換為BIGINT類型,語句如下:SELECT CAST(DATE '2021-09-18' AS BIGINT);
返回結果如下:
+-----------------------------------+ | CAST(DATE '2021-09-18' AS BIGINT) | +-----------------------------------+ | 20210918 | +-----------------------------------+
將JSON類型的資料
-1
轉換為BIGINT類型,語句如下:SELECT CAST(JSON '-1' AS BIGINT);
返回結果如下:
+---------------------------+ | CAST(JSON '-1' AS BIGINT) | +---------------------------+ | -1 | +---------------------------+
將DOUBLE類型的資料
FLOOR(4/5)
轉換為BIGINT類型,語句如下:SELECT CAST(FLOOR(4/5) as BIGINT);
返回結果如下:
+---------------------------+ |CAST(FLOOR(4/5) as BIGINT) | +---------------------------+ | 0 | +---------------------------+
CAST AS DOUBLE
CAST (expr AS DOUBLE)
命令說明
將expr
轉換為DOUBLE類型。
輸入實值型別
BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
JSON
轉換規則
若VARCHAR類型的資料不是數值,轉換為DOUBLE類型,返回結果為0.0。
例如,將VARCHAR類型的資料
China
轉換為DOUBLE類型,語句如下:SELECT CAST( 'China' AS DOUBLE);
返回結果如下:
+--------------------------+ | CAST( 'China' AS DOUBLE) | +--------------------------+ | 0.0 | +--------------------------+
若JSON類型的資料不是數值,轉換為DOUBLE時會返回NULL。
例如,將JSON類型的資料
{}
轉換為DOUBLE類型,語句如下:SELECT CAST(JSON '{}' AS DOUBLE);
返回結果如下:
NULL
樣本
將DATE類型的資料2021-09-17
轉換為DOUBLE類型,語句如下:
SELECT CAST(DATE '2021-09-17' AS DOUBLE);
返回結果如下:
+------------------------------------+
| CAST(DATE '2021-09-17' AS DOUBLE) |
+------------------------------------+
| 2.0210917E7 |
+------------------------------------+
CAST AS DATE|DATETIME|TIMESTAMP|TIME
CAST (expr AS DATE|DATETIME|TIMESTAMP|TIME)
命令說明
將expr
轉換為DATE、DATETIME、TIMESTAMP或TIME類型。
輸入實值型別
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
JSON
轉換規則
若VARCHAR類型或BIGINT類型的資料不符合時間類型格式,轉換後的結果為NULL。
例如,將VARCHAR類型的資料
a
轉換為TIME類型,語句如下:SELECT CAST('a' AS TIME);
由于格式不匹配會返回NULL,結果如下:
+-------------------+ | CAST('a' AS TIME) | +-------------------+ | NULL | +-------------------+
若目標資料類型包含日期和時間資料,但資料缺少對應的資訊,那麼將根據如下規則自動補充資料:
若
expr
中缺失時間資訊,轉換後的時間預設為00:00:00
。例如,將TIMESTAMP類型的資料
2001-1-22
轉換為TIME類型,語句如下:SELECT CAST(TIMESTAMP '2001-1-22' AS TIME);
由於缺少時間資訊,預設返回
00:00:00
,結果如下:+-------------------------------------+ | CAST(TIMESTAMP '2001-1-22' AS TIME) | +-------------------------------------+ | 00:00:00 | +-------------------------------------+
若
expr
中缺失日期資訊,預設轉換後的日期為執行查詢時用戶端的系統日期。例如,將TIME類型的資料
00:00:00
轉換為DATE類型,語句如下:SELECT CAST(TIME '00:00:00' AS DATE);
由於缺少日期資訊,預設執行查詢時的系統日期,結果如下:
+-------------------------------+ | CAST(TIME '00:00:00' AS DATE) | +-------------------------------+ | 2021-09-14 | +-------------------------------+
樣本
將BIGINT類型的資料20010122000000
轉換成DATE類型,語句如下:
SELECT CAST(20010122000000 AS DATE);
返回結果如下:
+------------------------------+
| CAST(20010122000000 AS DATE) |
+------------------------------+
| 2001-01-22 |
+------------------------------+
CAST AS VARBINARY
CAST (expr AS VARBINARY)
命令說明
將expr
轉換為VARBINARY類型。
輸入實值型別
BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARCHAR
JSON
樣本
將VARCHAR類型的資料CHINA
轉換為VARBINARY類型,語句如下:
SELECT CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR);
返回結果如下:
+--------------------------------------------------------+
| CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR) |
+--------------------------------------------------------+
| CHINA |
+--------------------------------------------------------+
CAST AS VARCHAR
CAST (expr AS VARCHAR)
命令說明
將expr
轉換為VARCHAR類型。
輸入實值型別
BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
DATE、DATETIME、TIMESTAMP、TIME
VARBINARY
說明僅3.1.4及以上版本的AnalyticDB MySQL版叢集,支援通過CAST函數將VARBINARY類型轉換為VARCHAR類型。
MAP
JSON
樣本
將TIMESTAMP類型的資料2001-1-22 00:00:00
轉換為VARCHAR類型,語句如下:
SELECT CAST(TIMESTAMP '2001-1-22 00:00:00' AS VARCHAR);
返回結果如下:
+-------------------------------------------------+
| CAST(TIMESTAMP '2001-1-22 00:00:00' AS VARCHAR) |
+-------------------------------------------------+
| 2001-01-22 00:00:00 |
+-------------------------------------------------+
CAST AS ARRAY
CAST (expr AS ARRAY<element_type>)
命令說明
將expr
轉換為ARRAY類型,其中ARRAY資料由指定資料類型<element_type>
構成。
輸入實值型別
expr
:VARCHAR或JSON類型。<element_type>
:TINYINT、SMALLINT、INT/INTEGER或FLOAT類型。
轉換規則
如果VARCHAR資料或JSON資料不符合ARRAY格式,轉換時會報錯。
例如,將VARCHAR類型的資料{}}
轉換ARRAY資料,其中ARRAY資料由FLOAT類型構成,語句如下:
SELECT CAST('{}}' AS ARRAY<float>);
由于格式不匹配,會提示如下錯誤:
ERROR 1815 (HY000): [30013, 2021091815372119216818804803453204662] : Value cannot be cast to array(real)
樣本
將JSON類型的資料[1,2,3]
轉換ARRAY資料,其中ARRAY資料由INT類型構成,語句如下:
SELECT CAST( JSON '[1,2,3]' AS ARRAY<int>);
返回結果如下:
+-------------------------------------+
| CAST( JSON '[1,2,3]' AS ARRAY<int> |
+-------------------------------------+
| [1,2,3] |
+-------------------------------------+
CAST AS MAP
CAST (expr AS MAP<element_type_1,element_type_2>)
命令說明
將expr
轉換為MAP資料,該MAP會將<element_type_1>
類型的資料對應為<element_type_2>
類型。
輸入實值型別
expr
:VARCHAR類型。<element_type_1>
:BOOLEAN、DECIMAL、DOUBLE、FLOAT、BIGINT、INT/INTEGER、SMALLINT、TINYINT、VARCHAR類型。<element_type_2>
:BOOLEAN、DECIMAL、DOUBLE、FLOAT、BIGINT、INT/INTEGER、SMALLINT、TINYINT、VARCHAR、ARRAY、JSON、MAP類型。
轉換規則
如果VARCHAR類型的資料不符合MAP格式,轉換時會報錯。
例如,將VARCHAR類型的資料[a,b,c]
轉換為MAP類型,語句如下:
SELECT CAST('[a,b,c]' AS MAP<varchar,varchar>);
此時會返回如下錯誤:
ERROR 1815 (HY000): [30013, 2021091815562519216818804803453207833] : Value cannot be cast to map(varchar,varchar)
樣本
將VARCHAR類型的資料{"1":"a"}
轉換為MAP資料,該MAP會將一個VARCHAR類型資料對應為VARCHAR,語句如下:
SELECT CAST('{"1":"a"}' AS MAP<varchar,varchar>);
返回結果如下:
+-------------------------------------------+
| CAST('{"1":"a"}' AS MAP<varchar,varchar>) |
+-------------------------------------------+
| {"1":"a"} |
+-------------------------------------------+
CAST AS JSON
CAST (expr AS JSON)
命令說明
將expr
轉換為JSON類型。
來源資料類型
BOOLEAN
DECIMAL(m,d)、FLOAT、INT/INTEGER、SMALLINT、TINYINT
BIGINT
DOUBLE
VARCHAR
ARRAY
轉換規則
如果VARCHAR類型的資料不符合JSON格式,轉換時會返回NULL。
例如,將VARCHAR類型的資料{}}
轉換成JSON類型,語句如下:
SELECT CAST('{}}' AS JSON);
返回結果如下:
NULL
樣本
將VARCHAR類型的資料
{}
轉換為JSON類型,語句如下:SELECT CAST('{}' AS JSON);
返回結果如下:
+--------------------+ | CAST('{}' AS JSON) | +--------------------+ | {} | +--------------------+
將BIGINT類型的資料
0
轉換成JSON類型,語句如下:SELECT CAST( BIGINT '0' AS JSON);
返回結果如下:
+---------------------------+ | CAST( BIGINT '0' AS JSON) | +---------------------------+ | 0 | +---------------------------+