The case sensitivity of a pattern match operation is like that of a string comparison. That is, it depends on whether the operands are binary or nonbinary strings, and for nonbinary strings, it depends on their collation. See Controlling Case Sensitivity in String Comparisons for discussion of how these factors apply to comparisons.
The default character set and collation are
latin1
and latin1_swedish_ci
, so pattern match
operations are not case-sensitive by default:
mysql>SELECT 'a' LIKE 'A', 'a' REGEXP 'A';
+--------------+----------------+
| 'a' LIKE 'A' | 'a' REGEXP 'A' |
+--------------+----------------+
| 1 | 1 |
+--------------+----------------+
Note that a REGEXP
operation that is not case-sensitive
can lead to some unintuitive results:
mysql>SELECT 'a' REGEXP '[[:lower:]]', 'a' REGEXP '[[:upper:]]';
+--------------------------+--------------------------+
| 'a' REGEXP '[[:lower:]]' | 'a' REGEXP '[[:upper:]]' |
+--------------------------+--------------------------+
| 1 | 1 |
+--------------------------+--------------------------+
Both expressions are true because [:lower:]
and [:upper:]
are equivalent when case
sensitivity doesn’t matter.
If a pattern match uses different case-sensitive behavior from what you want, control it the same way as for string comparisons: convert the strings to binary or nonbinary as necessary or change the collation of nonbinary strings.
To make a pattern match case-sensitive, use a case-sensitive
collation for either operand. For example, with the latin1
character set, use a collation of
latin1_general_cs
:
mysql>SET @s = 'a' COLLATE latin1_general_cs;
mysql>SELECT @s LIKE 'A', @s REGEXP 'A';
+-------------+---------------+ | @s LIKE 'A' | @s REGEXP 'A' | +-------------+---------------+ | 0 | 0 | +-------------+---------------+
Use of a case-sensitive collation also has the effect of causing
[:lower:]
and [:upper:]
in regular expressions to match
only lowercase and uppercase characters, respectively. The second
expression in the following statement yields a result that really is
true only for uppercase letters:
mysql>SET @s = 'a', @s_cs = 'a' COLLATE latin1_general_cs;
mysql>SELECT @s REGEXP '[[:upper:]]', @s_cs REGEXP '[[:upper:]]';
+-------------------------+----------------------------+ | @s REGEXP '[[:upper:]]' | @s_cs REGEXP '[[:upper:]]' | +-------------------------+----------------------------+ | 1 | 0 | +-------------------------+----------------------------+
3.16.66.156