MySql returns incorrect results for emoji strings

Question

My database, tables, fields use utf8mb4. I can store emoji characters in some fields. Now I am trying to execute a query, for example:

SELECT * FROM user WHERE name = '😀😇😈😉'

Surprisingly, the result is records with field names other than "😀😃😇😉"

It seems that mysql matches emoji strings in length but not content.

Any idea to fix this problem? Thank you very much.

Answer 1

Just executed this command in my table:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

what all. then the result will be correct.

Answer 2

What is the sort order on your desk? Since you are using utf8mb4, it should be utf8mb4_unicode_ci . Anything else, and you are likely to get the problem you see, the worse that you can even return a few records.

To set the sort order, use:

 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

Answer 3

Try the following:

 ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;