我将如何搜索包含表情符号的文本? [英] How would I search for text that contains emojis?

问题描述

我们有一个MySQL InnoDB表，其文本字段为COLLATE utf8mb4_unicode_ci.我需要搜索包含任何表情符号字符的行.我已经搜索了很多SO问题，但是人们似乎有他们要搜索的表情符号列表.我实际上是在寻找可以找到任何表情符号的解决方案. 此处 一些帖子没有帮助.

We have a MySQL InnoDB table, with a text field COLLATE utf8mb4_unicode_ci. I need to search for rows that contain any emoji characters. I've searched through quite a few SO questions, but people seem to have a list of emojis they are searching for. I'm actually looking for a solution that will find ANY emoji. Here are some posts that are not helping.

这一个似乎最接近实际为我提供所需的东西，但是OP并未实际发布他的搜索代码.

This one seems to come closest to actually providing me with what I'm looking for, but the OP hasn't actually posted his search code.

谢谢！

推荐答案

我遇到的情况是，数据库从一台服务器迁移到另一台服务器导致表情符号消失.因此，我不得不在原始表中查找所有包含utf8(表情符号)高字符的行.

I've had situation where db migration from one server to another caused emoji to disappear. So I had to find all rows in original table which contained high utf8 (emoji) characters.

此查询按预期工作:

SELECT field FROM `table` WHERE HEX(field) RLIKE "^(..)*F.";

在执行任何操作之前，请检查您是否在数据库，表和连接上使用utf8mb4:

before doing anything check if you are using utf8mb4 on your db, tables AND connection:

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

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