Solved

MySQL Case Sensitivity

Posted on 2014-12-01
3
166 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
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…

777 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