MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 are 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
, you get all column values that start with
col_name
LIKE
'a%'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:
col_name
COLLATE latin1_general_cs LIKE 'a%'col_name
LIKE 'a%' COLLATE latin1_general_cscol_name
COLLATE latin1_bin LIKE 'a%'col_name
LIKE 'a%' COLLATE latin1_bin
If you want a column always to be treated in case-sensitive fashion, declare it with a case-sensitive or binary collation. See Section 13.1.17, “CREATE TABLE Statement”.
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:
mysql>SET @s1 = 'MySQL' COLLATE latin1_bin,
->@s2 = 'mysql' COLLATE latin1_bin;
mysql>SELECT @s1 = @s2;
+-----------+ | @s1 = @s2 | +-----------+ | 0 | +-----------+ mysql>SELECT @s1 COLLATE latin1_swedish_ci = @s2;
+-------------------------------------+ | @s1 COLLATE latin1_swedish_ci = @s2 | +-------------------------------------+ | 1 | +-------------------------------------+
A binary string is case-sensitive in comparisons. To compare
the string as case-insensitive, convert it to a nonbinary
string and use COLLATE
to name a
case-insensitive collation:
mysql>SET @s = BINARY 'MySQL';
mysql>SELECT @s = 'mysql';
+--------------+ | @s = 'mysql' | +--------------+ | 0 | +--------------+ mysql>SELECT CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'mysql';
+--------------------------------------------------------------+ | CONVERT(@s USING latin1) COLLATE latin1_swedish_ci = 'mysql' | +--------------------------------------------------------------+ | 1 | +--------------------------------------------------------------+
To determine whether a value compares 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:
mysql> SELECT COLLATION(VERSION());
+----------------------+
| COLLATION(VERSION()) |
+----------------------+
| utf8_general_ci |
+----------------------+
For binary strings, the collation value is
binary
, so comparisons are case-sensitive.
One context in which you may see binary
is
for compression functions, which return binary strings as a
general rule: string:
mysql> SELECT COLLATION(COMPRESS('x'));
+--------------------------+
| COLLATION(COMPRESS('x')) |
+--------------------------+
| binary |
+--------------------------+
To check the sort value of a string, the
WEIGHT_STRING()
may be helpful.
See Section 12.8, “String Functions and Operators”.