MySQL unicode literals

If the goal is to specify the code point instead of the encoded byte sequence (i.e. 0x0F02 instead of the UTF-8 0xE0BC82 for "༂"), then you need to use an encoding in which the code point value just happens to be the encoded byte sequence. For example, "0xE28098" is the UTF-8 encoded byte sequence for the " ‘ " character (as shown in dkamins's answer), which is code point U+2018. However, 0x2018 is both the code point value for ‘ and the encoded byte sequence for ucs2 / utf16 (they are effectively the same encoding for BMP characters, but I prefer to use "utf16" as it is consistent with "utf8" and "utf32", consistent in the "utf" theme). Hence:

_utf16 0x2018

returns the same ‘ character as:

_utf8 0xE0BC82

But, utf16 only works for BMP characters (code points U+0000 - U+FFFF) in terms of specifying the code point value. If you want a Supplementary Character (by specifying the code point instead of a specific encoding's sequence of bytes), then you will need to use the utf32 encoding. Not only does _utf32 0x2018 return ‘, but:

_utf32 0x1F47E

returns: 👾

To use either UTF-8 or UTF-16 encodings for that same Supplementary Character would require the following:

_utf8mb4 0xF09F91BE

_utf16 0xD83DDC7E

HOWEVER, if you are having trouble adding this to a string that is already utf8, then you will need to convert this into utf8 (or into utf8mb4 when creating Supplementary Characters as the utf8 encoding / charset can only handle BMP characters):

CONVERT(_utf32 0x1F47E USING utf8mb4)

Or, using the example character from Michael - sqlbot's answer:

CONVERT(_utf32 0x2192 USING utf8)

returns a →. Hence, a custom function is not needed in order to create a UTF-8 encoded character from its code point (at least not as of MySQL 8.0). Here is a test query

SELECT _utf32 0x1F47E AS "Supplementary Character in utf32",
       CONVERT(_utf32 0x1F47E USING utf8mb4) AS "Supplementary Character in utf8mb4",
       CHARSET(CONVERT(_utf32 0x1F47E USING utf8mb4)) AS "Proof",

       "---" AS "---",

       _utf32 0x2192 AS "BMP character in utf32",
       CONVERT(_utf32 0x2192 USING utf8) AS "BMP character in utf8",
       CHARSET(CONVERT(_utf32 0x2192 USING utf8)) AS "Proof";

And you can see it working on db<>fiddle (might not work in pre-8.0 MySQL).

For more details on these options, plus Unicode escape sequences for other languages and platforms, please see my post:

Unicode Escape Sequences Across Various Languages and Platforms (including Supplementary Characters)