Storing Emojis in MySQL, PostgreSQL, and SQLite Without Data Loss
Emojis sit above U+FFFF and require four byte UTF-8. Here is how to configure each major database so emojis survive the round trip. I have lost count of how many times I have seen an emoji turn into a row of question marks after being saved to a database. The bug is always the same root cause: somewhere in the storage stack, the character set is not UTF-8 or the UTF-8 implementation does not support four byte characters. Emojis sit above U+FFFF, which means they require a UTF-8 implementation that handles four byte sequences, and not every default configuration does. Here is how to set up each major database to store emojis correctly. The most recent instance was a production system that had been running for two years before anyone tried to store an emoji. The system used MySQL with the default utf8 charset, which is actually a 3-byte subset of UTF-8. Every emoji above U+FFFF was silently replaced with question marks. The bug had been present since launch but was only discovered when a user filed a support ticket about their display name showing as??? instead of their chosen emoji. MySQL and the utf8mb4 Requirement MySQL has two UTF-8 character sets: utf8 and utf8mb4. The utf8 character set, despite the name, only supports up to three byte sequences, which covers the BMP but not the supplementary plane where most emojis live. Read the full article on Emoji Reference, and copy any emoji mentioned from the catalog of 1303+ entries on the home page.