Table 12.14 Cast Functions
BINARY operator casts the
string following it to a binary string. This is an easy way to
force a column comparison to be done byte by byte rather than
character by character. This causes the comparison to be case
sensitive even if the column is not defined as
BINARY also causes trailing
spaces to be significant.
SELECT 'a' = 'A';-> 1 mysql>
SELECT BINARY 'a' = 'A';-> 0 mysql>
SELECT 'a' = 'a ';-> 1 mysql>
SELECT BINARY 'a' = 'a ';-> 0
In a comparison,
the entire operation; it can be given before either operand
with the same result.
Note that in some contexts, if you cast an indexed column to
BINARY, MySQL is not able to use the index
type for the result can be one
of the following values:
BINARY produces a string with
BINARY data type. See
Section 11.4.2, “The BINARY and VARBINARY Types” for a description of how
this affects comparisons. If the optional length
N is given,
the cast to use no more than
bytes of the argument. Values shorter than
N bytes are padded with
0x00 bytes to a length of
causes the cast to use no more than
N characters of the argument.
USING is used to convert 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
the default character set to the corresponding string in the
utf8 character set:
SELECT CONVERT('abc' USING utf8);
Normally, you cannot compare a
value or other binary string in case-insensitive fashion because
binary strings have no character set, and thus no concept of
lettercase. To perform a case-insensitive comparison, use the
CONVERT() function to convert the
value to a nonbinary string. Comparisons of the result use the
string collation. For example, if the character set of the result
has a case-insensitive collation, a
LIKE operation is not case sensitive:
SELECT 'A' LIKE CONVERT(
blob_colUSING latin1) FROM
To use a different character set, substitute its name for
latin1 in the preceding statement. To specify a
particular collation for the converted string, use a
COLLATE clause following the
CONVERT() call, as described in
Section 10.1.9.2, “CONVERT() and CAST()”. For example, to use
SELECT 'A' LIKE CONVERT(
blob_colUSING latin1) COLLATE latin1_german1_ci FROM
CONVERT() can be used more
generally for comparing strings that are represented in different
SET @str = BINARY 'New York';mysql>
SELECT LOWER(@str), LOWER(CONVERT(@str USING latin1));+-------------+-----------------------------------+ | LOWER(@str) | LOWER(CONVERT(@str USING latin1)) | +-------------+-----------------------------------+ | New York | new york | +-------------+-----------------------------------+
The cast functions are useful when you want to create a column
with a specific type in a
CREATE TABLE ...
CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);
The functions also can be 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:
tbl_nameORDER BY CAST(
To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:
SELECT 1+'1';-> 2
If you use a string in an arithmetic operation, it is converted to a floating-point number during expression evaluation.
If you use a number in string context, the number automatically is converted to a string:
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. If you are using numeric operators (such as
-) and one of the
operands is an unsigned integer, the result is unsigned by default
(see Section 12.6.1, “Arithmetic Operators”). You can override
this by using the
UNSIGNED cast operator to cast a value to a
signed or unsigned 64-bit integer, respectively.
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.)
SELECT CAST(1 AS UNSIGNED) - 2.0;-> -1.0
The SQL mode affects the result of conversion operations. Examples:
If you convert a “zero” date string to a date,
NULL and produce a warning if strict SQL
mode is enabled (as of MySQL 5.7.4) or the
NO_ZERO_DATE mode is enabled
(before MySQL 5.7.4).
For integer subtraction, if the
mode is enabled, the subtraction result is signed even if any
operand is unsigned.
For more information, see Section 5.1.7, “Server SQL Modes”.