B.5.4.1 Case Sensitivity in String Searches

For nonbinary strings (CHAR,
VARCHAR,
TEXT), string searches use the
collation of the comparison operands. For binary strings
(BINARY,
VARBINARY,
BLOB), comparisons use the
numeric values of the bytes in the operands; this means that
for alphabetic characters, comparisons will be case sensitive.

A comparison between a nonbinary string and binary string is
treated as a comparison of binary strings.

Simple comparison operations (>=, >, =, <,
<=, sorting, and grouping) are based on each
character's “sort value.” Characters with the
same sort value are treated as the same character. For
example, if e and
é have the same sort value in a
given collation, they compare as equal.

The default character set and collation are
latin1 and
latin1_swedish_ci, so nonbinary string
comparisons are case insensitive by default. This means that
if you search with
col_name LIKE
'a%', you get all column values that start with
A or a. To make this
search case sensitive, make sure that one of the operands has
a case sensitive or binary collation. For example, if you are
comparing a column and a string that both have the
latin1 character set, you can use the
COLLATE operator to cause either operand to
have the latin1_general_cs or
latin1_bin collation:

To cause a case-sensitive comparison of nonbinary strings to
be case insensitive, use COLLATE to name a
case-insensitive collation. The strings in the following
example normally are case sensitive, but
COLLATE changes the comparison to be case
insensitive:

To determine whether a value will compare as a nonbinary or
binary string, use the
COLLATION() function. This
example shows that VERSION()
returns a string that has a case-insensitive collation, so
comparisons are case insensitive:

For binary strings, the collation value is
binary, so comparisons will be case
sensitive. One context in which you will see
binary is for compression and encryption
functions, which return binary strings as a general rule:
string: