14

I am trying to insert emoji's into mysql but it turns to question marks, I have changed mysql connection server collation, database collation , table collation and column collation. I used these to change the items

# For each database:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# For each table:
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# For each column:
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I have done all these but emoji's in mysql still show question marks. Please what should I do to make mysql show the emojis. Thanks in advance

George
  • 1,086
  • 14
  • 48
  • Is your connection to the database using utf8? – Phylogenesis Oct 28 '15 at 08:15
  • I am inserting them through the phpmyadmin, and no the connection is utf8mb4_unicode_ci – George Oct 28 '15 at 08:17
  • In the top-left corner of the main phpMyAdmin page, there's an item called "Server connection collation". Is this set to utf8mb4_unicode_ci, too? Are the character set settings in the "Variables" tab on the main page set to utf8? – Phylogenesis Oct 28 '15 at 08:21
  • 1
    @Phylogenesis Yes please, the "Server Connection collation" is set to utf8mb4_unicode_ci, below is the settings in the "Variables". The character set Conection is utf8mb4, and the charcater set client is utf8 – George Oct 28 '15 at 08:22
  • Are you sure that this is a MySQL issue and not a PHPMyAdmin issue? What happens if you directly query the DB? – cwallenpoole Jun 12 '17 at 21:28

2 Answers2

8

Little late to answer the question. But I hope it will be useful for others...

Above configuration makes the database tables to store utf8 encoded data. But, the database connection(JDBC) should be able to transfer the utf8 encoded data to client. For that the JDBC connection parameter charset should be set to utf8mb4.

Vamshi
  • 133
  • 1
  • 9
  • my connection url is like this, but still my emojis get stored as ??. Do I need to change anything? jdbc:mysql://someIP:port/DB?useUnicode=yes&character_set_server=utf8mb4&characterEncoding=UTF-8 – Aparna Feb 24 '20 at 09:54
0

The default encoding for inbound connections isn't set properly. DEFAULT CHARSET will return as utf8 however character_set_server will be something different.

So, Set default-character-set=utf8.

Thomas Rbt
  • 1,483
  • 1
  • 13
  • 26