Solved

MySQL Case Sensitivity

Posted on 2014-12-01
3
169 Views
Last Modified: 2014-12-01
I believe that MySQL is NOT case sensitive; that is, for example "GENE" and "Gene" are considered equal so a query like "SELECT * from techs where techid = 'Gene'" will get that record.

Is there a way to cause MySQL to be case sensitive so the above example fails if The database has "GENE" as the value.

Thanks.
0
Comment
Question by:Richard Korts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 40474122
Use BINARY

SELECT ...  WHERE BINARY column_name = ''

Open in new window

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 40474252
Case sensitivity for data is determined by the 'collation' which in simple terms defines the 'sort order' in the character set.  As shown on this page http://dev.mysql.com/doc/refman/5.5/en/charset-we-sets.html , if the 'collation' name ends in 'ci', it is case insensitive.  If it ends in 'cs', it is case sensitive.  And if it ends in 'bin', it is binary order without regard to case.  Note that 'collation' is applied without regard to the operating system.  It works the same on Windows, Mac, and Linux/Unix.

Database and table names are also used as directory and file names.  In the MySQL installs that I have, it seems that database and table names are always forced to lower case.  That may be because of phpMyAdmin and not MySQL.  I haven't really checked to find out... since that's fine with me.
0
 
LVL 58

Expert Comment

by:Gary
ID: 40474259
I wouldn't be forcing case sensitive collation on a db, more likely to cause problems down the line when you cannot work out why a simple select is not working, better to use BINARY when you need it for those edge cases.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question