B.4.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
, 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_cs
col_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.18, “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 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:
mysql> SELECT COLLATION(VERSION());
+----------------------+
| COLLATION(VERSION()) |
+----------------------+
| utf8_general_ci |
+----------------------+
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 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.7, “String Functions and Operators”.