Mysql – How to enforce case sensitive table and column names in MySql

case-sensitiveMySQLwindows

The thing is we are working on windows machine and once done we deploy the code on unix machine. The code works fine on windows but then in unix we get the error like 'no such table exists' after changing the table name in correct case it works fine in unix too. Actually, in windows there is no case sensitive table names by default but in unix they do have ( read that MySQL tables are actually files and in unix we have case sensitive file name but not in windows ). Workaround could be to create all tables again and lets have the table name in lowercase. Yes, we can do that too, thats fine.

But, still can we impose case sensitivity on table names in MySql ( windows machine ). If yes, then please let me know how to do that.

Best Answer

The setting is called lower_case_table_names. If you set it to 0, comparisons will be case sensitive.

However,

You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or Mac OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result.

making all table names lowercase across all systems (including Linux), ie. setting it to a value of 1, sounds like the better option:

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.

Related Topic