Solved: Error 1273 Unknown Collation utf8mb4_0900_ai_ci in MySQL

Did you just stumble upon the Unknown Collation: utf8mb4_0900_ai_ci error in MySQL? Well, that’s because of the character set mismatch between the databases. Read this blog post till the end to find the solution.

Recently, I was manually importing a database to a new server using MySQL. Things were going well… until my screen flashed – Unknown collation: ‘utf8mb4_0900_ai_ci’ ⚠

I dug up further to find the actual cause and found that the collation of the new database was different from the old one. In this post, I am going to share the solution I used to fix this issue. But let’s first understand what ‘Collation’ mean in MySQL.

What is Collation?

“A collation is a set of rules that defines how to compare and sort character strings. Each collation in MySQL belongs to a single character set. Every character set has at least one collation, and most have two or more collations.” according to MySQL.

SQL servers use collations that define the character sets, along with their sorting rules and case & accent sensitivity properties. Each part of the collation, separated by an underscore _, defines certain properties.

Take the collation — ‘utf8mb4_0900_ai_ci’ for example. Here the first part (utf8mb4) refers to a 4-Byte UTF-8 Unicode Encoding character set. The number 0900 refers to the Unicode Collation Algorithm version. And the last parts define the accent & case insensitivities.  Learn SQL query for case-insensitive data.

Enough of the theory part..! Now, let’s come to a solution to the Unknown collation: ‘utf8mb4_0900_ai_ci’ error in MySQL.

The solution to Unknown Collation utf8mb4_0900_ai_ci Error in MySQL

Simply follow these steps to solve the Unknown Collation utf8mb4_0900_ai_ci Error in MySQL:

• Step 1: Open the dump.sql file in any editor.
• Step 2: Find ‘utf8mb4_0900_ai_ci’ & replace it with ‘utf8mb4_unicode_ci’.
• Step 3: Save the file.

Bingo! The error will be solved.

I hope the above-provided solution will be helpful to you in solving the ‘unknown Collation utf8mb4_0900_ai_ci error in MySQL’

Comment if you still have any queries or doubts. 🙂

Also, share this solution via social media with your developer friends.

Thanks for reading! 🍀