asked on

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.

ASKER CERTIFIED SOLUTION

Gary

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

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.

Gary

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.