Search code examples
mysqlselectutf-8case-sensitive

mysql, utf-8 column: how to select only case-sensitive?

I work with utf-8 strings. They should be stored in a MySQL database table. That's why I decided to choose some of utf-8 collations for several columns. Now I see the choice in not as clear as I guessed. The problem is the SELECT statement should be case sensitive. But it is not.

The first option is to choose another utf-8 collation (the only non-ci collation is utf8_bin as far as I see). Is it a solution? The second one is to use BINARY in the SELECT statement:

select col1, col2
from table1
where BINARY col3='CasE_sENsiTive';

That's quite simple, but it reduces the performance at least for x10 times and even more. My table has 1 index, and it works fine without BINARY (no index = slow).

What's a solution in my situation?

Thank you.

Solution

  • You can improve the performance for case-sensitive search in the following way:

    select col1, col2 from table1 where col3='Case_sEnsiTive' AND BINARY col3="CasE_sENsiTive";

    However, if there is no good reason for having a case-insensitive collation, I strongly advise to use utf8_bin as collation.