MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT _utf8mb4 0xF09F91BE AS "UTF-8 bytes in
0x notation",
       _utf8mb4 X'F09F91BE' AS "UTF-8 bytes in
X'' notation",

       _utf32 0x1F47E AS "Code Point in
0x notation",
       _utf32 X'01F47E' AS "Code Point in
X'' notation";


UTF-8 bytes in
0x notation
UTF-8 bytes in
X'' notation
Code Point in
0x notation
Code Point in
X'' notation


👾
👾
👾
👾


# BMP Character ( U+0F02 = ༂ ):
SELECT _utf8 0xE0BC82,    # 3-byte (BMP-only) UTF-8
       _utf8mb4 0xE0BC82, # Full UTF-8
       _utf16 0xF02,      # UTF-16 (implied Big Endian)
       _utf16le 0x020F,   # UTF-16 Little Endian
       _utf32 0xF02;      # Code Point / UTF-32


_utf8 0xE0BC82
_utf8mb4 0xE0BC82
_utf16 0xF02
_utf16le 0x020F
_utf32 0xF02


༂
༂
༂
༂
༂


# Supplementary Character ( U+1F47E = 👾 ):
SELECT _utf16 0xD83DDC7E,   # UTF-16 (implied Big Endian) Surrogate Pair
       _utf16le 0x3DD87EDC, # UTF-16 Little Endian Surrogate Pair
       _utf32 0x1F47E;      # Code Point / UTF-32


_utf16 0xD83DDC7E
_utf16le 0x3DD87EDC
_utf32 0x1F47E


👾
👾
👾


# CHAR(0xHEX USING encoding) function:
SELECT CHAR(0xF09F91BE USING utf8mb4) AS "UTF-8 bytes",
       CHAR(0xD83DDC7E USING utf16) AS "UTF-16 Big Endian
Surrogate Pair",
       CHAR(0x3DD87EDC USING utf16le) AS "UTF-16 Little Endian
Surrogate Pair",
       CHAR(0x0001F47E USING utf32) AS "Code Point /
UTF-32",
       CHAR(0x1F47E USING utf32) AS "Code Point / UTF-32
(implied leading zeros)";


UTF-8 bytes
UTF-16 Big Endian
Surrogate Pair
UTF-16 Little Endian
Surrogate Pair
Code Point /
UTF-32
Code Point / UTF-32
(implied leading zeros)


👾
👾
👾
👾
👾