MariaDB select wrong unicode character

Question

INSERT INTO table_name (col_name) VALUES ('😂');

SELECT * FROM table_name WHERE col_name = '🍖';

I my opinion no row has to be returned from the second query, but 😂 is returned.

The table is utf8mb4 with collation utf8mb4_unicode_ci.

Is something related to ci? I would like to keep it.

Answer 1

SELECT '😂' = '🍖' COLLATE utf8mb4_unicode_ci,
       '😂' = '🍖' COLLATE utf8mb4_unicode_520_ci;

Yields 1 and 0.

That is, utf8mb4_unicode_ci treats Emoji as equal, but utf8mb4_unicode_520_ci treats them as different.

So, change the collation of col_name to utf8mb4_unicode_520_ci.