Solved

MySQL Case Sensitivity

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

707 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