lower_case_table_names

• See SkySQL details in MariaDB SkySQL previous release

• See SkySQL details in MariaDB SkySQL new release

Case-Sensitive Names

When the lower_case_tables_names system variable is set to 0 (the default on Unix-based systems), table names, table aliases, and database names are compared in a case-sensitive manner:

SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SELECT * FROM tab;

Empty set (0.000 sec)

SELECT * FROM TAB;

ERROR 1146 (42S02): Table 'TAB' doesn't exist

Case-Insensitive Names

When the lower_case_tables_names system variable is set to 1 (the default on Windows), table names and database names are stored in lowercase and compared in a case-insensitive manner:

SHOW GLOBAL VARIABLES LIKE 'lower_case_table_names';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+

CREATE OR REPLACE TABLE tab (
   a INT PRIMARY KEY,
   b VARCHAR(50)
);

SELECT * FROM tab;

Empty set (0.000 sec)

SELECT * FROM TAB;

Empty set (0.000 sec)

Special Mode for Case-Insensitive File Systems

When the lower_case_table_names system variable is set to 2, the server uses a special mode for case-insensitive file systems. In this mode, table names and database names are stored as declared, but they are compared in lowercase.

If the lower_case_table_names system variable is set to 0 and the server is using a case-insensitive file system, the value is automatically set to 2. In that case, the following error will be written to the error log:

[Warning] Setting lower_case_table_names=2 because file system for /var/lib/mysql/ is case insensitive

For example, this would happen on Mac OS X when using HFS+ or APFS, except when APFS is used in case-sensitive mode.

If the lower_case_table_names system variable is set to 2 and the server is using a case-sensitive file system, the value is automatically set to 0. In that case, the following error will be written to the error log:

[Warning] lower_case_table_names was set to 2, even though your the file system '/var/lib/mysql/' is case sensitive.
   Now setting lower_case_table_names to 0 to avoid future problems.

For example, this would usually happen on Linux, since it almost always uses a case-sensitive file system.