CODEWITHSUNDEEP
mysqlphpmyadminemojiutf8mb4
hashchen
hashchen

Reputation: 1041

phpMyAdmin mysql saved Emoji as Question mark

I want to save Emoji into MySql database, and I realize, three bytes Emoji is saved correctly in the database, but 4 byte emoji have been saved as question marks. It seems like I did fully convert utf8 to utf8mb4, but I dont know what exactly is missing here. My MySQL version is 5.5.29, when I do a SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; in MySql shell, it shows the following: 

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Now, for testing purpose, I only have 1 database with 1 table created to test emoji saving. I created the database through phpMyAdmin, and created the table through MySql shell:

CREATE TABLE `test_emojis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

and it still does not work (still question marks).

However, I found something interesting, I see question marks in phpMyAdmin, but I can see emoji icon properly in Mysql shell if I type select * from test_emoji; any ideas?

Can someone help please?

Thanks

Upvotes: 6

Views: 4975

Answers (3)

Saif Warsi
Saif Warsi

Reputation: 1

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Upvotes: 0

James Gwee
James Gwee

Reputation: 341

Upgrade your phpMyAdmin to >= 4.3.9 and problem solved.

Upvotes: 3

Michal Čihař
Michal Čihař

Reputation: 10091

phpMyAdmin has hardcoded utf8 charset so you would have to edit it's code to change this. For future versions it's fixed in fb30c14 (this shows you also where to change these values).

Upvotes: 3

Related Questions