MariaDB and MySQL table name is case sensitive if it's running on case-sensitive systems such as in Linux and Unix. Windows however does not enforce case sensitivity for its folders and files causing MySQL and MariaDB table names in Windows to be case-insensitive.

This means that a lowercase table named tablename is just the same as uppercase TABLENAME or TableName and using any of these will not make any difference in your query. This is due to the fact that MySQL and MariaDB stores and query database tables based on the filesystem's filename and folder.

You can use case-insensitive table names for MySQL and MariaDB in Linux and other Unix systems or use case sensitive table names in Windows. by enabling lower_case_table_names option in the configuration file.

Steps to set case sensitivity for MySQL and MariaDB table names:

  1. Open MySQL or MariaDB configuration file using your favourite editor.
    $ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
  2. Find and set 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 to the followings as per your requirement.

    ValueDescription
    0Stored based on CREATE stetement and case sensitive
    1Stored in lowercase and not case sensitive
    2Stored based on CREATE stetement and not case sensitive
  3. Save configuration file and exit text editor.
  4. Restart MySQL or MariaDB service.
  5. Check if changes is successful.
    $ sudo mysqladmin -u root -p variables | grep lower_case_table_names
    Enter password: 
    | lower_case_table_names                                   | 1
Discuss the article:

Comment anonymously. Login not required.

Share!