cara menggunakan fungsi cast untuk mengonversi tipe data - AnalyticDB

Gunakan CAST untuk mengonversi nilai dari satu tipe data ke tipe data lainnya di AnalyticDB for MySQL.

Sintaksis

CAST(expr AS type)

Parameter	Wajib	Deskripsi
`expr`	Ya	Nilai sumber atau ekspresi yang akan dikonversi
`type`	Ya	Tipe data tujuan

Konversi yang didukung

Tabel berikut mencantumkan jenis tujuan yang didukung oleh CAST dan jenis sumber yang diterima masing-masing.

Jenis tujuan	Jenis sumber yang diterima
BOOLEAN	DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, JSON
DECIMAL(M,D), FLOAT, INT, INTEGER, SMALLINT, TINYINT	DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, JSON
BIGINT	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR
DOUBLE	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR, JSON
DATE, DATETIME, TIMESTAMP, TIME	DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR, JSON
VARBINARY	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARCHAR, JSON
VARCHAR	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, DATE, DATETIME, TIMESTAMP, TIME, VARBINARY, MAP, JSON
ARRAY\<element_type\>	VARCHAR, JSON
MAP\<key_type, value_type\>	VARCHAR
JSON	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, ARRAY

Mengonversi VARBINARY ke VARCHAR memerlukan AnalyticDB for MySQL V3.1.4 atau yang lebih baru.

Perilaku saat konversi gagal

Hasil ketika konversi gagal bergantung pada jenis sumber dan tujuan.

Skenario	Hasil
Nilai VARCHAR atau BIGINT tidak sesuai dengan format TIME	NULL
Nilai di luar rentang yang dikonversi ke tipe numerik (kecuali kelompok INT)	NULL
VARCHAR non-numerik dikonversi ke DECIMAL(M,D) atau FLOAT	NULL
JSON non-numerik dikonversi ke BIGINT atau DOUBLE	NULL
VARCHAR yang bukan dalam format JSON dikonversi ke JSON	NULL
Nilai di luar rentang yang dikonversi ke INT/INTEGER, SMALLINT, atau TINYINT	NULL
VARCHAR non-numerik dikonversi ke INT/INTEGER, SMALLINT, atau TINYINT	0
JSON non-numerik dikonversi ke INT/INTEGER, SMALLINT, atau TINYINT	Error
Nilai yang bukan dalam format ARRAY dikonversi ke ARRAY	Error
Nilai yang bukan dalam format MAP dikonversi ke MAP	Error

CAST AS BOOLEAN

CAST(expr AS BOOLEAN)

Mengonversi expr ke BOOLEAN. Hasilnya selalu 1 (true) atau 0 (false).

Aturan konversi

Jenis sumber	Aturan
VARCHAR, JSON	`true` atau `1` → `1`; `false` atau `0` → `0`; semua nilai lainnya → NULL
DOUBLE	Non-`0.0` → `1`; `0.0` → `0`
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT	Non-nol → `1`; `0` → `0`

Contoh

-- Cast VARCHAR '1' ke BOOLEAN
SELECT CAST('1' AS BOOLEAN);

+----------------------+
| CAST('1' AS BOOLEAN) |
+----------------------+
|                    1 |
+----------------------+

-- String non-boolean mengembalikan NULL
SELECT CAST('a' AS BOOLEAN);

NULL

-- DOUBLE non-nol mengembalikan 1
SELECT CAST(4.3 AS BOOLEAN);

+----------------------+
| CAST(4.3 AS BOOLEAN) |
+----------------------+
|                    1 |
+----------------------+

-- INT non-nol mengembalikan 1
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])

Mengonversi expr ke salah satu tipe numerik: DECIMAL(M,D), FLOAT, INT, INTEGER, SMALLINT, atau TINYINT.

Aturan konversi

Nilai di luar rentang mengembalikan NULL. Misalnya, 99999999 melebihi rentang SMALLINT:
```
SELECT CAST(99999999 AS SMALLINT);
```
```
NULL
```

Konversi dari presisi tinggi ke presisi rendah membulatkan nilainya:

SELECT CAST(1.23456789 AS FLOAT);

+---------------------------+
| CAST(1.23456789 AS FLOAT) |
+---------------------------+
|                 1.2345679 |
+---------------------------+

DECIMAL(M,D), DOUBLE, atau FLOAT ke INT/INTEGER, SMALLINT, atau TINYINT membulatkan nilainya:

SELECT CAST(1.1342 AS INT);

+---------------------+
| CAST(1.1342 AS INT) |
+---------------------+
|                   1 |
+---------------------+

VARCHAR non-numerik ke DECIMAL(M,D) atau FLOAT mengembalikan NULL:
```
SELECT CAST('China' AS DECIMAL(5,2));
```
```
NULL
```

VARCHAR non-numerik ke INT/INTEGER, SMALLINT, atau TINYINT mengembalikan 0:

SELECT CAST('China' AS SMALLINT);

+---------------------------+
| CAST('China' AS SMALLINT) |
+---------------------------+
|                         0 |
+---------------------------+

JSON non-numerik ke INT/INTEGER, SMALLINT, atau TINYINT mengembalikan error:

SELECT CAST(CAST('[1,2,3]' AS JSON) AS SMALLINT);

ERROR 1815 (HY000): [20034, 2021091814103119216818804803453190138] : Cannot cast json to smallint

Contoh

SELECT CAST(2001012 AS FLOAT);

+------------------------+
| CAST(2001012 AS FLOAT) |
+------------------------+
|               2001012.0|
+------------------------+

CAST AS BIGINT

CAST(expr AS BIGINT)

Mengonversi expr ke tipe BIGINT.

Aturan konversi

VARCHAR non-numerik mengembalikan 0:

SELECT CAST('a' AS BIGINT);

+---------------------+
| CAST('a' AS BIGINT) |
+---------------------+
|                   0 |
+---------------------+

Nilai FLOAT, DOUBLE, atau DECIMAL(M,D) dibulatkan:

SELECT CAST(1.23456789 AS BIGINT);

+----------------------------+
| CAST(1.23456789 AS BIGINT) |
+----------------------------+
|                          1 |
+----------------------------+

JSON non-numerik mengembalikan NULL:
```
SELECT CAST(JSON'{}' AS BIGINT);
```
```
NULL
```

Contoh

-- DATE ke BIGINT: mengembalikan tanggal sebagai bilangan bulat dalam format YYYYMMDD
SELECT CAST(DATE '2021-09-18' AS BIGINT);

+-----------------------------------+
| CAST(DATE '2021-09-18' AS BIGINT) |
+-----------------------------------+
|                          20210918 |
+-----------------------------------+

-- Nilai numerik JSON ke BIGINT
SELECT CAST(JSON '-1' AS BIGINT);

+---------------------------+
| CAST(JSON '-1' AS BIGINT) |
+---------------------------+
|                        -1 |
+---------------------------+

-- Hasil ekspresi ke BIGINT
SELECT CAST(FLOOR(4/5) AS BIGINT);

+----------------------------+
| CAST(FLOOR(4/5) AS BIGINT) |
+----------------------------+
|                          0 |
+----------------------------+

CAST AS DOUBLE

CAST(expr AS DOUBLE)

Mengonversi expr ke tipe DOUBLE.

Aturan konversi

VARCHAR non-numerik mengembalikan 0.0:

SELECT CAST('China' AS DOUBLE);

+--------------------------+
| CAST('China' AS DOUBLE)  |
+--------------------------+
|                      0.0 |
+--------------------------+

JSON non-numerik mengembalikan NULL:
```
SELECT CAST(JSON '{}' AS DOUBLE);
```
```
NULL
```

Contoh

-- DATE ke DOUBLE: mengembalikan tanggal sebagai bilangan titik mengambang
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)

Mengonversi expr ke DATE, DATETIME, TIMESTAMP, atau TIME.

Aturan konversi

VARCHAR atau BIGINT yang tidak sesuai dengan format TIME mengembalikan NULL:

SELECT CAST('a' AS TIME);

+-------------------+
| CAST('a' AS TIME) |
+-------------------+
| NULL              |
+-------------------+

Komponen waktu yang tidak ada menggunakan nilai default 00:00:00:

SELECT CAST(TIMESTAMP '2001-1-22' AS TIME);

+-------------------------------------+
| CAST(TIMESTAMP '2001-1-22' AS TIME) |
+-------------------------------------+
| 00:00:00                            |
+-------------------------------------+

Komponen tanggal yang tidak ada menggunakan tanggal sistem saat kueri dijalankan:

SELECT CAST(TIME '00:00:00' AS DATE);

+-------------------------------+
| CAST(TIME '00:00:00' AS DATE) |
+-------------------------------+
| 2021-09-14                    |
+-------------------------------+

Contoh

-- BIGINT ke DATE
SELECT CAST(20010122000000 AS DATE);

+------------------------------+
| CAST(20010122000000 AS DATE) |
+------------------------------+
| 2001-01-22                   |
+------------------------------+

CAST AS VARBINARY

CAST(expr AS VARBINARY)

Mengonversi expr ke tipe VARBINARY. Untuk membaca hasilnya sebagai string, bungkus dengan CAST lain ke VARCHAR.

Contoh

SELECT CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR);

+---------------------------------------------+
| CAST(CAST('CHINA' AS VARBINARY) AS VARCHAR) |
+---------------------------------------------+
| CHINA                                       |
+---------------------------------------------+

CAST AS VARCHAR

CAST(expr AS VARCHAR)

Mengonversi expr ke tipe VARCHAR.

Mengonversi VARBINARY ke VARCHAR memerlukan AnalyticDB for MySQL V3.1.4 atau yang lebih baru.

Contoh

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>)

Mengonversi expr ke array yang elemennya bertipe element_type.

Parameter	Nilai yang diterima
`expr`	VARCHAR, JSON
`element_type`	TINYINT, SMALLINT, INT/INTEGER, FLOAT

Aturan konversi

Jika expr tidak sesuai dengan format ARRAY, error akan dikembalikan:

SELECT CAST('{}}' AS ARRAY<float>);

ERROR 1815 (HY000): [30013, 2021091815372119216818804803453204662] : Value cannot be cast to array(real)

Contoh

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<key_type, value_type>)

Mengonversi nilai VARCHAR ke map dari key_type ke value_type.

Parameter	Nilai yang diterima
`expr`	VARCHAR
`key_type`	BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, VARCHAR
`value_type`	BOOLEAN, DECIMAL, DOUBLE, FLOAT, BIGINT, INT/INTEGER, SMALLINT, TINYINT, VARCHAR, ARRAY, JSON, MAP

Aturan konversi

Jika expr tidak sesuai dengan format MAP, error akan dikembalikan:

SELECT CAST('[a,b,c]' AS MAP<varchar,varchar>);

ERROR 1815 (HY000): [30013, 2021091815562519216818804803453207833] : Value cannot be cast to map(varchar,varchar)

Contoh

SELECT CAST('{"1":"a"}' AS MAP<varchar,varchar>);

+-------------------------------------------+
| CAST('{"1":"a"}' AS MAP<varchar,varchar>)  |
+-------------------------------------------+
| {"1":"a"}                                 |
+-------------------------------------------+

CAST AS JSON

CAST(expr AS JSON)

Mengonversi expr ke tipe JSON.

Aturan konversi

Jika expr adalah VARCHAR yang tidak sesuai dengan format JSON, NULL akan dikembalikan:

SELECT CAST('{}}' AS JSON);

NULL

Contoh

-- VARCHAR ke JSON
SELECT CAST('{}' AS JSON);

+--------------------+
| CAST('{}' AS JSON) |
+--------------------+
| {}                 |
+--------------------+

-- BIGINT ke JSON
SELECT CAST(BIGINT '0' AS JSON);

+--------------------------+
| CAST(BIGINT '0' AS JSON) |
+--------------------------+
| 0                        |
+--------------------------+