CODEWITHSUNDEEP
mysqlcase-sensitivemysql5
benstpierre
benstpierre

Reputation: 33591

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.

Upvotes: 232

Views: 214008

Answers (7)

Ranushka Lakmal Sankalpa
Ranushka Lakmal Sankalpa

Reputation: 931

In MySQL, the case sensitivity of table names depends on the operating system and the lower_case_table_names system variable:

  • Windows: MySQL is case-insensitive with table names by default, so table_name and Table_Name are treated the same.

  • Linux (including Ubuntu): MySQL is case-sensitive with table names by default, so table_name and Table_Name are considered different.

Solotion for this

Set lower_case_table_names=1 on your Ubuntu MySQL server: This makes MySQL treat table names as case-insensitive, like on Windows.

To change lower_case_table_names on Ubuntu

  1. Open the MySQL configuration file (/etc/mysql/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf).
  2. Add or modify the line lower_case_table_names=1
  3. Restart MySQL sudo service mysql restart

Upvotes: 0

NITHI_007
NITHI_007

Reputation: 11

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

Upvotes: 1

Raman Sahasi
Raman Sahasi

Reputation: 31901

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

Upvotes: 39

sendon1982
sendon1982

Reputation: 11314

  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.

Upvotes: 18

StephenLembert
StephenLembert

Reputation: 1534

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.

Upvotes: 119

CloudyMarble
CloudyMarble

Reputation: 37576

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.

Upvotes: 278

Oswald
Oswald

Reputation: 31685

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

Upvotes: 20

Related Questions