Here are three bugs that look unrelated.

A user can't register because "[email protected]" is already taken, but the only account in the table is "[email protected]". A product list sorts "Zebra" before "apple" in one screen and after it in another. A search for a name in Arabic finds nothing, even though the record is right there, spelled with a hamza the user didn't type.

Same root cause every time: collation. It's the database setting most developers never choose on purpose, and it quietly decides how your text is compared, matched, sorted and indexed.

Character set vs collation

These two get mixed up, so let's separate them:

• Character set is how text is stored: which characters exist and how they become bytes. In MySQL, the answer today is utf8mb4, real UTF-8 with full support for every language and emoji. (The older utf8, also called utf8mb3, can't store 4-byte characters. Avoid it.)
• Collation is how text is compared: is "a" equal to "A"? Is "é" equal to "e"? Does "Ä" sort with "A" or after "Z"?

Think of the character set as the alphabet and the collation as the rules of a dictionary written in that alphabet. Same letters, different ordering rules, depending on whose dictionary it is.

Reading a collation name

MySQL collation names look cryptic until you split them:

So utf8mb4_0900_ai_ci means: UTF-8, Unicode 9.0 rules, ignore accents, ignore case.

Why did "Sara@" clash with "sara@"?

Because with a _ci collation, they're equal. And a unique index uses the collation too, so it treats them as duplicates.

For email addresses that's usually what you want: nobody should be able to register "SARA@" next to "sara@". For other columns, like case-sensitive API keys, invite codes or file names, it's a bug waiting to happen. Two different codes that differ only in case would collide.

The fix is a per-column collation where exactness matters:

$table->string('invite_code', 32)->collation('utf8mb4_bin')->unique();

utf8mb4_unicode_ci or utf8mb4_0900_ai_ci?

This is the choice most Laravel developers actually face. MySQL 8 defaults to utf8mb4_0900_ai_ci, while Laravel's default database config uses utf8mb4_unicode_ci. Both are case- and accent-insensitive, but they're not identical:

• Unicode version. 0900 follows a much newer version of the Unicode rules, so it handles newer characters and emoji more sensibly.
• Trailing spaces. unicode_ci is a PAD SPACE collation: 'abc' = 'abc ' is true. 0900 collations are NO PAD: trailing spaces count. This can change which rows match and what a unique index accepts.
• Portability. MariaDB has historically not recognised the 0900 collations. Many people discover this when a MySQL 8 dump fails to import with "Unknown collation".

My take: pick one deliberately, set it in config/database.php (or DB_COLLATION), and make sure every table uses it. If you need MariaDB compatibility, utf8mb4_unicode_ci is the safe choice. If you're firmly on MySQL 8, utf8mb4_0900_ai_ci is the more modern one.

"Illegal mix of collations"

The real trouble starts when you have both. Tables created by migrations get Laravel's collation; a table created by hand, or imported from somewhere else, gets the server default. Then you join them:

SELECT * FROM tenants t
JOIN legacy_contacts c ON c.email = t.email;
-- Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT)
-- and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

You can force it with COLLATE in the query, but that can stop the database from using the index on that column. The proper fix is to convert the odd table out so that every column you compare shares one collation. Check with SHOW TABLE STATUS or information_schema.COLUMNS.

Why does PHP sort differently from MySQL?

Because PHP's sort() compares bytes, not language. Uppercase letters come before lowercase, so "Zebra" sorts before "apple", and accented letters land after "z". JavaScript's default Array.prototype.sort() does something similar with UTF-16 code units.

So the same list can be ordered one way by the database and another way after you sort it in PHP or in the browser. When you sort text in code, use a collator:

$collator = new Collator('en');
$collator->sort($names); // needs the intl extension

// JavaScript: names.sort(new Intl.Collator('en').compare)

Better still, sort in one place only, usually the database, and don't re-sort in the frontend.

Arabic and English in the same column

Apps in the Gulf deal with this every day: names, addresses and descriptions in both Arabic and English, often in the same table. A few things worth knowing:

• Script order. Under the Unicode collation rules, Latin letters sort before Arabic ones. A mixed list will show all the English names first, then the Arabic names. If users expect something else, you need a separate sort key, not a different collation.
• Diacritics. Short-vowel marks (tashkeel) are treated as accents. In an accent-insensitive collation, a name written with them generally matches the same name written without them. That's usually what search users want.
• Letter variants. Users often type alef without the hamza. Whether "احمد" matches "أحمد" depends on the collation and the data, so test it with real names instead of assuming. When search has to be forgiving, a normalised search column (alef variants unified, tashkeel removed) gives you full control.
• Storage. All of this assumes utf8mb4. If Arabic text turns into question marks, the problem is the character set or the connection, not the collation.

And in PostgreSQL?

Postgres approaches it from the other side: its default collations are case-sensitive, so 'Sara' = 'sara' is false. Case-insensitive matching is something you opt into, with lower() plus an index, the citext extension, or a non-deterministic ICU collation. Neither approach is wrong. You just need to know which one you're standing on, especially when moving an app between the two.

Quick checklist

• utf8mb4 everywhere: database, tables, connection.
• One collation, chosen deliberately and set in config.
• Case-sensitive columns (codes, tokens) get utf8mb4_bin.
• Check new and imported tables for collation mismatches.
• Sort in one place; use a collator when sorting text in code.
• Test search and sorting with real Arabic and English data.

What's the strangest collation bug you've run into? My vote goes to the unique index that disagrees with users about what counts as "different".