Inserting a surrogate pair into MySQL with an INSERT statement

Question

I'm trying to insert a surrogate pair ('', \uD852\uDF62, the same as U+24B62 from this example) into MySQL.

An INSERT with an unescaped literal, suggested by this answer:

INSERT INTO unicode_test (value) VALUES ('');
-- or
INSERT INTO unicode_test (value) VALUES (_utf8'');

fails with

Error Code: 1366. Incorrect string value: '\xF0\xA4\xAD\xA2' for column 'value' at row 1

(note that \xF0\xA4\xAD\xA2 isn't even close to the original value of \uD852\uDF62).

On the other hand, both

INSERT INTO unicode_test (value) VALUES (_utf16'');

and

INSERT INTO unicode_test (value) VALUES (_utf8mb4'');

succeed, but the inserted values are different from the original one.

My database uses the utf8mb4 character set, so I assume it should handle surrogates transparently.

What is the recommended way of inserting non-BMP characters into MySQL?

`\xF0\xA4\xAD\xA2` is _UTF-8_ expression of codepoint `U+24b62` (or `\uD852\uDF62` in _UTF-16_). — JosefZ, Aug 02 '17 at 15:01
Another reference: http://unicode.scarfboy.com/?s=%F0%A4%AD%A2 — Rick James, Aug 13 '17 at 23:15

score 1 · Accepted Answer · answered Aug 13 '17 at 23:20

Use CHARACTER SET utf8mb4, not utf8 or utf16.

What you tried should have worked:

INSERT INTO unicode_test (value) VALUES ('');

Another approach is

INSERT INTO unicode_test (value) VALUES (UNHEX('F0A4ADA2'));

1 Answers1