MySql returns wrong results for emoji strings

Question

My database, tables, fields all are using utf8mb4. I can store well emoji symbols into some fields. Now I try to query such as:

SELECT * FROM user WHERE name='😀😇😈😉'

Amazing, the result are records with field names different such as '😀😃😇😉'

Looks like mysql matches emoji strings by their lengths but not contents.

Any idea to fix that problem? Many thanks.

This might help you: stackoverflow.com/questions/7814293/… — Stuart Wagner
– Stuart Wagner, Commented Jun 5, 2015 at 3:54
Thanks Stuart for very quick reply. My problem is quite different. I can store, browse and extract very well all strings with emoji symbols. Only problem of matching them when querying. — Tony
– Tony, Commented Jun 5, 2015 at 4:13
May have something to do with the upper/lower case algorithm (search 'A' and 'a' gives same rows). — Déjà vu
– Déjà vu, Commented Feb 18, 2016 at 4:07

z33 · Accepted Answer · 2016-02-18 04:03:43Z

2

Just did this command to my table:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

that's all. the result is correct then.

answered Feb 18, 2016 at 4:03

z33

1,25314 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sobolevn · Accepted Answer · 2016-01-26 09:45:30Z

1

Try this:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

edited Jan 26, 2016 at 9:45

sobolevn

18.2k6 gold badges66 silver badges64 bronze badges

answered Jan 26, 2016 at 8:56

akiraak

1112 bronze badges

Comments

Brian Cryer · Accepted Answer · 2015-07-23 10:54:10Z

0

What is the collation order on your table? Since you are using utf8mb4 it should be utf8mb4_unicode_ci. Anything else and you are likely to get the problem you are seeing - worse you might even get multiple records coming back.

To set the collation order use:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

answered Jul 23, 2015 at 10:54

Brian Cryer

2,27220 silver badges18 bronze badges

1 Comment

ACJ Over a year ago

I have the same problem as the OP, and I have collation_connection, collation_database, and collation_server set to utf8mb4_unicode_ci. SELECT queries appear to indeed only match the number of emoji, and not the actual content (which is odd, to say the least).

Collectives™ on Stack Overflow

MySql returns wrong results for emoji strings

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related