> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL case sensitivity > Why a MySQL string comparison ignores letter case, how to read the collation that decides it, and how to ask for a case-sensitive match instead. 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`: ```sql theme={null} 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 ```sql theme={null} SELECT 'ABC' = 'abc' AS same; ``` ```text theme={null} +------+ | 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. ```sql theme={null} SELECT title FROM film WHERE title = 'academy dinosaur'; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACADEMY DINOSAUR | +------------------+ 1 row in set (0.00 sec) ``` ### 3) Read the column's collation ```sql theme={null} SELECT COLUMN_NAME, DATA_TYPE, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'film' AND COLUMN_NAME = 'title'; ``` ```text theme={null} +-------------+-----------+--------------------+ | 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 ```sql theme={null} SELECT 'ABC' = 'abc' COLLATE utf8mb4_0900_as_cs AS case_sensitive, 'ABC' = 'abc' COLLATE utf8mb4_0900_ai_ci AS case_insensitive; ``` ```text theme={null} +----------------+------------------+ | case_sensitive | case_insensitive | +----------------+------------------+ | 0 | 1 | +----------------+------------------+ 1 row in set (0.00 sec) ``` It works on a column the same way. ```sql theme={null} SELECT title FROM film WHERE title COLLATE utf8mb4_0900_as_cs = 'academy dinosaur'; ``` ```text theme={null} 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](/docs/guides/character-sets) 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: ```sql theme={null} 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. ## See also * [LIKE](/docs/tutorial/like) — where this shows up first * [REGEXP](/docs/tutorial/regexp) — the same rule, with a different override * [Character sets and collations in MySQL](/docs/guides/character-sets) — choosing one for a column * [Using utf8mb4 in MySQL](/docs/guides/utf8mb4) — the character set behind the usual collation