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
BINARY affects the entire comparison; it can be given before either operand with the same result.
BINARY was added in MySQL 3.23.0. As of MySQL 4.0.2, BINARY str is a shorthand for CAST(str AS BINARY).
Note that in some contexts, if you cast an indexed column to BINARY, MySQL will not be able to use the index efficiently.
If you want to compare a BLOB value in case-insensitive fashion, you can do so as follows:
Before MySQL 4.1.1, use the UPPER() function to convert the BLOB value to uppercase before performing the comparison:
SELECT 'A' LIKE UPPER(blob_col) FROM tbl_name;
If the comparison value is lowercase, convert the BLOB value using LOWER() instead.
For MySQL 4.1.1 and up, BLOB columns have a character set of binary, which has no concept of lettercase. To perform a case-insensitive comparison, use the CONVERT() function to convert the BLOB value to a character set that is not case sensitive. The result is a non-binary string, so 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.
CONVERT() can be used more generally for comparing strings that are represented in different character sets.
The CAST() and CONVERT() functions can be used to take a value of one type and produce a value of another type.
The type can be one of the following values:
BINARY
CHAR
DATE
DATETIME
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]
BINARY produces a binary string. See the entry for the BINARY operator in this section for a description of how this affects comparisons.
CAST() and CONVERT() are available as of MySQL 4.0.2. The CHAR conversion type is available as of 4.0.6. The USING form of CONVERT() is available as of 4.1.0.
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 server's default character set to the corresponding string in the utf8 character set:
SELECT CONVERT('abc' USING utf8);
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.
Note: In MysQL 4.0, a CAST() to DATE, DATETIME, or TIME only marks the column to be a specific type but doesn't change the value of the column.
As of MySQL 4.1.0, the value is converted to the correct column type when it's sent to the user (this is a feature of how the new protocol in 4.1 sends date information to the client):
mysql> SELECT CAST(NOW() AS DATE);
-> 2003-05-26
As of MySQL 4.1.1, 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 the section called “Date and Time Functions”.
To cast a string to a numeric value in numeric context, you don't normally have to do anything. Just 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.
The handing of unsigned values was changed in MySQL 4.0 to be able to support BIGINT values properly. If you have some code that you want to run in both MySQL 4.0 and 3.23, you probably can't use the CAST() function. You can use the following technique to get a signed result when subtracting two unsigned integer columns ucol1 and ucol2:
mysql> SELECT (ucol1+0.0)-(ucol2+0.0) FROM ...;
The idea is that the columns are converted to floating-point values before the subtraction occurs.
If you have a problem with UNSIGNED columns in old MySQL applications when porting them to MySQL 4.0, you can use the --sql-mode=NO_UNSIGNED_SUBTRACTION option when starting mysqld. However, as long as you use this option, you will not be able to make efficient use of the BIGINT UNSIGNED column type.