Choosing the Right MySQL Collation for PHP and UTF-8

By the end of this page, you will understand the difference between a character set and a collation in MySQL, how they relate to PHP UTF-8 output, and how to choose a sensible default for a typical web application. You will also learn why utf8mb4 is usually the safest modern choice, and when to use case-insensitive versus binary collations.

A common source of confusion is that UTF-8 and collation are not the same thing.

• A character set defines how text is stored.
• A collation defines how text is compared and sorted.

In MySQL:

• utf8 or utf8mb4 = character sets
• utf8_general_ci, utf8_unicode_ci, utf8_bin = collations

If PHP outputs UTF-8, that does not map to one single MySQL collation. It only means your application is sending text encoded in UTF-8. MySQL still needs:

1. a character set to store the bytes
2. a collation to decide how string comparisons behave

For example, a collation controls questions like:

• Should A equal a?
• Should accented letters be treated similarly when sorting?
• How should words be ordered alphabetically?

This matters in real programming because collations affect:

• ORDER BY
• GROUP BY
• equality checks in WHERE
• unique indexes on text columns
• user search behavior

For modern applications, the usual recommendation is:

CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci

or, in newer MySQL versions, a modern utf8mb4 collation such as utf8mb4_0900_ai_ci where available.

Why utf8mb4 instead of utf8?

• MySQL's utf8 is historically limited and does not support all Unicode characters.
• utf8mb4 supports full Unicode, including emoji and many less common symbols.

So the most important practical advice is:

• prefer utf8mb4 over utf8
• choose a Unicode-aware collation for general text
• use binary collation only when you specifically need exact byte-level comparisons

Think of text handling like a library system:

• The character set is the alphabet the library knows how to store.
• The collation is the rulebook for how books are alphabetized.

Two libraries may store the same letters, but sort them differently.

In the same way, PHP outputting UTF-8 means your app is speaking a text language MySQL can store, but MySQL still needs instructions for how to compare words.

So:

• UTF-8 = the letters available
• collation = the sorting and comparison rules

The core idea is to define both the character set and the collation explicitly.

Creating a database

CREATE DATABASE app_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Creating a table

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    display_name VARCHAR(100) NOT NULL
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

Setting the connection character set from PHP

Using PDO:

<?php
$pdo = new PDO(
    'mysql:host=localhost;dbname=app_db;charset=utf8mb4',
    'username',
    'password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]
);

Column-level collation when needed

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
    sku VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

In this example:

• name uses a general text collation for user-friendly searching and sorting
• sku uses a binary collation for exact, case-sensitive matching

Why not just use utf8?

-- Older and limited
CHARACTER SET utf8
COLLATE utf8_unicode_ci

-- Better modern default
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci

For beginner-friendly projects, utf8mb4 with a Unicode-aware collation is the safest default.

Consider this table:

CREATE TABLE demo (
    word VARCHAR(20)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And these rows:

INSERT INTO demo (word) VALUES
('apple'),
('Apple'),
('ápple');

Now run this query:

SELECT * FROM demo WHERE word = 'apple';

What happens step by step

1. MySQL reads the value 'apple' using the connection character set.
2. The word column is stored using utf8mb4.
3. The column collation is utf8mb4_unicode_ci.
4. The _ci part means case-insensitive comparison.
5. MySQL compares 'apple' with each stored value according to that collation.
6. Depending on the collation rules, apple and Apple are treated as equal for comparison.

Now compare that with a binary collation:

CREATE TABLE demo_bin (
    word VARCHAR(20)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

With the same inserted values, this query:

SELECT * FROM demo_bin WHERE word = 'apple';

behaves differently because utf8mb4_bin performs exact comparisons.

• apple matches apple
• Apple does not match apple
• accented characters are compared more strictly

This shows why collation affects behavior even when the stored text is still UTF-8.

General website content

For usernames, article titles, comments, and profile names, developers usually want text stored in full Unicode and compared in a human-friendly way.

Typical choice:

utf8mb4_unicode_ci

Exact identifiers

For API keys, hashes, case-sensitive codes, or internal identifiers, exact comparison matters more than friendly sorting.

Typical choice:

utf8mb4_bin

Search and filtering

If users search for names or titles, a case-insensitive collation often gives more expected results.

International applications

If your app stores multiple languages, Unicode-aware collations help sorting and comparisons behave more consistently than older simplified collations.

E-commerce

• product names: Unicode-aware collation
• SKUs: binary or exact matching collation
• customer names: Unicode-aware collation

In real projects, developers usually set defaults at multiple levels so text handling is consistent.

Common pattern: set database and table defaults

• database default character set: utf8mb4
• database default collation: Unicode-aware collation
• override at column level only when necessary

Common pattern: set connection charset in PHP

A very common mistake is storing UTF-8 data in UTF-8 tables but forgetting to tell the MySQL connection to use UTF-8. In PDO, developers typically use:

$pdo = new PDO('mysql:host=localhost;dbname=app_db;charset=utf8mb4', 'user', 'pass');

Common pattern: binary for exact fields

Real codebases often use binary collations for:

• tokens
• invitation codes
• case-sensitive login identifiers
• checksum-like values

Validation and guard clauses

Applications often validate user input before insert, but they still rely on the database collation for consistent comparison behavior.

Example:

if ($username === '') {
    throw new InvalidArgumentException('Username is required.');
}

Then the database schema decides whether Alice and alice are considered duplicates.

Unique indexes depend on collation

CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

With a case-insensitive collation, Alice and alice may conflict as duplicates. This is often desirable for usernames, but not always. Developers choose the collation based on the business rule.

1. Confusing character set with collation

Broken assumption:

UTF-8 output from PHP means MySQL automatically knows the correct collation.

Why it is wrong:

• UTF-8 is a character encoding
• collation controls sorting and comparison

2. Using MySQL utf8 and assuming it means full UTF-8

Broken setup:

CHARACTER SET utf8

Why to avoid it:

• MySQL's older utf8 is limited
• it may not support all Unicode characters

Prefer:

CHARACTER SET utf8mb4

3. Forgetting the connection charset

Broken PHP example:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=app_db', 'user', 'pass');

Better:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=app_db;charset=utf8mb4', 'user', 'pass');

Without this, characters may be misinterpreted during inserts and reads.

4. Using binary collation for normal user-facing text

Broken choice:

COLLATE utf8mb4_bin

for names, titles, or comments.

Problem:

• sorting feels less natural
• searches become stricter than users expect

5. Not realizing _ci means case-insensitive

If a column uses _ci, then text comparisons usually ignore case differences.

That affects:

• WHERE
• unique constraints
• grouping

6. Mixing collations carelessly

If different columns or queries use incompatible collations, MySQL can produce confusing comparison behavior or collation errors. Keep defaults consistent unless you have a clear reason to differ.

*_unicode_ci vs *_general_ci

*_ci vs *_bin

• Character set = how text is stored
• Collation = how text is compared and sorted
• PHP outputting UTF-8 does not choose a MySQL collation automatically
• For new projects, prefer:

CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci

• Use utf8mb4_bin for exact matching fields
• Avoid relying on MySQL's old utf8 for new applications
• Set the connection charset in PHP:

$pdo = new PDO('mysql:host=localhost;dbname=app_db;charset=utf8mb4', 'user', 'pass');

• _ci means case-insensitive
• _bin means binary comparison
• Collation affects:
  • WHERE
  • ORDER BY
  • GROUP BY
  • UNIQUE constraints

Quick default rule:

• normal text: utf8mb4_unicode_ci
• exact codes or tokens: utf8mb4_bin

Does PHP UTF-8 output correspond to one specific MySQL collation?

No. UTF-8 output only describes the text encoding. MySQL still needs a character set and a collation defined separately.

What is the difference between utf8 and utf8mb4 in MySQL?

utf8mb4 supports full Unicode. MySQL's older utf8 is limited and should usually be avoided for new projects.

Which MySQL collation is best for a general website?

For most modern websites, a Unicode-aware case-insensitive collation on utf8mb4 is a good default, such as utf8mb4_unicode_ci.

When should I use a binary collation?

Use a binary collation for values that must match exactly, such as tokens, hashes, codes, or other case-sensitive identifiers.

Does collation affect unique usernames?

Yes. With a case-insensitive collation, Alice and alice may be treated as the same value in a unique column.

Do I need to set UTF-8 in both PHP and MySQL?

Yes. Your HTML or HTTP output, PHP database connection, and MySQL schema should all be configured consistently.

Is utf8_general_ci still a good default?

It is mostly a legacy choice. Unicode-aware collations are usually a better default for general text.

• Character sets — directly related because they define how text is stored.
• Unicode — important for understanding why utf8mb4 is preferred.
• Database encoding — covers consistent text handling across application layers.
• PDO MySQL connection settings — needed to ensure PHP sends and receives UTF-8 correctly.
• SQL ORDER BY behavior — collation affects how rows are sorted.
• Unique indexes — collation changes whether text values are considered equal.
• Case-sensitive vs case-insensitive comparisons — central to choosing the right collation.