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
utf8mb4
and
utf8mb4_0900_ai_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
utf8mb4
character set, you can use the
COLLATE
operator to cause either operand to
have the utf8mb4_0900_as_cs
or
utf8mb4_bin
collation:
Press CTRL+C to copycol_name COLLATE utf8mb4_0900_as_cs LIKE 'a%' col_name LIKE 'a%' COLLATE utf8mb4_0900_as_cs col_name COLLATE utf8mb4_bin LIKE 'a%' col_name LIKE 'a%' COLLATE utf8mb4_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 15.1.20, “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:
Press CTRL+C to copymysql> SET NAMES 'utf8mb4'; mysql> SET @s1 = 'MySQL' COLLATE utf8mb4_bin, @s2 = 'mysql' COLLATE utf8mb4_bin; mysql> SELECT @s1 = @s2; +-----------+ | @s1 = @s2 | +-----------+ | 0 | +-----------+ mysql> SELECT @s1 COLLATE utf8mb4_0900_ai_ci = @s2; +--------------------------------------+ | @s1 COLLATE utf8mb4_0900_ai_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:
Press CTRL+C to copymysql> SET @s = BINARY 'MySQL'; mysql> SELECT @s = 'mysql'; +--------------+ | @s = 'mysql' | +--------------+ | 0 | +--------------+ mysql> SELECT CONVERT(@s USING utf8mb4) COLLATE utf8mb4_0900_ai_ci = 'mysql'; +----------------------------------------------------------------+ | CONVERT(@s USING utf8mb4) COLLATE utf8mb4_0900_ai_ci = 'mysql' | +----------------------------------------------------------------+ | 1 | +----------------------------------------------------------------+
To determine whether a value is compared 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:
Press CTRL+C to copymysql> SELECT COLLATION(VERSION()); +----------------------+ | COLLATION(VERSION()) | +----------------------+ | utf8mb3_general_ci | +----------------------+
For binary strings, the collation value is
binary
, so comparisons are case sensitive.
One context in which you can expect to see
binary
is for compression functions, which
return binary strings as a general rule: string:
Press CTRL+C to copymysql> SELECT COLLATION(COMPRESS('x')); +--------------------------+ | COLLATION(COMPRESS('x')) | +--------------------------+ | binary | +--------------------------+
To check the sort value of a string, the
WEIGHT_STRING()
may be helpful.
See Section 14.8, “String Functions and Operators”.