SQL Case Sensitivity

“Is SQL case sensitive?” The honest answer is: it depends on what you are asking about. There are three separate cases.

1. Keywords: not case sensitive

SELECT, select and SeLeCt are the same.

1
2

select 'a' as lower_case;
SELECT 'a' AS UPPER_CASE;

1
2
3
4
5
6
7
8
9
10
11
12
13

+------------+
| lower_case |
+------------+
| a          |
+------------+
1 row in set

+------------+
| UPPER_CASE |
+------------+
| a          |
+------------+
1 row in set

Convention: write keywords in capitals and names in lowercase. It makes SQL easier to scan.

2. Text values: depends on the collation

When MySQL compares text, the collation decides whether a equals A. The default collation in MySQL 8 is utf8mb4_0900_ai_ci: accent-insensitive and case-insensitive.

1
2

SELECT 'Pune' = 'PUNE' AS same_by_default,
       'Pune' = 'PUNE' COLLATE utf8mb4_0900_as_cs AS same_when_case_sensitive;

1
2
3
4
5
6

+-----------------+--------------------------+
| same_by_default | same_when_case_sensitive |
+-----------------+--------------------------+
|               1 |                        0 |
+-----------------+--------------------------+
1 row in set

So a search for WHERE city = 'pune' also finds Pune and PUNE in a default MySQL setup. The collation utf8mb4_0900_as_cs is accent-sensitive and case-sensitive. A _bin collation compares raw bytes. Character Sets and Collations goes deeper.

3. Names of databases and tables: depends on the operating system

MySQL stores each database as a folder and each table as files. File names are case sensitive on Linux and not on Windows. So:

• Linux (default): Users and users are two different tables.
• Windows: they are the same table.
• macOS: usually the same table (case-insensitive file system).

The setting that controls this is lower_case_table_names. On Linux it is 0 by default, on Windows 1, and on macOS 2. You choose it when the server is first set up, and it must not be changed afterwards.

1

SELECT @@lower_case_table_names AS setting;

1
2
3
4
5
6

+---------+
| setting |
+---------+
|       0 |
+---------+
1 row in set

(On Linux this prints 0. On Windows it prints 1.)

Column names are never case sensitive in MySQL. Aliases are case sensitive on Linux only if they are table aliases.

The safe habit

• Write every database, table and column name in lowercase with underscores: order_items, created_at.
• Always use exactly the same case when you refer to a table.
• A project built on Windows and deployed to Linux fails with Table 'shop.Users' doesn't exist when the case differs. Lowercase names prevent that.