Introduction

Collation controls how a database compares and sorts text. A binary collation compares the underlying encoded bytes instead of using language-aware rules, so the biggest effect is not storage or syntax, but semantics.

That choice can be exactly what you want for identifiers, hashes, and protocol values. It can also be a poor fit for user-facing names or multilingual search, where humans expect case folding and language-aware ordering.

What "Binary" Actually Changes

With a binary collation, text comparison becomes byte-wise. If two strings differ by case, accent, normalization, or any other byte-level detail, they are different.

That means all of the following may change:

• Equality checks
• Ordering in ORDER BY
• Grouping and DISTINCT
• Unique index behavior
• Prefix and range comparisons

The database is still storing text, not raw blobs, but it is using binary rules when deciding whether strings match or which one sorts first.

A Concrete MySQL Example

MySQL makes the difference easy to see because one column can use a case-insensitive collation while another uses a binary collation:

1CREATE TABLE demo (
2    name_ci  VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
3    name_bin VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
4);
5
6INSERT INTO demo (name_ci, name_bin) VALUES
7    ('A', 'A'),
8    ('a', 'a'),
9    ('e', 'e'),
10    ('é', 'é');
11
12SELECT COUNT(DISTINCT name_ci) AS ci_distinct,
13       COUNT(DISTINCT name_bin) AS bin_distinct
14FROM demo;

In the case-insensitive column, 'A' and 'a' may collapse together. In the binary column, they remain distinct because their byte representations differ.

You can also see it in a filter:

SELECT name_bin
FROM demo
WHERE name_bin = 'a';

That matches only the lowercase row. A case-insensitive collation could match both 'A' and 'a' depending on the chosen collation rules.

Sorting Behavior Becomes Less Human-Friendly

Binary collation orders values by encoded bytes, not dictionary rules. That can make sorting stable and predictable for machines, but surprising for humans.

For example, uppercase letters often sort separately from lowercase letters, and accented characters may not appear anywhere near their unaccented forms. If users expect "e" and "é" to appear near each other, binary collation will often violate that expectation.

This is why binary collation is common for machine-oriented fields such as:

• Usernames when case must be significant
• API keys and tokens
• Checksums and digests
• Versioned identifiers and protocol values

It is usually a weaker choice for contact names, titles, tags meant for search, and any field where linguistic ordering matters.

Indexes and Uniqueness

Indexes still work with binary collation, but their meaning changes. A unique index on a binary-collated column treats "ABC" and "abc" as different values. That may be desirable for an identifier, but disastrous for a login name if the application assumes case-insensitive uniqueness.

Consider:

1CREATE TABLE accounts (
2    username VARCHAR(50)
3        CHARACTER SET utf8mb4
4        COLLATE utf8mb4_bin
5        UNIQUE
6);
7
8INSERT INTO accounts (username) VALUES ('Mark');
9INSERT INTO accounts (username) VALUES ('mark');

Both inserts can succeed under a binary collation because the strings are different at the byte level.

That is not a performance trick. It is a data-model decision.

Performance Expectations

Binary comparison can be simpler than language-aware comparison, but it is a mistake to choose binary collation mainly for speed. Real query performance depends far more on indexing, query shape, cardinality, and I/O patterns than on the comparison rule alone.

In practice, the most important effect is correctness: does your application want byte-wise equality, or does it want user-oriented text behavior?

Migration Effects

Changing an existing column to binary collation can alter application behavior immediately:

• Existing searches may become case-sensitive
• 'GROUP BY and DISTINCT counts can change'
• Unique constraints can start allowing values that were previously treated as duplicates
• Joins between columns with different collations may require explicit handling

That is why collation changes need both data review and application review. A silent behavior change in authentication or lookup logic is harder to debug than a syntax error.

Common Pitfalls

One common mistake is using binary collation on user-facing text because it "feels safer." It often produces confusing sort order and harder search behavior.

Another is assuming binary collation is the same as storing bytes in a blob. It is still text, just compared under binary rules.

Teams also get caught by uniqueness changes. If the application treats usernames case-insensitively but the database uses binary collation, duplicate accounts can slip in.

Finally, mixed-collation comparisons can become awkward. Even when the database allows them, implicit conversion rules may not match what you intended.

Summary

• Binary collation compares text by encoded bytes rather than language-aware rules.
• Equality, sorting, DISTINCT, and unique indexes all change under binary comparison.
• Case and accent differences usually remain significant.
• Binary collation fits machine-oriented identifiers better than user-facing natural language text.
• Choose it for semantics, not because you expect a dramatic performance win.