Table 11.13. Cast Functions
Cast a string to a binary string
Cast a value as a certain type
Cast a value as a certain type
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.
typecan be one of the following values:
BINARYproduces a string with the
BINARYdata type. See Section 10.4.2, “The
VARBINARYTypes” 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.
USINGis 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
'abc'in the default character set to the corresponding string in the
SELECT CONVERT('abc' USING utf8);
Normally, you cannot compare a
BLOBvalue 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. If the character set of the result has a case-insensitive collation, the
LIKEoperation is not case sensitive:
SELECT 'A' LIKE CONVERT(
blob_colUSING latin1) FROM
To use a different character set, substitute its name for
latin1in the preceding statement. To ensure that a case-insensitive collation is used, specify a
COLLATEclause following the
CONVERT()can be used more generally for comparing strings that are represented in different character sets.
The cast functions are useful when you want to create a column with a specific type in a
CREATE TABLE ... SELECTstatement:
CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE);
The functions also can be useful for sorting
ENUMcolumns in lexical order. Normally, sorting of
ENUMcolumns occurs using the internal numeric values. Casting the values to
CHARresults 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 number in string context, the number automatically is converted to a
SELECT CONCAT('hello you ',2);-> 'hello you 2'
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. You can override this by using the
UNSIGNEDcast operators to cast the operation 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
Note that 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,
DECIMALcolumn values are regarded as floating-point values.)
SELECT CAST(1 AS UNSIGNED) - 2.0;-> -1.0
If you are using a string in an arithmetic operation, this is converted to a floating-point number.