how to use the cast function to convert data types - AnalyticDB

Syntaxe

CAST(expr AS type)

Paramètre	Obligatoire	Description
`expr`	Oui	Valeur ou expression source à convertir
`type`	Oui	Type de données de destination

Conversions prises en charge

Le tableau suivant répertorie les types de destination pris en charge par CAST ainsi que les types sources acceptés pour chacun d'eux.

Type de destination	Types sources acceptés
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\	VARCHAR, JSON
MAP\	VARCHAR
JSON	BOOLEAN, DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT, DOUBLE, VARCHAR, ARRAY

La conversion de VARBINARY en VARCHAR nécessite AnalyticDB for MySQL V3.1.4 ou une version ultérieure.

Comportement en cas d'échec de la conversion

Le résultat d'un échec de conversion dépend des types source et de destination.

Scénario	Résultat
La valeur VARCHAR ou BIGINT ne correspond pas au format TIME	NULL
Valeur hors limites convertie vers un type numérique (sauf groupe INT)	NULL
VARCHAR non numérique converti en DECIMAL(M,D) ou FLOAT	NULL
JSON non numérique converti en BIGINT ou DOUBLE	NULL
VARCHAR au format JSON incorrect converti en JSON	NULL
Valeur hors limites convertie en INT/INTEGER, SMALLINT ou TINYINT	NULL
VARCHAR non numérique converti en INT/INTEGER, SMALLINT ou TINYINT	0
JSON non numérique converti en INT/INTEGER, SMALLINT ou TINYINT	Erreur
Valeur au format ARRAY incorrect convertie en ARRAY	Erreur
Valeur au format MAP incorrect convertie en MAP	Erreur

CAST AS BOOLEAN

CAST(expr AS BOOLEAN)

Convertit expr en BOOLEAN. Le résultat est toujours 1 (vrai) ou 0 (faux).

Règles de conversion

Type source	Règle
VARCHAR, JSON	`true` ou `1` → `1` ; `false` ou `0` → `0` ; toutes les autres valeurs → NULL
DOUBLE	Différent de `0.0` → `1` ; `0.0` → `0`
DECIMAL(M,D), FLOAT, INT/INTEGER, SMALLINT, TINYINT, BIGINT	Différent de zéro → `1` ; `0` → `0`

Exemples

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

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

-- Non-boolean string returns NULL
SELECT CAST('a' AS BOOLEAN);

NULL

-- Non-zero DOUBLE returns 1
SELECT CAST(4.3 AS BOOLEAN);

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

-- Non-zero INT returns 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])

Convertit expr vers l'un des types numériques suivants : DECIMAL(M,D), FLOAT, INT, INTEGER, SMALLINT ou TINYINT.

Règles de conversion

Les valeurs hors limites renvoient NULL. Par exemple, 99999999 dépasse la plage de SMALLINT :
```
SELECT CAST(99999999 AS SMALLINT);
```
```
NULL
```

Les conversions d'une haute précision vers une faible précision arrondissent la valeur :

SELECT CAST(1.23456789 AS FLOAT);

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

La conversion de DECIMAL(M,D), DOUBLE ou FLOAT vers INT/INTEGER, SMALLINT ou TINYINT arrondit la valeur :

SELECT CAST(1.1342 AS INT);

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

La conversion d'un VARCHAR non numérique vers DECIMAL(M,D) ou FLOAT renvoie NULL :
```
SELECT CAST('China' AS DECIMAL(5,2));
```
```
NULL
```

La conversion d'un VARCHAR non numérique vers INT/INTEGER, SMALLINT ou TINYINT renvoie 0 :

SELECT CAST('China' AS SMALLINT);

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

La conversion d'un JSON non numérique vers INT/INTEGER, SMALLINT ou TINYINT renvoie une erreur :

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

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

Exemple

SELECT CAST(2001012 AS FLOAT);

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

CAST AS BIGINT

CAST(expr AS BIGINT)

Convertit expr vers le type BIGINT.

Règles de conversion

Un VARCHAR non numérique renvoie 0 :

SELECT CAST('a' AS BIGINT);

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

Les valeurs FLOAT, DOUBLE ou DECIMAL(M,D) sont arrondies :

SELECT CAST(1.23456789 AS BIGINT);

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

Un JSON non numérique renvoie NULL :
```
SELECT CAST(JSON'{}' AS BIGINT);
```
```
NULL
```

Exemples

-- DATE to BIGINT: returns the date as an integer in YYYYMMDD format
SELECT CAST(DATE '2021-09-18' AS BIGINT);

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

-- JSON numeric value to BIGINT
SELECT CAST(JSON '-1' AS BIGINT);

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

-- Expression result to BIGINT
SELECT CAST(FLOOR(4/5) AS BIGINT);

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

CAST AS DOUBLE

CAST(expr AS DOUBLE)

Convertit expr vers le type DOUBLE.

Règles de conversion

Un VARCHAR non numérique renvoie 0.0 :

SELECT CAST('China' AS DOUBLE);

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

Un JSON non numérique renvoie NULL :
```
SELECT CAST(JSON '{}' AS DOUBLE);
```
```
NULL
```

Exemple

-- DATE to DOUBLE: returns the date as a floating-point number
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)

Convertit expr vers DATE, DATETIME, TIMESTAMP ou TIME.

Règles de conversion

Un VARCHAR ou BIGINT qui ne correspond pas au format TIME renvoie NULL :

SELECT CAST('a' AS TIME);

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

L'absence de composant horaire prend par défaut la valeur 00:00:00 :

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

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

L'absence de composant date prend par défaut la date système au moment de l'exécution de la requête :

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

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

Exemple

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

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

CAST AS VARBINARY

CAST(expr AS VARBINARY)

Convertit expr vers le type VARBINARY. Pour lire le résultat sous forme de chaîne, encapsulez-le dans un autre CAST vers VARCHAR.

Exemple

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

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

CAST AS VARCHAR

CAST(expr AS VARCHAR)

Convertit expr vers le type VARCHAR.

La conversion de VARBINARY en VARCHAR nécessite AnalyticDB for MySQL V3.1.4 ou une version ultérieure.

Exemple

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

Convertit expr en un tableau dont les éléments sont de type element_type.

Paramètre	Valeurs acceptées
`expr`	VARCHAR, JSON
`element_type`	TINYINT, SMALLINT, INT/INTEGER, FLOAT

Règles de conversion

Si expr n'est pas conforme au format ARRAY, une erreur est renvoyée :

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

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

Exemple

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

Convertit une valeur VARCHAR en un mappage de key_type vers value_type.

Paramètre	Valeurs acceptées
`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

Règles de conversion

Si expr n'est pas conforme au format MAP, une erreur est renvoyée :

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

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

Exemple

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

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

CAST AS JSON

CAST(expr AS JSON)

Convertit expr vers le type JSON.

Règles de conversion

Si expr est un VARCHAR qui n'est pas conforme au format JSON, NULL est renvoyé :

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

NULL

Exemples

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

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

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

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