3

How to make full support of emoji in MySQL database? What have been done:

Upgrade the databases, tables and columns 

ALTER DATABASE database CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table CHANGE column column VARCHAR(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Optimizing tables

REPAIR TABLE table;
OPTIMIZE TABLE table;

This make small effect. Some emoji I can see in my table records ("heart", "cloud" and other), but other like "smiling face", "angry face" looks like ????.

MySQL version: 5.5.44

In the General Settings (phpMyAdmin) MySQL collation is utf8mb4_unicode_ci.

halfer
  • 19,824
  • 17
  • 99
  • 186
Dmitry
  • 2,963
  • 2
  • 21
  • 39
  • 1
    This might help you http://stackoverflow.com/questions/7814293/how-to-insert-utf-8-mb4-characteremoji-in-ios5-in-mysql – Bogdan Burym Jul 28 '15 at 19:12
  • Thank you, I will try to use this solution! – Dmitry Jul 28 '15 at 19:18
  • I tried to use the advice in this solution. When I added "SET NAMES utf8mb4" before queries - my emoji began to look like a "?" and not as "????" ... What am I doing wrong? P.S. other emoji("heart", "cloud") looks as it should be, but "smiling face", "angry face" and other looks like "?" – Dmitry Jul 28 '15 at 19:42
  • 2
    Bear in mind that the system you view the data with has to know how to display all the emoji, e.g. have the characters in the selected font. Since you're seeing some and not others, this seems a likely problem. – IMSoP Jul 28 '15 at 19:56
  • Thanks, but I'm working on the iMac and the iMac knows how to display emoji! Also, these emodji come from a database on iPhone, and Iphone also knows how to display them! it is 100%! – Dmitry Jul 28 '15 at 20:03
  • Type BLOB solved this problem... – Dmitry Jul 28 '15 at 21:50
  • You have so many wrong assumptions it's not even funny. Type "blob" is probably not what you need, but whatever - it's just a character type without charset information. And it can store a lot of data. Your program that transmits the data is supplying it using **wrong** charset in the first place. Yes, the program that pulls it out probably has no clue about character sets either. What @IMSoP wrote is 100% correct. At least try to understand why swapping the data types worked. It's a clear hint you have a problem somewhere and might have it again. – N.B. Jul 28 '15 at 22:22
  • [This](https://stackoverflow.com/questions/39463134/how-to-store-emoji-character-in-my-sql-database) may helps you, i tried with success result. – uruapanmexicansong Jun 04 '18 at 18:21

0 Answers0