在MySQL中搜索表情符号 [英] Searching for emojis in MySQL

问题描述

我有一个看起来像这样的字符串:

I have a string that looks like this:

现在，当我的应用程序将此字符串推入其utf8 mysql数据库列时，在MySQL CLI中如下所示:

Now, when my app shoves this string into its utf8 mysql database column, it looks like this in the MySQL CLI:

如果我选择convert(mystring using utfmb4)，它仍然看起来像这样.

If I select convert(mystring using utfmb4) it still looks like this.

如果我使用select hex(mystring) from mytable;将其转换为十六进制，则看起来像这样:

And if I turn it to hex using select hex(mystring) from mytable;, it looks like this:

C3A2CB9CE282ACC3AFC2B8C28FC3B0C5B8C592CB86C3B0C5B8C592C5A0C3B0C5B8C592C281C3B0C5B8E280A1C2ACC3B0C5B8E280A1C2A7

现在，假设我要查找其中带有表情符号波动的字符串.好吧，波浪表情符号的十六进制为F09F8C8A.但是F09F8C8A不在上面的十六进制中，因此select * from mytable where hex(mystring) like '%F09F8C8A%';之类的东西不起作用.

Now, let's say I want to find strings with that emoji wave in it. Well, the hex for the wave emoji is F09F8C8A. But F09F8C8A isn't in the hex above so something like select * from mytable where hex(mystring) like '%F09F8C8A%'; doesn't work.

有什么建议吗?

推荐答案

我称之为双重编码".您的客户声称它正在获取latin1字符，但告诉MySQL它们应该为utf8，因此在数据库中将3字节的utf8字符转换为6字节.

I call that "double encoding". Your client claimed it was getting latin1 characters, but told MySQL that they should be utf8, so a 3-byte utf8 character got converted to 6 bytes in the database.

您需要修复客户端和表中的数据. 此链接对此进行了讨论: http://mysql.rjweb.org/doc.php/charcoll . (很抱歉，没有有关如何解决问题的简短摘要.) 修复 .

You need to fix both the client and the data in the table(s). This link discusses it: http://mysql.rjweb.org/doc.php/charcoll . (Sorry, there is no brief summary of how to fix your problems.) The issues and the fixes.

这篇关于在MySQL中搜索表情符号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！