MYSQL - Can the difference between Charset and Collation cause any problem?

Question

I have a Schema on a MySQL Version 8 and I should migrate it to a MariaDB Version 10.3. (with Mysqldump)

The Schema has Charset = utf8mb4 and collation = utf8mb4_0900_ai_ci

The MariaDB server has default Charset = utf8 and collation = utf8_general_ci

When I try to import the dump its impossible because of error:

Unknown collation: utf8mb4_0900_ai_ci

I found a solution from here: https://stackoverflow.com/questions/42385099/1273-unknown-collation-utf8mb4-unicode-520-ci

And It worked. But I want to know:

First: could these differences cause data loss after import dmp data?

Second: could these differences cause any difference in output or sort on my new server?

• I have some other Schemas on MariaDB Server and I can't (and don't want!) to change default charset and collation)

Answer 1

First, I do not recommend going back two decades to Charset = utf8 and collation = utf8_general_ci

utf8 will lose all your Emoji and some Chinese characters. Plus lots of things will compare differently.

Instead, go the the "best available" in MariaDB...

You do have a problem, but the Title of your question is not the right question.

Unknown collation: utf8mb4_0900_ai_ci

This is because MySQL has implemented some COLLATIONs that MariaDB has not yet implemented.

Both databases handle the character set utf8mb4. So, the encoding is not the problem.

The collation could be a small problem or a big problem.

• To import the data without having the specified collation, you must edit the dump file to change to collation. The best is probably utf8mb4_unicode_520. It is not identical to utf8mb4_0900_ai_ci, but it is close.
• (This is unlikely, but possible.) IF you have some special characters and if those characters are being treated as "equal" by one collation and not the other, then you may have an issue with any PRIMARY KEY or UNIQUE key. It might show up as "duplicate key".

Your questions --

Data loss -- No (assuming utf8mb4), unless you get a dup key, and that leads to failure to insert a row.

Differences in output -- (again, unlikely) -- Various accents and special characters may compare differently due to the change in Collation.

My chart shows no collation differences between 520 and 0900, But the chart covers only a small subset of UTF-8 characters. (There are lots of differences with utf8_general_ci.)

Answer 2

could these differences cause data loss after import dmp data?

No. MariaDB 10.3 supports UTF8MB4.

could these differences cause any difference in output or sort on my new server?

Yes. Comparing and sorting is performed according to COLLATION. So compare may produce another result and, hence, another output, sorting may produce another rows ordering.