Letʼs examine MySQL. Prior to version 5.5, MySQL could only handle a part of Unicode, specifically the BMP (Basic Multilingual Plane). The reason being that itʼs Unicode UTF8 encoding is a 3 byte encoding. In order to access the whole of Unicode, a 4 byte UTF8 encoding is required. MySQL version 5.5 and greater have a 4 byte UTF8 encoding called utf8mb4 and thus can address and store the whole of Unicode.
Unicode has 17 planes, each of which has FFFF codepoints. The 2 most commonly used planes are: Plane 0, the BMP (Basic Multilingual Plane) and Plane 1, the SMP (Supplementary Multilingual Plane). The BMP codepoints range is 0➜FFFF and the SMP range is 10000➜1FFFF. A 3 byte UTF8 encoding can only address the BMP codepoints range 0➜FFFF. Therefore MySQL versions < 5.5 cannot handle SMP characters. More seriously, with MySQL versions < 5.5, if any SMP characters are used then on storage in a database the first encountered SMP character and all subsequent characters are discarded. The discarded characters cannot be recovered. There are still many systems with this problem. Even those systems that have been upgraded to MySQL 5.5 or greater can still have the problem because of associated code and/or tables not yet updated for 4 byte UTF8 encoding.
So, basically, SMP characters break some MySQL setups. I will refer to these breakable MySQL setups as BMP only MySQL. There are solutions and work arounds that can be used to make BMP only MySQL handle the whole of Unicode.
Letʼs first look at Emoji as they are hugely popular. Most Emoji are in the SMP but there are a small number Emoji which have been in Unicode for many a year which are in the BMP. These can be safely used with BMP only MySQL. I have listed those I have found below. There may be more. I have appended to each of these Emoji the single BMP character variation selector FE0F. This variation selector directs a rendering agent to use, if available, an emojified glyph for the Emoji. What these Emoji look like on your device will depend on the fonts your device has and the browser you are using. On my OSX Mac, all these Emoji look really good and they all have emojified glyphs. With my Android phone, only about half of the below Emoji have emojified glyphs.
The SMP contains much more than Emoji. SMP characters include: Cuneiform, Egyptian Hieroglyphs, Byzantine Musical Symbols, Mahjong Tiles, Playing Cards and much more. All these SMP characters will break BMP only MySQL. WRT web technologies we can fix this SMP breaking problem. Rather than encoding these SMP characters as UTF8 we can instead encode them as NCRs (Numeric Character References). NCRs only use ASCII characters and so are BMP only MySQL safe. ASCII is a subset of Unicode and occupies the first 128 codepoints of the BMP. NCRs take the form &#xnumber;. The NCR for the SMP character 🀣 is 🀣, the NCR for the SMP character 𓀬 is 𓀬 and the NCR for the SMP character 🎋 is 🎋.
‼️ ⁉️ ℹ️ ↔️ ↕️ ↖️ ↗️ ↘️ ↙️ ↩️ ↪️ ⌚️ ⌛️ ⌨️ ⏩️ ⏪️ ⏫️ ⏬️ ⏭️ ⏮️ ⏯ ⏰️ ⏱️ ⏲️ ⏳️ ⏸️ ⏹️ ⏺️ Ⓜ️ ▶️ ☀️ ☁️ ☂️ ☃️ ☄️ ☎️ ☑️ ☔️ ☕️ ☘️ ☝️ ☠️ ☢️ ☣️ ☦️ ☪️ ☮️ ☯️ ☸️ ☹️ ☺️ ♈️ ♉️ ♊️ ♋️ ♌️ ♍️ ♎️ ♏️ ♐️ ♑️ ♒️ ♓️ ♠️ ♣️ ♥️ ♦️ ♨️ ♻️ ♿️ ⚒️ ⚓️ ⚔️ ⚖️ ⚗️ ⚙️ ⚛️ ⚜️ ⚠️ ⚡️ ⚪️ ⚫️ ⚰️ ⚱️ ⚽️ ⚾️ ⛄️ ⛅️ ⛈️ ⛎️ ⛏️ ⛑️ ⛓️ ⛔️ ⛩️ ⛪️ ⛰️ ⛱️ ⛲️ ⛳️ ⛴️ ⛵️ ⛷️ ⛸️ ⛹️ ⛺️ ⛽️ ✂️ ✅️ ✈️ ✉️ ✊️ ✋️ ✌️ ✍️ ✏️ ✒️ ✝️ ✡️ ✨️ ✳️ ✴️ ❄️ ❇️ ❌️ ❎️ ❓️ ❔️ ❕️ ❗️ ❣️ ❤️ ➡️ ⬅️ ⬆️ ⬇️ ⭐️ ⭕️ 〽️ ㊗️ ㊙️
