Searching emoji from varchar column returns differ

Question

I'm using MySQL5.6. The DB character set is utf8mb4. When I search emoji as below, I got unexpected results.

mysql> SELECT id, hex(title) FROM tags WHERE title = 0xF09F9886;
+-----+------------+
| id  | hex(title) |
+-----+------------+
| 165 | F09F9886   |
| 166 | F09F9884   |
+-----+------------+

It should return only id=165. Does anyone know this why?

Answer 1

I found how to fix it. It was a problem of collation. I used default collation value, I presume it's utf8mb4_general_ci. When I changed that utf8mb4_bin, MySQL returned right result.

You can change collation as below.

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;