220

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
benstpierre
  • 32,833
  • 51
  • 177
  • 288

6 Answers6

263

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
CloudyMarble
  • 36,908
  • 70
  • 97
  • 130
  • 69
    This totally burned me as my code was working great on my local windows environment, but throwing exceptions when moved into production on linux!! Thanks! – benathon Feb 14 '13 at 21:10
  • 9
    There is one caveat to this answer, which is not mentioned in the documentation: InnoDB does not use file or directory names for databases and tables, and therefore *its* objects are *always* case insensitive, even when run on a case sensitive system. See this question for an example of what can go wrong because of this: http://stackoverflow.com/questions/23182969/innodb-complaining-about-foreign-key-name-in-use-when-restoring-database-after-d/23183479#23183479 – Jules Apr 20 '14 at 14:29
  • this is not the whole story. see StephenLembert's answer as it is configurable – Chris Wood Jun 08 '16 at 12:45
  • 1
    By default most mac computers use a case-insensitive file system. You can opt-in to have your file system be case-sensitive, though. – Chad Sep 29 '16 at 16:13
  • This and the file name case sensitivity is one of the reasons that led me to move to Ubuntu as a web developer. – Muhammad bin Yusrat Dec 14 '17 at 08:17
  • @MuhammadbinYusrat docker is your friend. – Lucio Mollinedo Aug 24 '22 at 03:29
119

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
StephenLembert
  • 1,534
  • 1
  • 8
  • 4
  • 1
    They are also not case sensitive on MacOS X, even though the underlying Unix is. This is presumably why autocomplete within MySQL on the Mac is case-sensitive for table or field names even though queries are not. – David Jan 14 '16 at 14:19
  • Yes, but there to put this statement? I guess it is on: /etc/mysql/my.cnf underneath [mysql] group. But this is not enough, still something else needs to be done (besides of course restart mysql... – Alg_D Feb 05 '16 at 12:22
  • 2
    This only affects new tables. Existing tables have to be renamed to lowercase before changing this setting. – Martin Apr 12 '17 at 10:08
  • where to set "lower_case_table_names=1" ? Someone mentioned somewhere that it needs to be done in my.cnf under /etc/mysql but that fails server restart. – Hemant Nagpal Jul 28 '21 at 19:01
  • you may find [mysqld] in the file named "/etc/mysql/mysql.conf.d/mysqld.cnf". edit that file and add the line lower_case_table_names=1 under [mysqld]. – Hemant Nagpal Jul 28 '21 at 19:38
33

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

shareef
  • 9,255
  • 13
  • 58
  • 89
Raman Sahasi
  • 30,180
  • 9
  • 58
  • 71
20

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

shA.t
  • 16,580
  • 5
  • 54
  • 111
Oswald
  • 31,254
  • 3
  • 43
  • 68
18

  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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
sendon1982
  • 9,982
  • 61
  • 44
  • 3
    If you get an error `Different lower_case_table_names settings for server ('1') and data dictionary ('0').` recreate the database/tables. – Jan Nov 09 '21 at 10:29
  • Hello there, if you guys are on Ubuntu and can't start the mysql service after the 3rd step. Use this: https://github.com/igrmk/ubuntu-reinit-mysql – destroyer22719 May 02 '23 at 15:59
1

Refer This [Doc][1]

Use lower_case_table_names=0 on Unix and lower_case_table_names=2 on Windows. [1]: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html

C:\Program Files\MySQL\MySQL Server X.X\my.ini -> Edit

Add the below line after [mysqld]

lower_case_table_names = 2

NITHI_007
  • 11
  • 2