从varchar列中搜索表情符号会返回不同的记录 [英] Searching emoji from varchar column returns different record

问题描述

我正在使用MySQL5.6. DB字符集是utf8mb4. 当我按以下方式搜索表情符号时，得到了意外的结果.

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   |
+-----+------------+

它应该仅返回id = 165.有人知道这是为什么吗?

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

推荐答案

我找到了解决方法.这是一个整理问题.我使用了默认的排序规则值，我假设它是utf8mb4_general_ci.当我更改utf8mb4_bin时，MySQL返回了正确的结果.

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;

这篇关于从varchar列中搜索表情符号会返回不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！