MySQL utf8mb4 Collation Choices for Multilingual Applications

Character sets get picked once, usually by a default, and then quietly shape every comparison, sort, and unique index in your database for years. For a multilingual application, the collation you choose decides whether "resume" and "résumé" are equal, whether a unique email constraint treats uppercase and lowercase as the same, and how names sort in different languages. Changing it later is possible but expensive, so it is worth understanding the options up front.

Character Set Versus Collation

A character set defines which characters can be stored and how they are encoded. A collation defines how strings are compared and sorted within that character set. They are separate choices, and the collation name tells you its character set, its rules, and its sensitivity: a suffix of ci means case-insensitive, cs case-sensitive, ai accent-insensitive, as accent-sensitive, and bin means binary comparison.

In MySQL, the character set called utf8 is an alias for utf8mb3, which stores at most three bytes per character and cannot represent many characters outside the basic multilingual plane, including emoji and some rare scripts. Use utf8mb4, which implements full UTF-8 with up to four bytes per character. The utf8mb3 name is deprecated in current MySQL versions, so new schemas should use utf8mb4 explicitly.

The Main Collation Families

For utf8mb4 you will mostly choose between a few families. Default behavior varies by version and by server distribution, so check the documentation for what your server uses.

• utf8mb4_0900_ai_ci: the default in MySQL 8.0 and later. It is based on a recent Unicode collation algorithm version, is accent-insensitive and case-insensitive, and handles a wide range of languages with generally better rules and speed than older options.
• utf8mb4_unicode_ci: based on an older Unicode algorithm version. It is widely used in existing schemas and supported by older MySQL and MariaDB versions, which makes it a common choice for compatibility.
• utf8mb4_general_ci: a simpler, historically faster collation with less accurate language rules. It is mostly kept for legacy reasons, and modern servers rarely need it.
• Language-specific variants: collations such as those for Swedish, German, or Japanese apply locale rules, for example treating certain letters as separate rather than as accented forms. Use them when a single dominant language needs correct sorting.
• utf8mb4_bin: compares by byte values, so it is case-sensitive and accent-sensitive and ignores linguistic rules. It is suited to tokens, hashes, and identifiers where exact matching matters.

MySQL 8.0's 0900 collations also use the NO PAD attribute, meaning trailing spaces are significant in comparisons, whereas many older collations ignore them. This can change the behavior of comparisons and unique keys during an upgrade, so test with real data.

How Collation Affects Your Data Rules

Collation affects correctness, not just sorting. With an accent-insensitive, case-insensitive collation, a unique index on an email or username column treats "Jose" and "José" as duplicates and rejects the second insert. That may be desirable for emails and usernames, and unwelcome for names or tags where the difference matters. Decide per column based on what should count as identical.

For values such as API tokens, hashes, and opaque identifiers, use a binary or case-sensitive collation, because a case-insensitive match could make two different tokens compare equal. For human-readable text that users search, a case-insensitive, accent-insensitive collation usually matches expectations, since people rarely type accents consistently.

CREATE TABLE users (
    id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email    VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    api_key  VARCHAR(64)  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_api_key (api_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Index Size and Storage Limits

Because utf8mb4 reserves up to four bytes per character when calculating index limits, long indexed strings can hit key length limits. InnoDB's maximum index key length is 3072 bytes with the usual row formats, so a fully indexed column is limited to 768 characters, and older configurations had a much smaller limit of 767 bytes. If you see an error about a specified key being too long, shorten the column, use a prefix index, or reconsider indexing that column. Check the documentation for the limits that apply to your version and row format.

Keeping Connections and Comparisons Consistent

The client connection has its own character set and collation settings. If the connection uses a different character set than the column, MySQL converts data, and a mismatch can corrupt text or cause unexpected comparisons. Configure your driver and connection to use utf8mb4 explicitly rather than relying on defaults.

Mixing collations in a comparison or join can raise an "illegal mix of collations" error, and even when it works, comparing columns with different collations can prevent index use and slow queries. Standardize on one default collation per database and override it only for columns with a clear reason.

Migrating an Existing Schema

Converting a table rewrites it, which can be slow and can lock or consume significant resources on large tables. Plan the change, test on a copy, and check that unique indexes will not collide under the new comparison rules before running it in production.

ALTER TABLE users
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Before migrating, search for values that would become duplicates under the new collation, and verify that index key lengths still fit. For large tables, use an online schema change approach appropriate for your environment and consult the MySQL documentation for the algorithms and locking behavior of your version.

Frequently Asked Questions

Is utf8 in MySQL the same as UTF-8?

No. MySQL's utf8 is utf8mb3, limited to three bytes per character. Use utf8mb4 for full UTF-8, including emoji.

Which collation should I use for a new multilingual app?

On MySQL 8.0 and later, utf8mb4_0900_ai_ci is a sensible default. Use binary collations for tokens and exact-match identifiers, and language-specific ones when one language dominates.

Why does my unique index reject names that look different?

An accent-insensitive collation considers them equal. Change the column's collation to an accent-sensitive or binary one if distinctions should be preserved.

Does collation affect performance?

It can. Different collations have different comparison costs, and mismatched collations can block index use. Measure with your own data and queries.

Summary

Use utf8mb4 everywhere, pick a default collation that matches your search expectations, and use binary collations for exact identifiers. Watch for uniqueness effects, index length limits, and connection settings, and test migrations on real data before changing production. Collation names and defaults depend on server version and distribution, so confirm them in the official documentation.