Preserving the case of table names in MySql on Windows

Windows is case insensitive, so table “test” and “Test” refers to the same table. However if you want to preserve the case sensitivity of the your table names on a Windows machine, you should set the option lower_case_table_names to 2 in the MySql configuration file. You can check the value by running the following SQL command:

SHOW VARIABLES;

Scroll down to lower_case_table_names and the value will most likely be set to 1. Now to change that value, you will either have to modify my.ini or my.cfg file found in your installation of MySql (C:\Program Files\MySql\MySql Server 5.1 0r C:\Program Files (x86)\MySql\MySql Server 5.1). Try searching for lower_case_table_names and change the value to 2 or just append the following in the my.ini file:

lower_case_table_names = 2;

Now you will need to restart the MySql service for the changes to take effect. Note that tables which have previously been created won’t be affected by this change. Only new tables will preserve their case sensitivity rather than having their case lowered.

After making the change, if you still find that some tables are not preserving their case (same problem that happened to me), then you will need to do rename the table to something else and then back to its original name. Say you created a table called ‘Article’ and you find that it’s showing as ‘article’, then do the following:

RENAME TABLE `Article` to `Article2`;
RENAME TABLE `Article2` to `Article`;

This will solve the problem.

comments powered by Disqus