Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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.

Level Description Default for OS
0 Database and Table names are stored case sensitive. Tables names in queries are also case sensitive. Linux
1 Database and Table names are stored lowercase. Table names in queries are not case sensitive (converted to lowercase on execution). Windows
2 Database and Table names are stored case sensitive. Tables names in queries are not case sensitive. Only possible for non case sensitive file systems (ie windows).

For more information visit the MySQL Docs.

What Do You Think?

Reader Comments

Kurt M.'s Gravatar
Kurt M.
Tuesday, March 20, 2012 at 1:48:53 PM EDT

Steve,

Thanks for this post. A link to this post came up in a Google search I initiated concerning case sensitivity in MySQL. I've recorded your post in a database of tips I keep using a program called TreePad; www.treepad.com

Nagadeep's Gravatar
Nagadeep
Friday, October 30, 2015 at 2:59:12 AM EDT

What about the data inside the tables? How to make that also case sensitive?

Steven Neiland's Gravatar
Steven Neiland
Friday, November 6, 2015 at 12:44:40 PM EST

@Nagadepp,
I'm not sure what you mean by data inside the tables being case sensitive? Any database will record the data as it is entered including case. If you are having problems with the case sinsitivity of the data in your database I would say you are looking for the cause of your problem in the wrong place.

Post a Comment

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub