Table 12.14 Cast Functions
BINARYoperator 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
BINARYalso 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,
BINARYaffects 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 efficiently.
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 utf8);
typefor the result can be one of the following values:
BINARYproduces a string with the
BINARYdata type. See Section 11.4.2, “The BINARY and VARBINARY Types” for a description of how this affects comparisons. 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
CHAR(causes the cast to use no more than
Ncharacters of the argument.
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:
For more information, see Section 5.1.7, “Server SQL Modes”.