MySQL CAST() Function

In MySQL, the CAST() function converts the parameter value ​​of any type to a value ​​of the specified type and return the result.

CAST() Syntax

Here is the syntax of the MySQL CAST() function:

CAST(expr AS data_type)

CAST(expr AS data_type) is equivalent to CONVERT(expr, type).


  • expr

    Required. The value to be converted to a specified data type.

  • data_type

    Required. The target data type. You can use the following data types:

    • BINARY[(N)]: If the argument is empty (zero length), the result is of BINARY(0) data type, otherwise the result is a string of VARBINARY data type.

    • CHAR[(N)]: The result is a string of VARCHAR data type. Unless the argument is empty, the result is CHAR(0) data type.

    • DATE: The result is of DATE data type.

    • DATETIME[(M)]: The result is of DATETIME data type and M is the number of fractional seconds.

    • DECIMAL[(M[,D])]: The result is of DECIMAL data type.

    • DOUBLE: The result is of DOUBLE data type. It was added in MySQL 8.0.17.

    • FLOAT[(p)]: Convert the data according to the following rules

      • If not specified p, the result is of FLOAT data type.
      • If 0 <= p <= 24, the result is of FLOAT data type.
      • If 25 <= p <= 53, the result is of DOUBLE data type.
      • If p < 0or p > 53, return an error.
    • JSON: The result is of JSON data type.

    • NCHAR[(N)]: The result is of NCHAR data type.

    • REAL: The result is of REAL data type. Actually, it is FLOAT if REAL_AS_FLOAT enabled, otherwise it is DOUBLE.

    • SIGNED [INTEGER]: The result is a signed BIGINT data type.

    • TIME[(M)]: The result is of TIME data type and M is the number of fractional seconds.

    • UNSIGNED [INTEGER]: The result is an unsigned BIGINT data type.

    • YEAR: The result is of YEAR data type, which was added in MySQL 8.0.22.

Return value

The MySQL CAST() function converts a value of any data type to the specified data type and returns the result.

CAST() Examples

Here are some examples of CAST().

convert to binary

You can use the CAST() function to convert a regular string to a binary string.

| CAST('hello' AS BINARY)                          |
| 0x68656C6C6F                                     |

Note that in the mysql client, binary strings are printed in hexadecimal by default.

You can also use the BINARY operator to get the binary string of a regular string as follow:

| BINARY 'hello'                 |
| 0x68656C6C6F                   |

Convert to JSON

You can use the CAST() function to convert a JSON document represented as a string to value of JSON data type.

SELECT CAST('[1, 2, "a"]' AS JSON);
| CAST('[1, 2, "a"]' AS JSON) |
| [1, 2, "a"]                 |

Convert to datetime

You can use the CAST() function to convert date/time values ​​represented by strings to data of DATE, DATETIME type.

    CAST('2022-02-28' AS DATE),
    CAST('10:10:10' AS TIME),
    CAST('2022-02-28 10:10:10' AS DATETIME);
| CAST('2022-02-28' AS DATE) | CAST('10:10:10' AS TIME) | CAST('2022-02-28 10:10:10' AS DATETIME) |
| 2022-02-28                 | 10:10:10                 | 2022-02-28 10:10:10                     |