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.

Are you using MySQL or MariaDB? Even though MariaDB is (supposed to be) a drop-in replacement for MySQL, I would expect that in situations like this the behaviour might be different, so one of the tags should be removed. — Arjan, Apr 14 '16 at 22:01

score 0 · Accepted Answer · answered Apr 15 '16 at 05:43

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.

1 Answers1