14.10 Cast Functions and Operators (original) (raw)

Table 14.15 Cast Functions and Operators

Name Description Deprecated
BINARY Cast a string to a binary string Yes
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.

Cast Function and Operator Descriptions

mysql> SET NAMES utf8mb4 COLLATE utf8mb4_general_ci;  
        -> OK  
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: 

CONVERT(expr USING BINARY)  
CAST(expr AS BINARY)  
BINARY expr

If a value is a string literal, it can be designated as a binary string without converting it by using the_binary character set introducer: 

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

For information about introducers, seeSection 12.3.8, “Character Set Introducers”.
The BINARY operator in expressions differs in effect from theBINARY attribute in character column definitions. For a character column defined with theBINARY attribute, MySQL assigns 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) BINARY  
CHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin

The use of CHARACTER SET binary in the definition of a CHAR,VARCHAR, orTEXT 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  
BINARY(10)  
VARCHAR(10) CHARACTER SET binary  
VARBINARY(10)  
TEXT CHARACTER SET binary  
BLOB

If BINARY is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, seeSection 6.5.1, “mysql — The MySQL Command-Line Client”.

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 typeJSON. With ARRAY, the_type_ following theAS keyword may specify any of the types supported by CAST(), with the exceptions of BINARY, JSON, andYEAR. For syntax information and examples, as well as other relevant information, seeMulti-Valued Indexes.
Note
CONVERT(), unlikeCAST(), does_not_ support multi-valued index creation or the ARRAY keyword.
CAST() supports retrieval of aTIMESTAMP 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)  
mysql> CREATE TABLE tz (c TIMESTAMP);  
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)  
mysql> SELECT CAST(c AT TIME ZONE 'UTC' AS DATETIME(2)) AS u FROM tz;  
+------------------------+  
| 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 (seePopulating the Time Zone Tables).
AT TIME ZONE does not support theARRAY keyword, and is not supported by the CONVERT() function.

SELECT CONVERT('abc' USING utf8mb4);

CONVERT(expr,type) syntax (withoutUSING) takes an expression and a_type_ value specifying a result type, and produces a result value of the specified type. This operation may also be expressed asCAST(expr AS_type_), which is equivalent. For more information, see the description ofCAST().

Character Set Conversions

CONVERT() with aUSING clause converts data between character sets:

CONVERT(expr USING transcoding_name)

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

Examples:

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 character sets, you can also useCONVERT(expr,type) syntax (withoutUSING), orCAST(expr AS_type_), which is equivalent:

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

Examples:

SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);

If you specify CHARACTER SET_`charsetname`_ as just shown, the character set and collation of the result are_charsetname_ and the default collation of charsetname. If you omit CHARACTER SET_`charsetname`_, the character set and collation of the result are defined by thecharacter_set_connection andcollation_connection system variables that determine the default connection character set and collation (see Section 12.4, “Connection Character Sets and Collations”).

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

SELECT CONVERT('test' USING utf8mb4) COLLATE utf8mb4_bin;
SELECT CONVERT('test', CHAR CHARACTER SET 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 CONVERT('test', CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);
SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4 COLLATE utf8mb4_bin);

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 asCAST(), orCONVERT(), 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, seeSection 12.3.8, “Character Set Introducers”.

Character Set Conversions for String Comparisons

Normally, you cannot compare aBLOB value or other binary string in case-insensitive fashion because binary strings use thebinary character set, which has no collation with the concept of lettercase. To perform a case-insensitive comparison, first use theCONVERT() orCAST() function to convert the value to a nonbinary string. Comparisons of the resulting string use its collation. For example, if the conversion result collation is not case-sensitive, aLIKE 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 theCONVERT() 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 forutf8mb4 in the preceding statements (and similarly to use a different collation).

CONVERT() andCAST() 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 |
+---------------------------------+

Character set conversion is also useful preceding lettercase conversion of binary strings.LOWER() andUPPER() 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 apply BINARY,CAST(), orCONVERT() to an indexed column, MySQL may not be able to use the index efficiently.

Cast Operations on Spatial Types

CAST() andCONVERT() support casting geometry values from one spatial type to another, for certain combinations of spatial types. The following list shows the permitted type combinations, where “MySQL extension” designates casts implemented in MySQL beyond those defined in the SQL/MM standard:

In spatial casts, GeometryCollection andGeomCollection are synonyms for the same result type.

Some conditions apply to all spatial type casts, and some conditions apply only when the cast result is to have a particular spatial type. For information about terms such as“well-formed geometry,” seeSection 13.4.4, “Geometry Well-Formedness and Validity”.

General Conditions for Spatial Casts

These conditions apply to all spatial casts regardless of the result type:

Conditions for Casts to Point

When the cast result type is Point, these conditions apply:

Conditions for Casts to LineString

When the cast result type is LineString, these conditions apply:

Conditions for Casts to Polygon

When the cast result type is Polygon, these conditions apply:

Conditions for Casts to MultiPoint

When the cast result type is MultiPoint, these conditions apply:

Conditions for Casts to MultiLineString

When the cast result type is MultiLineString, these conditions apply:

Conditions for Casts to MultiPolygon

When the cast result type is MultiPolygon, these conditions apply:

Conditions for Casts to GeometryCollection

When the cast result type isGeometryCollection, these conditions apply:

Other Uses for Cast Operations

The cast functions are useful for creating a column with a specific type in aCREATE 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The cast functions are useful for sortingENUM columns in lexical order. Normally, sorting of ENUM columns occurs using the internal numeric values. Casting the values toCHAR 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 asCONCAT('Date: ',CAST(NOW() AS DATE)).

For temporal values, there is little need to useCAST() to extract data in different formats. Instead, use a function such asEXTRACT(),DATE_FORMAT(), orTIME_FORMAT(). SeeSection 14.7, “Date and Time Functions”.

To cast a string to a number, it normally suffices to 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 14.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 14.6.1, “Arithmetic Operators”). To override this, use the SIGNED orUNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer, respectively.

mysql> SELECT 1 - 2;
        -> -1
mysql> SELECT CAST(1 - 2 AS UNSIGNED);
        -> 18446744073709551615
mysql> SELECT CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED);
        -> -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 (seeSection 7.1.11, “Server SQL Modes”). Examples: