12.8. Cast Functions and Operators
-
BINARY
The 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 isn't defined as
BINARY or BLOB.
BINARY also causes trailing spaces to be
significant.
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.
BINARY str is
shorthand for CAST(str AS
BINARY).
Note that in some contexts, if you cast an indexed column to
BINARY, MySQL is not able to use the index
efficiently.
-
CAST(expr AS
type),
CONVERT(expr,type),
CONVERT(expr USING
transcoding_name)
The CAST() and CONVERT()
functions take a value of one type and produce a value of
another type.
The type can be one of the
following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
BINARY produces a string with the
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,
BINARY[N] causes
the cast to use no more than N
bytes of the argument. Values shorter than
N bytes are padded with
0x00 bytes to a length of
N.
CHAR[N] causes
the cast to use no more than N
characters of the argument.
CAST() and CONVERT(... USING
...) are standard SQL syntax. The
non-USING form of
CONVERT() is ODBC syntax.
CONVERT() with 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 'abc' in the default character set
to the corresponding string in the utf8
character set:
SELECT CONVERT('abc' USING utf8);
Normally, you cannot compare a BLOB 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
non-binary string. If the character set of the result has a
case-insensitive collation, the LIKE operation
is not case sensitive:
SELECT 'A' LIKE CONVERT(blob_col USING latin1) FROM tbl_name;
To use a different character set, substitute its name for
latin1 in the preceding statement. To ensure
that a case-insensitive collation is used, specify a
COLLATE clause following the
CONVERT() call.
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 ... SELECT
statement:
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 ENUM columns 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);
CAST(str AS BINARY)
is the same thing as BINARY
str.
CAST(expr AS CHAR)
treats the expression as a string with the default character set.
CAST() also changes the result if you use it as
part of a more complex expression such as CONCAT('Date:
',CAST(NOW() AS DATE)).
You should not use CAST() to extract data in
different formats but instead use string functions like
LEFT() or EXTRACT(). See
Section 12.5, “Date and Time Functions”.
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:
mysql> SELECT 1+'1';
-> 2
If you use a number in string context, the number automatically is
converted to a BINARY string.
mysql> 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 SIGNED and UNSIGNED cast
operators to cast the operation to a signed or unsigned 64-bit
integer, respectively.
mysql> 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, DECIMAL column values
are regarded as floating-point values.)
mysql> 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.
If you convert a “zero” date string to a date,
CONVERT() and CAST() return
NULL and produce a warning when the
NO_ZERO_DATE SQL mode is enabled.
