How to configure MySQL and MariaDB table name case sensitivity

MySQL and MariaDB manage table name case sensitivity based on the operating system. On case-sensitive systems like Linux or Unix, table names are case sensitive, meaning `TableName`, `TABLENAME`, and `tablename` are treated as distinct tables. However, on case-insensitive systems like Windows, table names are not case sensitive, and all variations are treated the same.

This behavior is controlled by the lower_case_table_names configuration option. On Linux, the default setting makes table names case sensitive, while on Windows, they are case insensitive by default. This can lead to issues when migrating databases between systems with different case sensitivity behavior. Adjusting the lower_case_table_names setting can prevent these issues by ensuring consistent behavior across platforms.

You can modify the lower_case_table_names option to change the table name handling. This involves setting the option in the configuration file based on your specific needs. After configuring this option, the database will store table names either in lowercase or as defined, depending on the chosen setting. This ensures table name consistency when moving databases between case-sensitive and case-insensitive environments.

1. Open the MySQL or MariaDB configuration file using a text editor.

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

   Ensure you have root or sudo access to modify the configuration file.

   Related: Understanding MySQL and MariaDB configuration files

2. Find or add the lower_case_table_names option under 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 editor.
4. Restart the MySQL or MariaDB service to apply changes.

   $ sudo systemctl restart mysql

   Related: How to manage MySQL or MariaDB service in Linux

5. Verify the changes by checking the current value of lower_case_table_names.

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