mysql，utf-8列:如何选择仅区分大小写? [英] mysql, utf-8 column: how to select only case-sensitive?

问题描述

我使用utf-8字符串.它们应该存储在MySQL数据库表中.这就是为什么我决定为几列选择一些utf-8归类的原因.现在，我看到的选择并不像我想象的那么清晰.问题在于SELECT语句应区分大小写.但事实并非如此.

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.

第一个选择是选择另一个utf-8归类(据我所知，唯一的非ci归类是utf8_bin).这是解决方案吗?第二个是在SELECT语句中使用BINARY:

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';

这很简单，但是它至少将性能降低了10倍甚至更多. 我的表有1个索引，并且在没有BINARY的情况下也可以正常工作(没有索引=慢).

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?

谢谢.

推荐答案

您可以通过以下方式提高区分大小写搜索的性能:

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";

但是，如果没有充分理由不区分大小写，我强烈建议使用utf8_bin作为排序规则.

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

这篇关于mysql，utf-8列:如何选择仅区分大小写?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！