How does mysql search for emoji? Mysql supports the utf8mb4 character set, that is, the storage of emoji emojis is supported.
Is it possible to use emoji as a where condition for select? How can this problem be achieved?
Reply to discussion (solution)
As long as you can play it out in the editor, the query is no different from other characters.
I used chrome + phpmyadmin to search for mysql:
Select * from 'table' where 'nickname' like convert (_ utf8' %
Depressed, an emoji was intercepted. use the emoji supported by the Forum.
I used chrome + phpmyadmin to search for mysql:
Select * from 'table' where 'nickname' like convert (_ utf8' % 'using utf8mb4) COLLATE utf8mb4_general_ci
The result is null. However, it is certain that the nick name of a record contains this emoji.
None of my devices support emoji and cannot provide accurate expressions.
However, the principle should be as follows:
If your field is utf8mb4_general_ci, the emoji in the field exists with 4 bytes of UTF-8.
If you can input emoji to a string, the query statement is
Select * from 'table' where 'nickname' like '% $ txt %'
If you want to specify an emoji that you cannot enter, you can use a hexadecimal internal code. For example
$ Ch = "\ xf0 \ x9f \ x98 \ x84 ";
$ Ch = "\ xf0 \ x9f \ x98 \ x83 ";
If there is no emoji table, write a small program and use the device supporting emoji to detect it.
Chrome has an extension that can be used to input emoji. I also have a comparison table.
I have also tried to use select * from 'table' where 'nickname' like '% \ xf0 %'; the result is also empty. I still don't want to understand why.
It is strange that the 4-byte emoji in phpmyadmin is a question mark, and the 3-byte emoji is displayed normally. The echo obtained from the read database can be displayed normally, proving that mysql supports 4-byte emoji. Why is the question mark only caused by phpmyadmin?
In phpmyadmin, the 4-byte emoji is a question mark. this is normal.
Although your field is utf8mb4, but phpmaiadmin is UTF-8, it is naturally a question mark when the encoding fails.
You cannot use nickname like '% \ xf0 %' because \ xf0, which is searched by word in text mode, does not exist independently.
You only need to declare that nickname is binary.
BINARY nickname like '% \ xf0 %'
Select * from 'table' where BINARY 'nickname' like '% \ xf0 % ';
It is also empty...
Select * from 'table' where BINARY 'nickname' like '% entire emoji % ';
In this way, I would like to thank xu for his enthusiastic help.