Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

sql - MySQL byte-for-byte comparison, which is faster? binary vs bin_collate

Assume we have a table that looks like this:

create table t1(c1 varchar(x)collate utf8mb4_general_ci, index(c1))

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?

(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. Would appreciate an answer that compares both hash and btree comparisons, although I'm primarily interested in btree comparison.)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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'

Answer to you question is option 1 will be faster where you are doing

WHERE c1 collate utf8mb4_bin='test'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...