Julie Bsd Julie Bsd - 1 month ago 15
MySQL Question

emoji display as ? in MySQL database

Emoji are stored as ? in my database (when i'm visualizing them using phpMyAdmin), however when i retrieve them using a simple request (from php) i get the real value.

line on database (using phpMyAdmin)
enter image description here

Request from php

$query = "SELECT id,com
FROM coms_table
WHERE id = 627";


Result

id com
627 \ud83d\ude0e





Using this command

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';


i get this :

enter image description here

my.cnf file :

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]
default-storage-engine=MyISAM
innodb_file_per_table=1
performance-schema=0
innodb_buffer_pool_size=134217728
max_allowed_packet=268435456
open_files_limit=10000
local-infile=0
character-set-client-handshake = TRUE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci


To summary, the issue only occurs when i'm connecting to database using phpMyAdmin. Worst, when i try to import database using phpMyAdmin, all question marks are replaced by true "?", and datas are definitively lost.

EDIT 1

phpMyAdmin>Variables
enter image description here
Session values(utf8) are different to expected values(utf8mb4)

Answer

It's appear that's a phpMyAdmin bug (corrected at version 4.6.4).
When you use phpMyAdmin to connect to your database, the session's variables like character_set_client and character_set_results aren't initialized with the default values. See this commit.

If you have this bug and want import/export your database, don't use phpMyAdmin. As a workaround, perform the import action using this link, and the export action using the documentation at this link.