MySQL Case Sensitive Table Names Using Windows and Linux

This article describes the way I deal with case sensitive table names when running the same database on a windows as well as a linux MySQL server.

MySQL table names in Linux are case sensitive by default. This means that a query must also specify the table names with the appropriate capitalization.
Table names on a Windows MySQL server are not case sensitive.

When transferring a database dump from Linux to Windows the table names lose their capitalization. The queries will still run, because the capitalization of the table names in the queries will simply be ignored by Windows MySQL.

Problems arise when:

the database is transferred back from Windows to Linux, because the table names lost their capitalization
when the table names themselves are queried from the database in your application (e.g. your return value from queries is not columnName=value, but should be tableName.columnName=value and instead you get tablename.columnName=value)

First, the decision has to be made whether one wants to work with case sensitive table names or not in the application.

I decided to use case sensitive tables, because I simply prefer working with `tableName` to `tablename`.

To work with case sensitive tables, the following needs to be done:

Each time a database is transferred from Windows to Linux a script must be run that that recapitalizes the table names:
RENAME TABLE `tablename` TO `tableName`;
RENAME TABLE `categorylanguage` TO `categoryLanguage`;
RENAME TABLE `categorylocale` TO `categoryLocale`;

Each time the application requests a table name itself from the database, it must be translated to the proper capitalization. I use the following code in PHP to do that:

class DataBaseTableNameUpperCase
    {
    private $hmTableLowerToUpper = array();
    	
    public function __construct()
        {
        $this->hmTableLowerToUpper = array(
            'tablename' => 'tableName',
            'categorylanguage' => 'categoryLanguage',
            'categorylocale' => 'categoryLocale'
            );
        }
    	
    public function tableNameToUpperCase($tableName)
        {
        $tableName = strtolower($tableName);
        if(isset($this->hmTableLowerToUpper[$tableName]))
            {
            return $this->hmTableLowerToUpper[$tableName];
            }
        $oRegistry = Registry::getInstance();
        $oLog = $oRegistry->get('Log');
        $oLog->logError('DataBaseTableNameUpperCase missing ' . $tableName);
        return $tableName;
        }
    }

To work with lowercase table names in the application:

either rename all your table names in Linux to lowercase
or make the Linux MySQL server case insensitive:
lower_case_table_names =1 in the MySQL configuration

Last modification: September 6, 2019