MySQL case insensitive table and column names

From Cosmin's Wiki

Jump to: navigation, search

Home > MySQL > MySQL case insensitive table and column names



Well, this is quite an issue ... at least for those coming from the Windows world (and/or Microsoft SQL Server). Why? Because Windows is not case sensitive (by default). Thus, I guess that many of you would create tables like this

CREATE TABLE TABLE1

so that it looks nice when you look at a listing of all tables, but then you would want to query your tables like this:

SELECT * FROM table1

Ok so far. Now you've moved to MySQL hosted on Linux (Debian in my case), and surprise, this doesn't work anymore. Let's see what can be done about this.


Case insensitive column names

Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Trigger names are case sensitive, which differs from standard SQL.

Well, this is good news. At least with column names or aliases we are good to go. Same for stored routines.


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).