Unicode (hexadecimal) character literals in MySQL

Question

Is there a way to specify Unicode character literals in MySQL?

I want to replace a Unicode character with an Ascii character, something like the following:

Click to copy

Update MyTbl Set MyFld = Replace(MyFld, "ẏ", "y")

But I'm using even more obscure characters which are not available in most fonts, so I want to be able to use Unicode character literals, something like

Click to copy

Update MyTbl Set MyFld = Replace(MyFld, "\u1e8f", "y")

This SQL statement is being invoked from a PHP script - the first form is not only unreadable, but it doesn't actually work!

Answer 1

You can specify hexadecimal literals (or even binary literals) using 0x, x'', or X'':

Click to copy

select  0xC2A2;
select x'C2A2';
select X'C2A2';

But be aware that the return type is a binary string, so each and every byte is considered a character. You can verify this with char_length:

Click to copy

select char_length(0xC2A2)

2

If you want UTF-8 strings instead, you need to use convert:

Click to copy

select convert(0xC2A2 using utf8mb4)

And we can see that C2 A2 is considered 1 character in UTF-8:

Click to copy

select char_length(convert(0xC2A2 using utf8mb4))

1

---

Also, you don't have to worry about invalid bytes because convert will remove them automatically:

Click to copy

select char_length(convert(0xC1A2 using utf8mb4))

0

As can be seen, the output is 0 because C1 A2 is an invalid UTF-8 byte sequence.

Answer 2

Thanks for your suggestions, but I think the problem was further back in the system.

There's a lot of levels to unpick, but as far as I can tell, (on this server at least) the command

Click to copy

set names utf8

makes the utf-8 handling work correctly, whereas

Click to copy

set character set utf8

doesn't.

In my environment, these are being called from PHP using PDO, for what difference that may make.

Thanks anyway!

Answer 3

You can use the hex and unhex functions, e.g.:

Click to copy

update mytable set myfield = unhex(replace(hex(myfield),'C383','C3'))