CODEWITHSUNDEEP
mysqlemojiutf8mb4
Tony
Tony

Reputation: 1611

MySql returns wrong results for emoji strings

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.

Upvotes: 6

Views: 633

Answers (3)

z33
z33

Reputation: 1263

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.

Upvotes: 2

akiraak
akiraak

Reputation: 111

Try this:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Upvotes: 1

Brian Cryer
Brian Cryer

Reputation: 2224

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;

Upvotes: 0

Related Questions