DEVELOPER ZONE ::
Login / Register
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 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,
BINARY affects the entire operation; it can be given before either operand with the same result.
BINARY is shorthand for
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.
CONVERT() functions take a value of one type and produce a value of another type.
type can be one of the following values:
BINARY produces a string with the
BINARY data type. See Section 11.4.2, “The
VARBINARY Types” for a description of how this affects comparisons. If the optional length
N is given,
BINARY( causes the cast to use no more than
N bytes of the argument. As of MySQL 5.0.17, values shorter than
N bytes are padded with
0x00 bytes to a length of
CHAR( causes the cast to use no more than
N characters of the argument.
DECIMAL type is available as of MySQL 5.0.8.
CONVERT(... USING ...) are standard SQL syntax. The non-
USING form of
CONVERT() is ODBC syntax.
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_colUSING latin1) FROM
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() 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:
tbl_nameORDER BY CAST(
CAST( is the same thing as
str AS BINARY)
CAST( treats the expression as a string with the default character set.
expr AS CHAR)
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
EXTRACT(). See Section 12.6, “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:
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
UNSIGNED cast 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,
DECIMAL column 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.
If you convert a “zero” date string to a date,
NULL when the
NO_ZERO_DATE SQL mode is enabled. As of MySQL 5.0.4, they also produce a warning.