问题描述
假设我们有一个看起来像这样的表:
Assume we have a table that looks like this:
create table t1(c1 varchar(x)collate utf8mb4_general_ci, index(c1))
要进行字节敏感的比较，我们基本上有两种方式(假设所有相关字符串不带有尾随空格，即它们都是符合Padspace的):
To do byte-sensitive comparisons, we basically have two ways (assume that all relevant strings do not have trailing spaces, i.e. they are all padspace-compliant):
select*from t1 where c1 ='test'collate utf8mb4_bin
select*from t1 where c1 = binary'test'
在性能方面值得关注的是哪个?
Which should be preferred when performance is of concern?
使用非二进制字符归类索引时，比较与二进制字符串还是二进制排序规则?
When using an index of non-binary character collation, is it faster to compare with binary string or binary collation?
(将新列添加到表中以仅存储c1的二进制等效项对存储来说是很大的打击，而且不可能).
(Adding a new column to the table just to store the binary equivalent of c1 is a big hit on storage and not possible.)
(P.S.希望能得到一个比较哈希和btree比较的答案，尽管我主要是对btree比较感兴趣.)
(P.S. Would appreciate an answer that compares both hash and btree comparisons, although I'm primarily interested in btree comparison.)
推荐答案
表中有索引时，对于二进制匹配，请使用二进制作为常量，而不是列.这将比您的两个选项都快.
As you have index in the table,for binary match use binary for constant,not to the column. This will be faster than both of your options.
select * from t1 where c1 = binary 'test'
回答您的问题是，选项1在您正在做的地方会更快
Answer to you question is option 1 will be faster where you are doing
WHERE c1 collate utf8mb4_bin='test'
这篇关于MySQL逐字节比较，哪个更快?二进制vs bin_collate的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！