Question
In regards to MySQL / MariaDB databases, how does the "lower_case_tables_names" option work?
Can the database names and tables be made case-sensitive?
Does the option display the uppercase and lowercase properly?
Answer
With MySQL/MariaDB database, there is an option called "lower_case_table_names".
The "lower_case_tables_names" option must be enabled when MySQL runtime, and can not be changed while MySQL is running.
To change the value, you would need to modify the configuration file. The configuration file is normally located on the filesystem here:
/etc/my.cnf
The "lower_case_table_names" option will need to be under the following section:
[mysqld]
lower_case_tables_names = 0
To learn what your current value is, use the following command via SSH as 'root', or the WHM: Terminal Feature:
Command:
# mysqladmin variables | grep -i lower_case_table_names
| lower_case_table_names | 0
There are three values listed below. These show how case sensitivity works based on the assigned value.
Default on Unix-based systems:
lower_case_table_names = 0
The "0" value:
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or macOS).
Default on Windows:
lower_case_table_names = 1
The "1" value:
Table names are stored in lowercase on disk and name comparisons are not case-sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases.
Default on Mac OS X:
lower_case_table_names = 2
The "2" value:
Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case-sensitive.
For more information regarding case sensitivity in regards to MySQL/MariaDB, use the following links to their documentation regarding the subject.
MariaDB - Identifier Case-sensitivity
MySQL 8 - 9.2.3 Identifier Case Sensitivity
MySQL 5.7 - 9.2.3 Identifier Case Sensitivity