6

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.

Tony
  • 1,551
  • 20
  • 21
  • This might help you: http://stackoverflow.com/questions/7814293/how-to-insert-utf-8-mb4-characteremoji-in-ios5-in-mysql – Stuart Wagner Jun 05 '15 at 03: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 Jun 05 '15 at 04:13
  • same situation here. – z33 Feb 18 '16 at 03:42
  • May have something to do with the upper/lower case algorithm (search 'A' and 'a' gives same rows). – Déjà vu Feb 18 '16 at 04:07

3 Answers3

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.

z33
  • 1,193
  • 13
  • 24
1

Try this:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
sobolevn
  • 16,714
  • 6
  • 62
  • 60
akiraak
  • 111
  • 2
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;
Brian Cryer
  • 2,126
  • 18
  • 18
  • 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). – ACJ Jan 18 '16 at 18:16