[mysql] Are table names in MySQL case sensitive?

Are table names in MySQL case sensitive?

On my Windows development machine the code I have is able to query my tables which appear to be all lowercase. When I deploy to the test server in our datacenter the table names appear to start with an uppercase letter.

The servers we use are all on Ubuntu.

This question is related to mysql case-sensitive mysql5

The answer is


  1. Locate the file at /etc/mysql/my.cnf

  2. Edit the file by adding the following lines:

     [mysqld]
    
     lower_case_table_names=1
    
  3. sudo /etc/init.d/mysql restart

  4. Run mysqladmin -u root -p variables | grep table to check that lower_case_table_names is 1 now

You might need to recreate these tables to make it work.


Table names in MySQL are file system entries, so they are case insensitive if the underlying file system is.


It depends upon lower_case_table_names system variable:

show variables where Variable_name='lower_case_table_names'

There are three possible values for this:

  • 0 - lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive.
  • 1 - Table names are stored in lowercase on disk and name comparisons are not case sensitive.
  • 2 - lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive.

Documentation


Database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux.

To resolve the issue, set the lower_case_table_names to 1

lower_case_table_names=1

This will make all your tables lowercase, no matter how you write them.


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to case-sensitive

Are PostgreSQL column names case-sensitive? How can I convert uppercase letters to lowercase in Notepad++ How do I commit case-sensitive only filename changes in Git? In VBA get rid of the case sensitivity when comparing words? Changing capitalization of filenames in Git How to compare character ignoring case in primitive types Contains case insensitive Should URL be case sensitive? MySQL case sensitive query Are table names in MySQL case sensitive?

Examples related to mysql5

MySQL 1062 - Duplicate entry '0' for key 'PRIMARY' Are table names in MySQL case sensitive? How to get a list of user accounts using the command line in MySQL? Can't connect to MySQL server on 'localhost' (10061)