[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

MySQL Case Sensitivity

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
Richard Korts
Asked:
Richard Korts
  • 2
1 Solution
 
GaryCommented:
Use BINARY

SELECT ...  WHERE BINARY column_name = ''

Open in new window

0
 
Dave BaldwinFixer of ProblemsCommented:
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
 
GaryCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now