Choosing MySQL Encoding for PHP: Tutorial with Solution

Introduction: Why is the collation question in MySQL still relevant?

If you have ever faced the task of configuring a MySQL database for a PHP application, you have likely seen mysterious terms like utf8mb4_general_ci or latin1_swedish_ci in phpMyAdmin or configuration files. The question "What is the best collation to use for MySQL with PHP?" is one of the most popular on Stack Overflow, and for good reason.

Choosing the wrong encoding can cause Russian letters to turn into gibberish, data sorting to break, and emojis (yes, those smileys) to simply stop being saved to the database. In this tutorial, we will once and for all figure out which collation to choose for a modern PHP project, and I will show you step-by-step configuration code.

What is collation in MySQL and why is it important for PHP?

Collation is a set of rules by which MySQL compares strings. It determines:

• Case insensitivity — whether 'A' will equal 'a' during a search.
• Sort order — how strings will be arranged in query results.
• Character support — which specific characters (including Cyrillic, hieroglyphs, emojis) can be stored.

This is critically important for a PHP developer because:

• You work with user input (forms, comments, posts).
• Modern applications must support Unicode (emojis, special characters).
• Incorrect collation breaks string comparison in WHERE conditions.

Main types of collation: breakdown of popular options

1. utf8_general_ci — the old reliable standard

For a long time, this was the de facto standard. It is fast, but has one serious drawback: it does not support characters outside the Basic Multilingual Plane (BMP). This means emojis 🚀, ancient language characters, and rare Chinese hieroglyphs will not be saved. Additionally, comparison rules are simplified (for example, 'ß' may not match 'ss' as in German).

2. utf8mb4_general_ci — an improved version

Here, mb4 stands for "multibyte 4" — support for 4-byte characters. This already solves the emoji problem. However, the comparison rules remain simplified. If linguistic accuracy is not important to you (for example, for a regular blog), this is an excellent choice.

3. utf8mb4_unicode_ci — the gold standard (my choice)

This is the best collation for most modern PHP projects. It uses the Unicode Collation Algorithm (UCA), which:

• Correctly handles Cyrillic, Latin, and Asian languages.
• Properly sorts strings alphabetically (for example, 'ё' and 'е' will be next to each other, not in different places).
• Supports emojis and all Unicode characters.

The only downside is it is slightly slower than general_ci, but in 99% of cases the difference is unnoticeable.

4. utf8mb4_unicode_520_ci and utf8mb4_0900_ai_ci — the newest versions

Improved options appeared in MySQL 8.0. utf8mb4_0900_ai_ci is the most modern standard based on Unicode 9.0. It is even more accurate, but requires MySQL 8.0+. If your hosting supports it, use it. For older versions (MySQL 5.7 and below), use utf8mb4_unicode_ci.

Step-by-step tutorial: setting up collation for a PHP project

Let's move to practice. I will show you how to properly configure a MySQL connection so that your PHP application works with utf8mb4_unicode_ci.

Step 1: Choosing encoding for the database and tables

Create a database with an explicit encoding specification. You can do this via SQL or phpMyAdmin:

CREATE DATABASE my_database    CHARAC