mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:

Error: Unknown collation utf8mb4_0900_ai_ci

Then I went back into the sql file and replaced utf8mb4_0900_ai_ci with utf8mb4_general_ci, but then it says there are errors with various CREATE VIEW sql statements. Are there any other quick, error free fixes I can try?

In MariaDB use utf8mb4_unicode_520_ci instead, or you may even use uca1400_as_ci if you have a newer version of MariaDB.

Don't use "_general_ci", as it does not correctly sort or compare according to any version of Unicode and have been superseded decades ago.

When MySQL introduced utf8mb4_0900_ai_ci based on comparison and sorting rules in Unicode 9.0, MariaDB chose not to follow at the time. The new rules did not change much as Unicode's sorting and comparison rules have been pretty stable for several generations of Unicode now. Choosing the "unicode_520_ci" rules will in almost all situations have the same result and this collation can be used in both MySQL and MariaDB.

MariaDB is currently introducing collation rules from Unicode 14.0 - called simply uca1400_as_ci (you don't need the "utf8mb4_" part anymore). You can upgrade to these if you have a new version of MariaDB.

Make sure you use "utf8mb4" as the character encoding (and not "utf8mb3" or "utf8", which represent the older flawed/deprecated implementation of UTF8).

StackExchange Database Administrators Q#248904, answer score: 20