Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using Ubuntu 13.10 and MySQL 5.6 and I know database name and table name are case sensitive in Ubuntu (and some other *nix environments) by default.

Now, I want to make MySQL work as case insensitive in Ubuntu.

Is it possible? If yes, how can I do it?

share|improve this question

migrated from stackoverflow.com Feb 22 '14 at 9:31

This question came from our site for professional and enthusiast programmers.

1  
I don't think you can. See dev.mysql.com/doc/refman/5.0/en/… – Barmar Feb 22 '14 at 6:37
    
stackoverflow.com/questions/11165944/… – Javier Feb 22 '14 at 6:41
    
even if you can, my instinct is that it's not a good idea.. – Thufir Mar 8 '15 at 0:36

Open terminal and edit /etc/mysql/my.cnf

sudo nano /etc/mysql/my.cnf

Underneath the [mysqld] section.add:

lower_case_table_names = 1

Restart mysql

sudo /etc/init.d/mysql restart

Then check it here:

mysqladmin -u root -p variables
share|improve this answer
1  
Worked for me, however I had to drop the database before making this change and re-import after the change. – DanH Aug 5 '14 at 13:56
2  
Worked like a charm. For me, it doesn't need to drop database and re-import after that. – StackOverflowError Jan 13 '15 at 8:28
    
This doesn't answer the question: how to make mysql case insensitive in Linux – ACV Oct 23 '15 at 12:00
1  
@ACV this particular answer precisely addresses the question published. I can see a reason why such a question would be asked. I had a client who had a production DB in Linux and had to backport the data into Windows. Foreign Key Constraints were not working in the MySQL DB due to case sensitivity of column names. Thus, came the first time I ever had to change lower_case_table_names on a Linux system. The final recommendation you posted from the Wiki does not attempt to address data migrations between case-sensitive and case-insensitive operating systems or at least fully explain it. – RolandoMySQLDBA Oct 23 '15 at 15:47
    
@RolandoMySQLDBA, you are right. – ACV Oct 26 '15 at 8:09

From here: Case insensitive table names

It says:

So, the final recommendation is to leave the lower_case_table_names variable unaltered (i.e. its default value), which is 0 on Linux-based systems and 2 on Windows and write table names to a lettercase that suits you best (both at creation as well as at query time).

If you are moving from Windows to Linux, then set it to 2.

Case insensitive table names

In MySQL, databases correspond to directories within the data directory (default is /var/lib/mysql. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix or Linux.

How table and database names are stored on disk and used in MySQL is affected by the lower_case_table_names system variable which can be set in the config when starting up your MySQL server. Possible values and their meaning are presented in the table below:

Value Description 0 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. Note that if you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive filesystem and access MyISAM tablenames using different lettercases, index corruption may result. 1 Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. 2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on filesystems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1. Thus, returning to our problem, i.e. having case insensitive table names in MySQL under Linux, the bad news is ... you cannot. The closest solution to this would be to set lower_case_table_names=1, which would make all your tables lowercase, no matter how you write them. The main disadvantage however is that when you use SHOW TABLES or SHOW DATABASES, you do not see the names in their original lettercase, but always in lowercase.I f you plan to set the lower_case_table_names system variable to 1 on Linux, you must first convert your old database and table names to lowercase before stopping mysqld and restarting it with the new variable setting.

So, the final recommendation is to leave the lower_case_table_names variable unaltered (i.e. its default value), which is 0 on Linux-based systems and 2 on Windows and write table names to a lettercase that suits you best (both at creation as well as at query time).

share|improve this answer

database and table names are not case sensitive on some platforms, you should not refer to a given database or table using different cases within the same statement.... it doesnt matter you write in which case..the following statement would not work because it refers to a table both as my_table and as MY_TABLE: mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

share|improve this answer

Unless you can mount your database directory on a case-insensitive filesystem, I don't think you can do it. MySQL table names map directly to file names; if the filesystem is case-sensitive, the table names are. See

9.2.2 Identifier Case Sensitivity

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.