2

I would like to export my database with emoji, but I have a problem with the export. When I exported my table, the emoji are replaced by "?".

For example :

enter image description here

When I export, and import, I have this :

enter image description here

I checked my table (utf-8) :

enter image description here

I use Sequel Pro to export and import.

But if I tried with DataGrip, and I have directly the "?", I never see the emoji :

enter image description here

Jérémie Chazelle
  • 1,721
  • 4
  • 32
  • 70

1 Answers1

3

before you run the queries, run 

set names utf8mb4;

Why?

In short.

First, Emoji usually takes four bytes, however, mysql's utf8, an alias for for utf8mb3, using one to three bytes(i.e., max byte three), which could not understand an Emoji char. As such, you see a '?' in your result. utf8mb4 can do the job since it requires a maximum of four bytes per multibyte character.

Second, set names utf8mb4 will set three session variable, e.g.,

SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

which will coordinate the barrier between server, client and results char set, so we can view the Emoji correctly.

For more information, you can find in the doc

https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html

https://dev.mysql.com/doc/refman/8.0/en/set-names.html

longkai
  • 3,598
  • 3
  • 22
  • 24
  • 1
    While this may solve the OP's problem, it would be a better answer if you [edit] it to explain why this works. A description of what `utf8mb4` is would be a good start. – shoover Jul 13 '18 at 04:52
  • @shoover hi, thank you for the advice and I have add the explanation. – longkai Jul 16 '18 at 03:17