Home

MySQL and case-sensitivity

Thu, 2008.03.13 - 10:39 — müzso
The case-sensitivity of MySQL's string comparison functions (and operators) depends on the collation used in the database+tables of the query and the query itself. Usually MySQL setups (eg. in linux distributions) have a case-insensitive default collation (something ending with "_ci", eg. utf8_general_ci).

In this case every string comparison (eg. the = operator, or using LIKE or IN) is going to be case-insensitive. If your table has a case-insensitive collation and you want to force a case-sensitive comparison in your query, you've two options: use the BINARY word in your WHERE expression or convert the table's column to a case-sensitive collation.
Eg. the following query will select "Agatha", but not "admin":
SELECT
  u.*
FROM
  `users` u
WHERE BINARY
  AND u.`first_name` LIKE 'A%'

And similiarily will do this:
SELECT
  u.*
FROM
  `users` u
WHERE u.`first_name`COLLATE utf8_bin LIKE 'A%'

In the second form you've to use a collation name that is case-sensitive. For a list of available collations use the following statement:
show collation
Or to get a list of all utf8 collations:
show collation like 'utf8%'