Are table names in MySQL case sensitive?

mysql case-sensitive mysql5
benstpierre picture benstpierre · May 26, 2011 · Viewed 140.2k times · Source

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.

Answer

CloudyMarble picture CloudyMarble · May 26, 2011

In general:

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

In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory. Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names.

One can configure how tables names are stored on the disk using the system variable lower_case_table_names (in the my.cnf configuration file under [mysqld]).

Read the section: 10.2.2 Identifier Case Sensitivity for more information.

Related questions

How can I make SQL case sensitive string comparison on MySQL?

I have a function that returns five characters with mixed case. If I do a query on this string it will return the value regardless of case. How can I make MySQL string queries case sensitive?

Read More
MySQL case sensitive query

This has been asked on this site before but I couldn't find a sufficient answer. If I'm doing a query like: Select Seller from Table where Location = 'San Jose' How can I make it return only Sellers with Location 'San …

Read More
How to force case sensitive table names?

I'm working on a MySQL database in windows. I must move it into a Linux environment. MySQL database contains a lot of tables and stored procedures which are CASE SENSITIVE. When I backup my database, all table names are forced …

Read More