Why are my Emojis Disappearing in MySQL?

MySQL Character Encoding and Emoji Storage

We have an iPhone application that transmits emoticons to a PHP backend, which then inserts them into a MySQL database. However, after successful insertion, the stored value appears blank.

Text-only inserts are successful, but when emoticons are included, only the text gets inserted. Suggestions to change the field type to Blog (to accommodate image data) proved unsuitable due to occasional non-emoticons inserts and small storage requirements.

Solution: MySQL Character Set Compatibility

The issue lies in MySQL's character encoding. iOS emojis primarily utilize code points exceeding the Basic Multilingual Plane (BMP) of the Unicode table. For instance, the "Smiling Face with Open Mouth and Smiling Eyes" emoji resides at U 1F604.

MySQL versions prior to 5.5 only support UTF-8 for BMP, limiting storage to characters under U FFFF. Consequently, MySQL cannot store the U 1F604 emoji or other "high characters."

To solve this, use MySQL 5.5 and select utf8mb4 (authentic UTF-8), utf16, or utf32 as the column character set. Additionally, ensure the MySQL connection between PHP and the database uses the same charset.

For MySQL versions less than 5.5, use the BLOB column type, which stores raw bytes without regard for "characters." While this allows for emoji storage, it compromises text searching and indexing efficiency.

The above is the detailed content of Why are my Emojis Disappearing in MySQL?. For more information, please follow other related articles on the PHP Chinese website!