CAST expression

Introduction

CAST converts a value of one data type into another data type. TRY_CAST is a safe type conversion mechanism that returns a SQL NULL value instead of throwing an error when the conversion might fail.

Syntax

CAST( <source_expr> AS <target_data_type> )
TRY_CAST( <source_expr> AS <target_data_type> )

Arguments

source_expr
Expression of any supported data type to be converted into a different data type.
target_data_type
The target data type. If the type supports additional properties (for example, precision and scale for DECIMAL(p, s)), include them as needed.

Strict Mode

In the default (non-strict) mode, an invalid conversion returns NULL. Strict mode, enabled with the enable_strict_cast variable, reports an error instead:

set enable_strict_cast = true;

In strict mode, illegal CAST operations will directly result in errors:

mysql> select cast('abc' as int);
ERROR 1105 (HY000): errCode = 2, detailMessage = abc can't cast to INT in strict mode.

The advantages of strict mode are:

It prevents users from getting unexpected values during CAST operations
The system can assume that all data can be successfully type-converted (illegal data will directly cause errors), enabling better optimization during computation

Examples

Normal CAST Conversion

SELECT CAST('123' AS INT);

+--------------------+
| cast('123' as int) |
+--------------------+
|                123 |
+--------------------+

Using TRY_CAST to Handle Potentially Failed Conversions

When conversions might fail, using TRY_CAST can prevent query errors by returning NULL instead:

SELECT TRY_CAST('abc' AS INT);

+------------------------+
| try_cast('abc' as int) |
+------------------------+
|                   NULL |
+------------------------+

Behavior

We categorize CAST by the target_data_type:

Implicit CAST

Some functions may trigger implicit CASTs, which can lead to unexpected behavior in certain cases. You can use the EXPLAIN statement to check whether an implicit CAST occurs:

EXPLAIN SELECT length(123);

...
length(CAST(123 AS varchar(65533)))
...

You can see from the execution plan above that the system automatically performs a CAST conversion, converting the integer 123 to a string type. This is an example of implicit CAST.

Introduction​

Syntax​

Arguments​

Strict Mode​

Examples​

Normal CAST Conversion​

Using TRY_CAST to Handle Potentially Failed Conversions​

Behavior​

Implicit CAST​