MySQL case sensitivity

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →

A MySQL string comparison ignores letter case unless you ask otherwise. That surprises people once and then bites them later, so it is worth knowing what decides it and how to change it for one query. Open the client with mysql -u root -p sakila before you start.

How case sensitivity works

Nothing in your query decides it. A collation decides it, and every text column carries one. A character set says which characters a column can hold, and its collation says how to compare them. You override the collation for a single comparison with COLLATE:

SELECT COLUMN_LIST
FROM TABLE_NAME
WHERE COLUMN_NAME COLLATE COLLATION_NAME = 'SEARCH_TEXT';

Replace COLLATION_NAME with the collation you want the comparison to use, and SEARCH_TEXT with the value to match.

Examples

1) 2 strings that differ only in case are equal

SELECT 'ABC' = 'abc' AS same;

+------+
| same |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

2) The same holds against a column

A lower case search term finds an upper case title.

SELECT title FROM film WHERE title = 'academy dinosaur';

+------------------+
| title            |
+------------------+
| ACADEMY DINOSAUR |
+------------------+
1 row in set (0.00 sec)

3) Read the column’s collation

SELECT COLUMN_NAME, DATA_TYPE, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'film' AND COLUMN_NAME = 'title';

+-------------+-----------+--------------------+
| COLUMN_NAME | DATA_TYPE | COLLATION_NAME     |
+-------------+-----------+--------------------+
| title       | varchar   | utf8mb4_0900_ai_ci |
+-------------+-----------+--------------------+
1 row in set (0.01 sec)

Read a collation name from the end. ci is case-insensitive, which is why example 2 matched. ai is accent-insensitive, and it does separate work: under this collation 'e' = 'é' is true, and under utf8mb4_0900_as_cs it is false.

Ending	The comparison
`_ci`	ignores letter case
`_cs`	respects letter case
`_ai`	ignores accents
`_as`	respects accents
`_bin`	respects both, by comparing the stored values directly

That table is what your own column’s name means too. Run the query above against any table and column to see which rules you are working under.

4) Ask for a case-sensitive comparison

SELECT 'ABC' = 'abc' COLLATE utf8mb4_0900_as_cs AS case_sensitive,
       'ABC' = 'abc' COLLATE utf8mb4_0900_ai_ci AS case_insensitive;

+----------------+------------------+
| case_sensitive | case_insensitive |
+----------------+------------------+
|              0 |                1 |
+----------------+------------------+
1 row in set (0.00 sec)

It works on a column the same way.

SELECT title FROM film WHERE title COLLATE utf8mb4_0900_as_cs = 'academy dinosaur';

Empty set (0.00 sec)

COLLATE on a column gives up the index lookup

The index on a column is built in that column’s own collation, so a comparison in a different one cannot use it to find the matching values. EXPLAIN on the query above reports a scan of all 1,000 rows rather than a lookup. That costs nothing on 1,000 films and a great deal on a large table. When you need case-sensitive matching on every query, the Character sets and collations guide covers storing the column that way instead.

Where the default comes from

A database created without a collation takes the server’s, a table takes the database’s, and a column takes the table’s. So a column with no collation named anywhere in its history is comparing case-insensitively because of a server setting. @@ in front of a name reads a server setting, and this pair tells you which one you inherited:

SELECT @@collation_server AS server_collation, @@character_set_server AS server_charset;

Your server answers with whatever it was started with, so treat the result as a fact about your own installation rather than about MySQL.

Identifiers follow a different rule

Column names are never case-sensitive. Database and table names are a separate question, decided by lower_case_table_names:

Value	Database and table names
`0`	stored as written and compared case-sensitively
`1`	stored in lower case and compared case-insensitively
`2`	stored as written and compared case-insensitively

Run SELECT @@lower_case_table_names; to see where you stand. You cannot change it from a session: SET GLOBAL lower_case_table_names=1 answers ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable. It is a startup setting, tied to how the server was first set up, so treat the answer as a property of the server you are on. The practical advice does not depend on the answer. Write every database and table name in one case and use that spelling everywhere, so that moving the same SQL between 2 servers cannot turn a working query into ERROR 1146 (42S02), the error for a table that does not exist.

Summary

Expect a string comparison to ignore case, because the usual collation ends _ci.
Read a column’s collation from INFORMATION_SCHEMA.COLUMNS.
Read a collation name from the end: _ci and _ai ignore, _cs, _as and _bin respect.
Use COLLATE for a one-off case-sensitive comparison, and accept that it gives up the index lookup.
Keep one spelling for every database and table name, whatever lower_case_table_names says.

Getting Started

Server Development

Using Extensions

Creating Extensions

VillageSQL is a drop-in replacement for MySQL with extensions.

How case sensitivity works

Examples

1) 2 strings that differ only in case are equal

2) The same holds against a column

3) Read the column’s collation

4) Ask for a case-sensitive comparison

COLLATE on a column gives up the index lookup

Where the default comes from

Identifiers follow a different rule

Summary

See also

VillageSQL is a drop-in replacement for MySQL with extensions.

​How case sensitivity works

​Examples

​1) 2 strings that differ only in case are equal

​2) The same holds against a column

​3) Read the column’s collation

​4) Ask for a case-sensitive comparison

​COLLATE on a column gives up the index lookup

​Where the default comes from

​Identifiers follow a different rule

​Summary

​See also

How case sensitivity works

Examples

1) 2 strings that differ only in case are equal

2) The same holds against a column

3) Read the column’s collation

4) Ask for a case-sensitive comparison

COLLATE on a column gives up the index lookup

Where the default comes from

Identifiers follow a different rule

Summary

See also