How to adjust MySQL or MariaDB table name case sensitivity

MySQL and MariaDB handle table name case sensitivity based on the underlying file system. On case-sensitive systems like Linux and Unix, table names are case sensitive, while on case-insensitive systems like Windows, table names are not.

This means that on a case-insensitive system, a table named tablename will be treated the same as TABLENAME or TableName. The reason for this behavior is that MySQL and MariaDB store and query database tables according to the filesystem's filename and folder case sensitivity.

You can configure MySQL and MariaDB to use case-insensitive table names on Linux and other Unix systems, or enable case-sensitive table names on Windows. This is done by modifying the lower_case_table_names option in the configuration file.

1. Open MySQL or MariaDB configuration file using your preferred test editor.

   $ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

   Related: Understanding MySQL and MariaDB configuration files

2. Locate and set the lower_case_table_names value in the [mysqld] section.

   lower_case_table_names=1

   Add the line if it doesn't already exist. Set the value according to your needs.

   Value	Description
   0	Stored based on CREATE statement and case sensitive
   1	Stored in lowercase and not case sensitive
   2	Stored based on CREATE statement and not case sensitive

3. Save the configuration file and exit the text editor.
4. Restart the MySQL or MariaDB service to apply the changes.

   Related: How to manage MySQL or MariaDB service in Linux

5. Check if the changes were successful.

   $ sudo mysqladmin -u root -p variables | grep lower_case_table_names
   Enter password: 
   | lower_case_table_names                                   | 1