6.3.2.2 Case-Sensitivity

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 case-sensitive even if the column isn't defined as BINARY or BLOB:

mysql> SELECT "a" = "A";
        -> 1
mysql> SELECT BINARY "a" = "A";
        -> 0

BINARY string is a shorthand for CAST(string AS BINARY). See section 6.3.5 Cast Functions. BINARY was introduced in MySQL Version 3.23.0. Note that in some context MySQL will not be able to use the index efficiently when you cast an indexed column to BINARY.

If you want to compare a blob case-insensitively you can always convert the blob to upper case before doing the comparison:

SELECT 'A' LIKE UPPER(blob_col) FROM table_name;

We plan to soon introduce casting between different character sets to make string comparison even more flexible.

User Comments

Experimenting a bit with "column LIKE constant" on an indexed column
shows that it's been optimized as much as one would expect. Thus, having
a constant that starts with a non-wildcard gives you a join type of 'range'.
It's unclear what happens when there's a leading '_' followed by non-wildcards -
will mysql use the sub-ranges for each possible first character, or just search
all rows. The latter is what I'd guess, although I might be wrong.

The new BINARY cast is very useful when you need for different purposes the benefits of both case-independence and case- (and diacritic-) dependence. There seem to be two limitations, though.

There is no symmetric "NATIONAL" cast, so if you are going to sometimes need to do non-BINARY ("NATIONAL") searches, you have to define the field as non-BINARY in the first place and use the BINARY cast when you need it.

Searches using the BINARY cast on a non-BINARY field can also be slow in a big table - presumably because the indexes are non-binary. I can't at the moment see any way of getting the benefit of fast indexed searches in both BINARY and non-BINARY form without duplicating the field.

I needed to put the BINARY keyword after the WHERE
keyword for it to have the desired effect with a
REGEXP operation. Easy to infer, but not exactly
obvious from the example given here.

Add your own comment.