MySQL Case Sensitivity Differences Between Linux and Windows

This will be a short one today as I have been dealing with both hardware and software problems for the last few days.

Windows vs Linux Case Sensitivity

If you often work with MySQL on both windows and linux you may find yourself getting tripped up by a little difference between the way Windows and Linux MySQL handle case sensitivity. On MySQL databases running on Linux table names are cases sensitive, while on Windows table names are case insensitive and normally forced lowercase.

This can potentially cause a problem if you are developing an MySQL driven application on a windows box (ie your laptop) but intend to deploy it to a Linux box.

Changing MySQL Case Sensitivity on Windows

Resolving this is actually very simple. All you need to do is edit the my.cnf file and set/add the following entry. For my default mysql install "my.cnf" was located at "c:\program files\mysql\mysql server[version]\my.cnf"

#Ensure that table names are stored case sensitive and that queries are case sensitive when naming a table
lower_case_table_names=0

Lower Case Levels

The above statement has three possible settings 0,1 & 2. Below is a brief explanation of each.

For more information visit the MySQL Docs.