Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.7Mb
PDF (A4) - 40.7Mb
PDF (RPM) - 40.1Mb
HTML Download (TGZ) - 10.2Mb
HTML Download (Zip) - 10.2Mb
HTML Download (RPM) - 8.7Mb
Man Pages (TGZ) - 244.6Kb
Man Pages (Zip) - 349.8Kb
Info (Gzip) - 3.9Mb
Info (Zip) - 3.9Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  Functions and Operators  /  Cast Functions and Operators

12.11 Cast Functions and Operators

Table 12.15 Cast Functions and Operators

Name Description
BINARY Cast a string to a binary string
CAST() Cast a value as a certain type
CONVERT() Cast a value as a certain type

Cast functions and operators enable conversion of values from one data type to another.

CONVERT() with a USING clause converts data between different character sets:

CONVERT(expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.


SELECT CONVERT('test' USING utf8mb4);
SELECT CONVERT(_latin1'Müller' USING utf8mb4);
INSERT INTO utf8mb4_table (utf8mb4_column)
    SELECT CONVERT(latin1_column USING utf8mb4) FROM latin1_table;

To convert strings between different character sets, you can also use CONVERT(expr, type) syntax (without USING), or CAST(expr AS type), which is equivalent:

CONVERT(string, CHAR[(N)] CHARACTER SET charset_name)
CAST(string AS CHAR[(N)] CHARACTER SET charset_name)



If you specify CHARACTER SET charset_name as just shown, the character set and collation of the result are charset_name and the default collation of charset_name. If you omit CHARACTER SET charset_name, the character set and collation of the result are defined by the character_set_connection and collation_connection system variables that determine the default connection character set and collation (see Section 10.4, “Connection Character Sets and Collations”).

A COLLATE clause is not permitted within a CONVERT() or CAST() call, but you can apply it to the function result. For example, these are legal:

SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin;

But these are illegal:

SELECT CONVERT('test' USING utf8mb4 COLLATE utf8mb4_bin);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

Normally, you cannot compare a BLOB value or other binary string in case-insensitive fashion because binary strings use the binary character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, first use the CONVERT() or CAST() function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the conversion result character set has a case-insensitive collation, a LIKE operation is not case-sensitive. That is true for the following operation because the default utf8mb4 collation (utf8mb4_0900_ai_ci) is not case-sensitive:

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4)
  FROM tbl_name;

To specify a particular collation for the converted string, use a COLLATE clause following the CONVERT() call:

SELECT 'A' LIKE CONVERT(blob_col USING utf8mb4) COLLATE utf8mb4_unicode_ci
  FROM tbl_name;

To use a different character set, substitute its name for utf8mb4 in the preceding statements (and similarly to use a different collation).

CONVERT() and CAST() can be used more generally for comparing strings represented in different character sets. For example, a comparison of these strings results in an error because they have different character sets:

mysql> SET @s1 = _latin1 'abc', @s2 = _latin2 'abc';
mysql> SELECT @s1 = @s2;
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT)
and (latin2_general_ci,IMPLICIT) for operation '='

Converting one of the strings to a character set compatible with the other enables the comparison to occur without error:

mysql> SELECT @s1 = CONVERT(@s2 USING latin1);
| @s1 = CONVERT(@s2 USING latin1) |
|                               1 |

For string literals, another way to specify the character set is to use a character set introducer. _latin1 and _latin2 in the preceding example are instances of introducers. Unlike conversion functions such as CAST(), or CONVERT(), which convert a string from one character set to another, an introducer designates a string literal as having a particular character set, with no conversion involved. For more information, see Section 10.3.8, “Character Set Introducers”.

Character set conversion is also useful preceding lettercase conversion of binary strings. LOWER() and UPPER() are ineffective when applied directly to binary strings because the concept of lettercase does not apply. To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string:

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
| New York    | new york                           |

Be aware that if you convert an indexed column using BINARY, CAST(), or CONVERT(), MySQL may not be able to use the index efficiently.

The cast functions are useful for creating a column with a specific type in a CREATE TABLE ... SELECT statement:

mysql> CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE) AS c1;
mysql> SHOW CREATE TABLE new_table\G
*************************** 1. row ***************************
       Table: new_table
Create Table: CREATE TABLE `new_table` (
  `c1` date DEFAULT NULL

The cast functions are useful for sorting ENUM columns in lexical order. Normally, sorting of ENUM columns occurs using the internal numeric values. Casting the values to CHAR results in a lexical sort:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST() also changes the result if you use it as part of a more complex expression such as CONCAT('Date: ',CAST(NOW() AS DATE)).

For temporal values, there is little need to use CAST() to extract data in different formats. Instead, use a function such as EXTRACT(), DATE_FORMAT(), or TIME_FORMAT(). See Section 12.7, “Date and Time Functions”.

To cast a string to a number, you normally need do nothing other than use the string value in numeric context:

mysql> SELECT 1+'1';
       -> 2

That is also true for hexadecimal and bit literals, which are binary strings by default:

mysql> SELECT X'41', X'41'+0;
        -> 'A', 65
mysql> SELECT b'1100001', b'1100001'+0;
        -> 'a', 97

A string used in an arithmetic operation is converted to a floating-point number during expression evaluation.

A number used in string context is converted to a string:

mysql> SELECT CONCAT('hello you ',2);
        -> 'hello you 2'

For information about implicit conversion of numbers to strings, see Section 12.3, “Type Conversion in Expression Evaluation”.

MySQL supports arithmetic with both signed and unsigned 64-bit values. For numeric operators (such as + or -) where one of the operands is an unsigned integer, the result is unsigned by default (see Section 12.6.1, “Arithmetic Operators”). To override this, use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT 1 - 2;
        -> -1
        -> 18446744073709551615
        -> -1

If either operand is a floating-point value, the result is a floating-point value and is not affected by the preceding rule. (In this context, DECIMAL column values are regarded as floating-point values.)

mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
        -> -1.0

The SQL mode affects the result of conversion operations (see Section 5.1.11, “Server SQL Modes”). Examples:

  • For conversion of a zero date string to a date, CONVERT() and CAST() return NULL and produce a warning when the NO_ZERO_DATE SQL mode is enabled.

  • For integer subtraction, if the NO_UNSIGNED_SUBTRACTION SQL mode is enabled, the subtraction result is signed even if any operand is unsigned.

The following list describes the available cast functions and operators:

  • BINARY expr

    The BINARY operator converts the expression to a binary string (a string that has the binary character set and binary collation). A common use for BINARY is to force a character string comparison to be done byte by byte using numeric byte values rather than character by character. The BINARY operator also causes trailing spaces in comparisons to be significant. For information about the differences between the binary collation of the binary character set and the _bin collations of nonbinary character sets, see Section 10.8.5, “The binary Collation Compared to _bin Collations”.

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT BINARY 'a' = 'A';
            -> 0
    mysql> SELECT 'a' = 'a ';
            -> 1
    mysql> SELECT BINARY 'a' = 'a ';
            -> 0

    In a comparison, BINARY affects the entire operation; it can be given before either operand with the same result.

    To convert a string expression to a binary string, these constructs are equivalent:

    BINARY expr
    CAST(expr AS BINARY)

    If a value is a string literal, it can be designated as a binary string without performing any conversion by using the _binary character set introducer:

    mysql> SELECT 'a' = 'A';
            -> 1
    mysql> SELECT _binary 'a' = 'A';
            -> 0

    For information about introducers, see Section 10.3.8, “Character Set Introducers”.

    The BINARY operator in expressions differs in effect from the BINARY attribute in character column definitions. A character column defined with the BINARY attribute is assigned the table default character set and the binary (_bin) collation of that character set. Every nonbinary character set has a _bin collation. For example, if the table default character set is utf8mb4, these two column definitions are equivalent:

    CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

    The use of CHARACTER SET binary in the definition of a CHAR, VARCHAR, or TEXT column causes the column to be treated as the corresponding binary string data type. For example, the following pairs of definitions are equivalent:

    CHAR(10) CHARACTER SET binary
  • CAST(expr AS type [ARRAY])

    CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])

    timezone_specifier: [INTERVAL] '+00:00' | 'UTC'

    The CAST() function takes an expression of any type and produces a result value of the specified type, similar to CONVERT(). For more information, see the description of CONVERT().

    In MySQL 8.0.17 and later, InnoDB allows the use of an additional ARRAY keyword for creating a multi-valued index on a JSON array as part of CREATE INDEX, CREATE TABLE, and ALTER TABLE statements. ARRAY is not supported except when used to create a multi-valued index in one of these statements, in which case it is required. The column being indexed must be a column of type JSON. With ARRAY, the type following the AS keyword may specify any of the types supported by CAST(), with the exceptions of BINARY, JSON, and YEAR. For syntax information and examples, as well as other relevant information, see Multi-Valued Indexes.


    Unlike CAST(), CONVERT() does not support multi-valued index creation or the ARRAY keyword.

    Beginning with MySQL 8.0.22, CAST() supports retrieval of a TIMESTAMP value as being in UTC, using the AT TIMEZONE operator. The only supported time zone is UTC; this can be specified as either of '+00:00' or 'UTC'. The only return type supported by this syntax is DATETIME, with an optional precision specifier in the range of 0 to 6, inclusive.

    TIMESTAMP values that use timezone offsets are also supported.

    mysql> SELECT @@system_time_zone;
    | @@system_time_zone |
    | EDT                |
    1 row in set (0.00 sec)
    Query OK, 0 rows affected (0.41 sec)
    mysql> INSERT INTO tz VALUES
         >     ROW(CURRENT_TIMESTAMP),
         >     ROW('2020-07-28 14:50:15+1:00');
    Query OK, 1 row affected (0.08 sec)
    mysql> TABLE tz;
    | c                   |
    | 2020-07-28 09:22:41 |
    | 2020-07-28 09:50:15 |
    2 rows in set (0.00 sec)
    mysql> SELECT CAST(c AT TIME ZONE '+00:00' AS DATETIME) AS u FROM tz;
    | u                   |
    | 2020-07-28 13:22:41 |
    | 2020-07-28 13:50:15 |
    2 rows in set (0.00 sec)
    | u                      |
    | 2020-07-28 13:22:41.00 |
    | 2020-07-28 13:50:15.00 |
    2 rows in set (0.00 sec)

    If you use 'UTC' as the time zone specifier with this form of CAST(), and the server raises an error such as Unknown or incorrect time zone: 'UTC', you may need to install the MySQL time zone tables (see Populating the Time Zone Tables).

    AT TIME ZONE does not support the ARRAY keyword, and is not supported by the CONVERT() function.

  • CONVERT(expr USING transcoding_name), CONVERT(expr,type)

    The CONVERT() function takes an expression of any type and produces a result value of the specified type.

    CONVERT(... USING ...) is standard SQL syntax. The non-USING form of CONVERT() is ODBC syntax.

    CONVERT(expr USING transcoding_name) converts data between different character sets. In MySQL, transcoding names are the same as the corresponding character set names. For example, this statement converts the string 'abc' in the default character set to the corresponding string in the utf8mb4 character set:

    SELECT CONVERT('abc' USING utf8mb4);

    CONVERT(expr, type) syntax (without USING) takes an expression and a type value specifying the result type. This operation may also be expressed as CAST(expr AS type), which is equivalent. These type values are permitted:

    • BINARY[(N)]

      Produces a string with the BINARY data type. For a description of how this affects comparisons, see Section 11.3.3, “The BINARY and VARBINARY Types”. If the optional length N is given, BINARY(N) causes the cast to use no more than N bytes of the argument. Values shorter than N bytes are padded with 0x00 bytes to a length of N.

    • CHAR[(N)] [charset_info]

      Produces a string with the CHAR data type. If the optional length N is given, CHAR(N) causes the cast to use no more than N characters of the argument. No padding occurs for values shorter than N characters.

      With no charset_info clause, CHAR produces a string with the default character set. To specify the character set explicitly, these charset_info values are permitted:

      • CHARACTER SET charset_name: Produces a string with the given character set.

      • ASCII: Shorthand for CHARACTER SET latin1.

      • UNICODE: Shorthand for CHARACTER SET ucs2.

      In all cases, the string has the character set default collation.

    • DATE

      Produces a DATE value.


      Produces a DATETIME value.

    • DECIMAL[(M[,D])]

      Produces a DECIMAL value. If the optional M and D values are given, they specify the maximum number of digits (the precision) and the number of digits following the decimal point (the scale).

    • DOUBLE

      Produces a DOUBLE result. Added in MySQL 8.0.17.

    • FLOAT[(p)]

      If the precision p is not specified, produces a result of type FLOAT. If p is provided and 0 <= < p <= 24, the result is of type FLOAT. If 25 <= p <= 53, the result is of type DOUBLE. If p < 0 or p > 53, an error is returned. Added in MySQL 8.0.17.

    • JSON

      Produces a JSON value. For details on the rules for conversion of values between JSON and other types, see Comparison and Ordering of JSON Values.

    • NCHAR[(N)]

      Like CHAR, but produces a string with the national character set. See Section 10.3.7, “The National Character Set”.

      Unlike CHAR, NCHAR does not permit trailing character set information to be specified.

    • REAL

      Produces a result of type REAL. This is actually FLOAT if REAL_AS_FLOAT SQL mode is enabled; otherwise the result is of type DOUBLE.


      Produces a signed integer value.

    • TIME

      Produces a TIME value.


      Produces an unsigned integer value.

    • YEAR

      Produces a YEAR value. Added in MySQL 8.0.22. The rules governing conversion to YEAR are listed here:

      • For a four-digit number in the range 1901-2155 inclusive, or for a string which can be interpreted as a four-digit number in this range, return the corresponding YEAR value.

      • For a number consisting of one or two digits, or for a string which can be interpeted as such a number, return a YEAR value as follows:

        • If the number is in the range 1-69 inclusive, add 2000 and return the sum.

        • If the number is in the range 70-99 inclusive, add 1900 and return the sum.

      • For a string which evaluates to 0, return 2000.

      • For the number 0, return 0.

      • For a DATE, DATETIME, or TIMESTAMP value, return the YEAR portion of the value. For a TIME value, return the current year.

        If you do not specify the type of a TIME argument, you may get a different result from what you expect, as shown here:

        mysql> SELECT CONVERT("11:35:00", YEAR), CONVERT(TIME "11:35:00", YEAR);
        | CONVERT("11:35:00", YEAR) | CONVERT(TIME "11:35:00", YEAR) |
        |                      2011 |                           2020 |
      • If the argument is of type DECIMAL, DOUBLE, DECIMAL, or REAL, round the value to the nearest integer, then attempt to cast the value to YEAR using the rules for integer values, as shown here:

        mysql> SELECT CONVERT(1944.35, YEAR), CONVERT(1944.50, YEAR);
        | CONVERT(1944.35, YEAR) | CONVERT(1944.50, YEAR) |
        |                   1944 |                   1945 |
        1 row in set (0.00 sec)
        mysql> SELECT CONVERT(66.35, YEAR), CONVERT(66.50, YEAR);
        | CONVERT(66.35, YEAR) | CONVERT(66.50, YEAR) |
        |                 2066 |                 2067 |
        1 row in set (0.00 sec)
      • An argument of type GEOMETRY cannot be converted to YEAR.

      • For a value that cannot be successfully converted to YEAR, return NULL.

      A string value containing non-numeric characters which must be truncated prior to conversion raises a warning, as shown here:

      mysql> SELECT CONVERT("1979aaa", YEAR);
      | CONVERT("1979aaa", YEAR) |
      |                     1979 |
      1 row in set, 1 warning (0.00 sec)
      mysql> SHOW WARNINGS;
      | Level   | Code | Message                                   |
      | Warning | 1292 | Truncated incorrect YEAR value: '1979aaa' |