MySQL Collate and Case Sensitivity – Flexible Compare

computer girl

In a typical MySQL installation, the default character set and collation are latin1 and latin1_swedish_ci.  This means that all string comparisons will be performed in a case insensitive manner.  So, ‘Search String’=’search string’.  This is great for most WHERE expressions, however there are occasions where performing a case-sensitive search is required.  Luckily MySQL provides the collate clause which allows you to override whatever the default collation is for a comparison.   As an example :

select * from mytable where first_name collate latin1_general_cs=’damon’

This will only return rows will the first_name matches damon exactly. Damon will not match.

You can also use latin1_bin which performs an exact binary match.

If you are unsure of a field or strings collation, you can use the COLLATION(str) function to return the collation of the string argument.

Leave a Reply and Let me know what you think...