Database Collation

A database does not sort text the way many developers expect. The same characters can compare equal, differ, or fall into an unexpected order depending on the sorting rule in use.

A sorting rule is a set of rules that defines how strings are compared and ordered. It is related to the character set, but it is not the same thing. The character set explains how characters are encoded into bytes. The sorting rule explains how those byte sequences are compared and what counts as equivalent.

Where Sorting Rules Matter

Sorting rules influence more than a simple ORDER BY. They affect:

• Sorting results from ORDER BY
• String comparisons in WHERE, including =, >, <, and LIKE
• Duplicate handling in DISTINCT and GROUP BY
• Matching in JOIN conditions
• Decisions made by unique indexes

Because these operations appear throughout normal database work, a mismatch can affect correctness and performance.

The Main Dimensions

Sorting behavior usually varies along a few dimensions.

1. Case sensitivity: A case-insensitive collation treats uppercase and lowercase as equivalent. A case-sensitive collation or binary collation does not.
2. Accent sensitivity: An accent-insensitive collation may ignore marks, while an accent-sensitive collation may distinguish them. For example, e and é might be equal or different.
3. Binary comparison: A binary collation compares raw bytes. It is often fast, but it may not match language habits.
4. Language rules: Some collations follow language-specific conventions, such as phonetic ordering or stroke ordering.

Each dimension can change query results, especially when users expect natural language order.

Common Collation Families

Different database systems provide different families of sorting rules.

• MySQL offers collations such as utf8mb4_general_ci, utf8mb4_unicode_ci, and utf8mb4_bin.
• SQL Server offers families such as Chinese_PRC_CI_AS and Chinese_PRC_Stroke_90_CI_AS.
• PostgreSQL may use the operating system locale or an ICU collation, such as zh_CN.UTF-8, or define custom ICU rules.

The availability and naming of these options vary by version and configuration.

Practical Examples

An equality check can show the difference clearly:

SELECT 'Apple' = 'apple' COLLATE utf8mb4_general_ci;
-- Returns 1, so the strings are considered equal

SELECT 'Apple' = 'apple' COLLATE utf8mb4_bin;
-- Returns 0, so the strings are considered different

Ordering can also change. A basic Unicode collation may sort by code point. A language-aware collation may sort differently, affecting which value comes first.

How to Choose

• For general web applications, a modern Unicode-based collation may be a reasonable default.
• For passwords, tokens, or other strict comparisons, a binary or case-sensitive collation may be safer.
• For language-specific ordering, select a collation that fits the audience.
• For consistency, align database, table, and column collations.

Common Pitfalls

• Index invalidation: Different collations can cause implicit conversion and prevent index usage.
• High cost: Converting an existing large table may rebuild it and lock it. Plan changes during low activity.
• Temporary table issues: Some systems copy collation rules from a temporary database, which can create conflicts.
• Migration risk: Upgrades may change default collations, so compatibility should be reviewed.

Conclusion

Sorting rules are small in name but important in effect. They decide how strings compare and how they are ordered. A wrong choice can lead to incorrect joins, duplicate keys, or surprising user-facing lists.

Choosing the right rule early, and keeping it consistent, is far more effective than fixing problems after they appear.