Do not worry if you find this error, its not mean that your backup.sql is corrupt or anything else. First before we talk about that error, I think its nice for us to know more about collation in database.
What’s collation name?
As dev.mysql.com said, that A collation name starts with the name of the character set with which it is associated, generally followed by one or more suffixes indicating other collation characteristics. For example, utf8mb4_0900_ai_ci and latin1_swedish_ci are collations for the utf8mb4 and latin1 character sets, respectively.
What’s the meaning of utf8mb4_0900_ai_ci?
utf8mb4_0900_ai_ci is one of the MySQL Collation that it have some meaning to configure our database. So, whats it mean?
uft8mb4means that each character is stored as a maximum of 4 bytes in the UTF-8 encoding scheme.0900refers to the Unicode Collation Algorithm version. (The Unicode Collation Algorithm is the method used to compare two Unicode strings that conforms to the requirements of the Unicode Standard).airefers accent insensitivity. That is, there is no difference between e, è, é, ê and ë when sorting.cirefers to case insensitivity. This is, there is no difference between p and P when sorting.
Regarding from Monolune.com, utf8mb4 has become the default character set, with utf8mb4_0900_ai_ci as the default collation in MySQL 8.0.1 and later. Previously, utf8mb4_general_ci was the default collation. Because the utf8mb4_0900_ai_ci collation is now the default, new tables have the ability to store characters outside the Basic Multilingual Plane by default. Emojis can now be stored by default. If accent sensitivity and case sensitivity are required, you may use utf8mb4_0900_as_cs instead.
How to solve error “Unknown collation: ‘utf8mb4_0900_ai_ci'”?
I’ve got this error when importing the database that I exported from another machine. So, principally we just need to change that collation to another one, and you can try to change it to utf8mb4_general_ci. Its work for me, and I think its also work for you.
The things that you need to do,
- Open your backup.sql in your favorite text editor (Sublime, Visual Code or others text editor that support Find and Replace feature)
- Then, you can do Find and Replace (CTRL+H), then find utf8mb4_0900_ai_ci and replace it to utf8mb4_general_ci.
- You need to find and replace all by pressing that button. You can also do ctrl+alt+enter on Sublime or Visual Studio Code.
Well, thats it. You can save it and try to import again, I hope it can help you.