What is the best collation to use for MySQL with PHP?
You are correct that your application's encoding layersâPHP, Apache, and HTMLâmust align on UTF-8. However, matching a character set in your code doesn't automatically resolve the question of which collation to use inside MySQL. This distinction is critical because the character set defines how bytes are interpreted as characters, whereas the collation dictates how those characters are sorted, compared, and indexed.
When you configure PHP to output UTF-8, you are setting the client connection's character set expectation. In a typical setup, this matches with utf8mb4, not just utf8. It is important to note that standard MySQL installations prior to version 5.5.3 often defaulted to utf8 (which supports only up to 3-byte characters), failing to handle emojis or rare Chinese characters correctly. Modern best practices require switching the database, tables, and columns to utf8mb4.
Regarding the specific collations you mentionedâutf8_general_ci, utf8_unicode_ci, and utf8_binânone of them are strictly "officially recommended" by MySQL as a universal default for all scenarios. Instead, the choice depends on your performance requirements versus your need for accurate sorting.
The Performance Trade-off: Binary vs. Case-Insensitive
The most common confusion arises from choosing between case-insensitive collations (ending in _ci) and binary collations (_bin).
If you select utf8_bin, MySQL performs byte-by-byte comparison. This means that "A" and "a" are treated as different characters. While this is often what you want for strict sorting logic, it creates significant performance bottlenecks. Binary comparisons cannot utilize standard B-Tree indexes effectively for range queries in many contexts, and they ignore locale-specific rules entirely. Furthermore, if your application relies on case-insensitive searches (which most websites do), utf8_bin will break that expectation unless you handle it explicitly in the query logic.
On the other hand, utf8_general_ci is a legacy collation designed for speed over accuracy. It groups characters together loosely based on their general linguistic properties but fails to sort them according to specific language rules. For example, in a French context, accented characters might be sorted differently than they should be under standard linguistic norms. If you are building a global website where the content language is unknown or mixed, this lack of precision can lead to confusing search results or incorrect ordering of data.
The Standard for Accuracy: utf8mb4_unicode_ci
For a general website where you are unsure of the specific content being entered, utf8mb4_unicode_ci (or its predecessor utf8_unicode_ci) is generally the safest choice. This collation follows the Unicode Collation Algorithm (UCA). It provides a consistent, case-insensitive ordering that respects the linguistic nuances of many languages while remaining performant enough for standard web applications.
When you switch from utf8_general_ci to utf8mb4_unicode_ci, you gain support for the full range of Unicode characters (including emojis) and improved sorting accuracy without sacrificing too much performance. This aligns well with the UTF-8 encoding layer you established in PHP and Apache.
Implementation Details
To apply this correctly, ensure your database, table, and column definitions reflect the new standard. You can check your current settings using a simple query:
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
If you need to alter an existing database to match the utf8mb4_unicode_ci standard, run commands like this:
ALTER DATABASE your_database_name
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
ALTER TABLE your_table_name
CONVERT TO CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
Be aware that changing collations on a table with existing data can be resource-intensive, especially for large datasets. It is often advisable to create the database and tables fresh with the correct settings from the start if you are building a new system.
Why This Matters for Your Stack
The connection between your PHP code and MySQL becomes seamless when both agree on the encoding and collation strategy. While there is no single "official" recommendation that covers every possible edge case, adopting utf8mb4 with utf8mb4_unicode_ci offers the best balance of internationalization support, sorting accuracy, and index performance for a general-purpose website. This setup ensures that whether a user enters an email address, a product description, or a comment with special characters, the data is handled consistently across your entire stack.