Solved

MySQL Case Sensitivity

Posted on 2014-12-01
3
163 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
Comment Utility
Use BINARY

SELECT ...  WHERE BINARY column_name = ''

Open in new window

0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now