How to determine if MySQL collation is case sensitive or not?

Question

Hi there,

I know most MySQL instances out there 'act' case-insensitive by default. But I know that you can use a case sensitive collation if you want to.

Know would there be a function to check if the collation is case sensitive or not? The problem I want to fix is that if you use LOWER in queries, MySQL can't use an index, even though it might not be needed at all to LOWER because of said case insensitivity.

Regards,

Michiel.

Answer 1

Usually the case sensitive collations end in "bin" as in "binary". As it seems you want to check upfront and then build your queries accordingly with or without a function call, you could simply check the collation of your table/column and if it ends in "bin" you have a case sensitive one at hand.