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

问题:

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.

回答1:

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.

回答2:

Either use your provided example, or, if you will always treat the column as case-sensitive, assign a case-sensitive collation to it (i.e. a collation that ends in _cs [case-sensitive] or _bin [binary]). See Case Sensitivity in the MySQL reference.