Mastering MySQL Case Sensitivity: Rules, Pitfalls, and 5 Practical Fixes

Background and OS Influence

In MySQL each database corresponds to a directory and each table to a file, so the file system’s case‑sensitivity determines whether database and table names are case‑sensitive. On Windows the file system is case‑insensitive, while most Unix/Linux file systems are case‑sensitive.

Linux Case‑Sensitivity Rules

Database and table names are strictly case‑sensitive.

Table aliases are strictly case‑sensitive.

Column names and column aliases are case‑insensitive.

Variable names are strictly case‑sensitive.

Windows Case‑Sensitivity Rules

All identifiers (database, table, column, alias, etc.) are case‑insensitive.

Key System Variables

The read‑only variable lower_case_file_system reflects the file system’s case handling. The configurable variable lower_case_table_names controls how MySQL stores and compares table names: 0: Store names as given; comparisons are case‑sensitive. 1: Store names in lowercase; comparisons are case‑insensitive. 2: Store names as given but compare in lowercase (applies to databases and aliases as well).

Recommended Naming Convention

To avoid case‑related problems, use all‑lowercase names with underscores for databases, tables, and columns, and avoid any uppercase letters.

Impact on Hibernate/JPA

Hibernate/JPA auto‑generates schemas based on Java class and field names, which often use camel‑case. This can clash with MySQL’s recommended lowercase naming. Teams typically agree on a convention; using MySQL’s lowercase naming avoids case‑sensitivity issues for DBAs, while Java‑style naming is friendlier for developers.

SQL Keywords and Function Names

Keywords (e.g., SELECT, WHERE, GROUP BY) and built‑in functions (e.g., ABS, NOW, VERSION) are case‑insensitive.

Names of Database Objects

Database, table, and view names follow the OS rules described above. Stored function, procedure, and event names are case‑insensitive; trigger names are case‑sensitive. Column and index names are case‑insensitive.

Collation and Case Sensitivity

Collations ending with _ci are case‑insensitive, _cs are case‑sensitive, and _bin are binary (strictly case‑sensitive). Example:

CREATE TABLE b(id VARCHAR(10)) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;

Inserting values ('A'),('a'),('B') and querying with LIKE 'a' returns only the lowercase row, demonstrating binary collation.

Five Practical Solutions

Configure MySQL to be case‑sensitive: Add lower_case_table_names=0 to my.cnf (Linux) or my.ini (Windows) and restart the server.

Use the BINARY attribute on columns: Define a column as VARCHAR(10) BINARY or alter an existing column with ALTER TABLE t MODIFY COLUMN name VARCHAR(10) BINARY; to make string comparisons case‑sensitive.

Set a binary collation for the table/column:

CREATE TABLE t(name VARCHAR(10)) DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_bin;

Use BINARY in queries: SELECT * FROM table WHERE field LIKE BINARY '%FIND%'; Change character set on Windows: In my.ini set default-character-set=gb2312 (or another appropriate charset) to influence case handling for multibyte characters.

Example Commands

mysql> SHOW VARIABLES LIKE 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+

-- After editing my.cnf and restarting
mysql> SET GLOBAL lower_case_table_names=1;  -- will fail if variable is read‑only
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

mysql> CREATE DATABASE Test;  -- fails if a case‑insensitive name already exists
ERROR 1007 (HY000): Can't create database 'test'; database exists

Conclusion

Understanding MySQL’s case‑sensitivity rules and the relevant system variables allows you to avoid confusing errors, choose an appropriate naming convention, and apply targeted fixes—whether through configuration, binary attributes, or collations—to meet the needs of your application and operating environment.