Which collation in MySQL with PHP is best? Full tutorial

Introduction: why the question of collation in MySQL is not just an aesthete's whim

Anyone who has ever set up a database for a PHP project has faced the question: "Which collation (comparison/sorting) should I choose?" On Stack Overflow, this question has gathered thousands of views, and the answers often confuse beginners. At first glance, it seems there is no difference — as long as it works. But in reality, the choice of collation affects search speed, sorting accuracy (especially with the Russian language), and even how special characters are displayed.

In this tutorial, we will figure out which collation for MySQL with PHP is truly the best in 2025, and why. You will learn how to avoid classic mistakes and configure your database so that your site runs fast and your data doesn't turn into gibberish.

What is collation in simple terms?

Collation is a set of rules by which MySQL compares and sorts strings. It is closely related to the charset. If the charset (e.g., utf8mb4) is responsible for how characters are stored, then collation determines how they are compared: whether to consider case, how to sort the letters "ё" and "е", whether to treat "A" and "a" as the same.

The main types of collation for utf8mb4:

\n
• utf8mb4_general_ci — old, fast, but with simplified sorting (does not always correctly handle some languages).
\n
• utf8mb4_unicode_ci — more accurate, based on the Unicode standard, but was slower before MySQL 8.0.
\n
• utf8mb4_0900_ai_ci — the newest (MySQL 8.0+), the most accurate and fast at the same time.
\n

Why shouldn't you use old collations?

Many legacy projects still live with utf8_general_ci or even latin1_swedish_ci. If you are writing a new PHP project, avoid them like the plague. Here's why:

\n
• Problems with the Russian language: "ё" and "е" may be sorted incorrectly.
\n
• Limited character set: old charsets do not support emoji and rare characters.
\n
• Performance: modern collations are optimized and work faster.
\n

Best collation in 2025: utf8mb4_0900_ai_ci

If your MySQL is version 8.0 or higher (which is the case for most people now), then the best choice is utf8mb4_0900_ai_ci. Breakdown:

\n
• utf8mb4 — a charset that supports 4 bytes per character (emoji, rare hieroglyphs).
\n
• 0900 — based on the Unicode 9.0 standard (the most current version).
\n
• ai — accent insensitive ("café" and "cafe" are equal).
\n
• ci — case insensitive ("Hello" and "hello" are equal).
\n

This collation provides maximum accuracy and speed. If you need to distinguish case or accents, use utf8mb4_0900_as_cs, but for 99% of web projects, ai_ci is ideal.

What if I have MySQL 5.7 or lower?

Then your choice is utf8mb4_unicode_520_ci (or simply utf8mb4_unicode_ci). It's not as fast as 0900, but much more accurate than general_ci. If you have an old version — update urgently, but while you're stuck with it, use utf8mb4_unicode_ci.

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

Let's set up the database correctly. I'll show you using PDO — a modern and secure way to work with MySQL in PHP.

Step 1. Creating a database with the correct collation

Execute this SQL query (via phpMyAdmin or console):

CREATE DATABASE my_database\n    CHARACTER SET utf8mb4\n    COLLATE utf8mb4_0900_ai_ci;

Step 2. Configuring the connection in PHP (PDO)

When connecting to the database, be sure to specify the charset: