Table 12.14 Cast Functions and Operators
Cast functions and operators enable conversion of values from one data type to another.
CONVERT() with a
USING clause converts data between different
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;
CONVERT(string, CHAR[(N)] CHARACTER SET charset_name) CAST(string AS CHAR[(N)] CHARACTER SET charset_name)
SELECT CONVERT('test', CHAR CHARACTER SET utf8mb4); SELECT CAST('test' AS CHAR CHARACTER SET utf8mb4);
If you specify
as just shown,
the character set and collation of the result are
charset_name and the default collation
charset_name. If you omit
, the character
set and collation of the result are defined by the
variables that determine the default connection character set and
collation (see Section 10.4, “Connection Character Sets and Collations”).
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);
Normally, you cannot compare a
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
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_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
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).
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.
_latin2 in the preceding example are instances
of introducers. Unlike conversion functions such as
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.
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 | +-------------+------------------------------------+
The cast functions are useful for creating a column with a
specific type in a
CREATE TABLE ...
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
The cast functions are useful for sorting
ENUM columns in lexical order.
Normally, sorting of
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);
For temporal values, there is little need to use
CAST() to extract data in different
formats. Instead, use a function such as
Section 12.6, “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.2, “Type Conversion in Expression Evaluation”.
MySQL supports arithmetic with both signed and unsigned 64-bit
values. For numeric operators (such as
-) where one of the
operands is an unsigned integer, the result is unsigned by default
(see Section 12.5.1, “Arithmetic Operators”). To override this,
cast operator to cast a value to a signed or unsigned 64-bit
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,
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:
The following list describes the available cast functions and operators:
BINARYoperator converts the expression to a binary string (a string that has the
binarycharacter set and
binarycollation). A common use for
BINARYis to force a character string comparison to be done byte by byte using numeric byte values rather than character by character. The
BINARYoperator also causes trailing spaces in comparisons to be significant. For information about the differences between the
binarycollation of the
binarycharacter set and the
_bincollations 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,
BINARYaffects 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) CONVERT(expr USING BINARY)
If a value is a string literal, it can be designated as a binary string without performing any conversion by using the
_binarycharacter 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”.
BINARYoperator in expressions differs in effect from the
BINARYattribute in character column definitions. A character column defined with the
BINARYattribute is assigned the table default character set and the binary (
_bin) collation of that character set. Every nonbinary character set has a
_bincollation. 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 binaryin the definition of a
TEXTcolumn 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
In MySQL 8.0.17 and later,
InnoDBallows the use of an additional
ARRAYkeyword for creating a multi-valued index on a
JSONarray as part of
CREATE TABLE, and
ARRAYis 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
CONVERT()does not support multi-valued index creation or the
ASkeyword may specify any of the types supported by
CAST(), with the exceptions of
JSON. For syntax information and examples, as well as other relevant information, see Multi-Valued Indexes.
CAST()is standard SQL syntax.
CONVERT()function takes an expression of any type and produces a result value of the specified type.
USINGconverts 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
SELECT CONVERT('abc' USING utf8mb4);
Produces a string with the
BINARYdata type. For a description of how this affects comparisons, see Section 11.3.3, “The BINARY and VARBINARY Types”. If the optional length
BINARY(causes the cast to use no more than
Nbytes of the argument. Values shorter than
Nbytes are padded with
0x00bytes to a length of
Produces a string with the
CHARdata type. If the optional length
CHAR(causes the cast to use no more than
Ncharacters of the argument. No padding occurs for values shorter than
CHARproduces a string with the default character set. To specify the character set explicitly, these
charset_infovalues are permitted:
CHARACTER SET: 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.
DECIMALvalue. If the optional
Dvalues are given, they specify the maximum number of digits (the precision) and the number of digits following the decimal point (the scale).
DOUBLEresult. Added in MySQL 8.0.17.
If the precision
pis not specified, produces a result of type
pis provided and 0 <= <
p<= 24, the result is of type
FLOAT. If 25 <=
p<= 53, the result is of type
p< 0 or
p> 53, an error is returned. Added in MySQL 8.0.17.
CHAR, but produces a string with the national character set. See Section 10.3.7, “The National Character Set”.
NCHARdoes not permit trailing character set information to be specified.
Produces a result of type
REAL. This is actually
REAL_AS_FLOATSQL mode is enabled; otherwise the result is of type
Produces a signed integer value.
Produces an unsigned integer value.