Can't insert a unicode glyph in mysql

Question

With this query:

UPDATE  `arg`.`arg_currency` SET  `symbol` =  '' WHERE  `arg_currency`.`id` =2 LIMIT 1 ;

I get this error:

Warning: #1366 Incorrect string value: '\xF0\x90\x80\xA4' for column 'symbol' at row 1

The column symbol was a CHAR(1) utf8_roman_ci, but I've both tried to change the collation to _general and to _unicode, and the chars to 4, but I've still got the same error.

What am I doing wrong?

(I've both tried to insert it via custom php code and via phpmyadmin)

@Pekka: it's a Linear B character http://www.fileformat.info/info/unicode/char/10024/index.htm — o0'., Mar 09 '11 at 14:12
@Lo yeah, just found it myself. Strange, looks like a perfectly fine utf-8 character to me. Can you try setting the type to `varchar`? Just to exclude the possibility — Pekka, Mar 09 '11 at 14:13
http://stackoverflow.com/questions/2692188/mysql-dont-want-to-store-unicode-charecter — CarpeNoctumDC, Mar 09 '11 at 14:20
@Carpe @Pekka: oh, true. Still unclear to me how to solve that, though... — o0'., Mar 09 '11 at 14:24
@Lo: What is your frontend? Have the frontend/app convert it to a number and then store the number... Then encode it when you retrieve it.. — CarpeNoctumDC, Mar 09 '11 at 14:26

score 6 · Accepted Answer · edited Jun 20 '20 at 09:12

6

As @CarpeNoctumDC points out, this question explains the problem:

MySQL don't want to store unicode character

MySQL only supports characters from the basic multilingual plane (0x0000 - 0xFFFF).

Your character is out if this plane.

In your case, I would work around it by storing the character as a numeric entity. ~~htmlentities() with the UTF-8 encoding specified should be able to entity it, for example.~~ no, it doesn't.

This function in the User Contributed Notes of the PHP manual works for me: It converts the character into a numeric entity.

echo utf8tohtml("", true); // echoes &#65572;

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 09 '11 at 14:28

Pekka

442,112
142
972
1,088

1

Oh even better, I'll just store it as a 4 byte unsigned int then! Thanks :) – o0'. Mar 09 '11 at 15:16
3

+1: But my actual reaction is WHAAT!? Doesn't MySQL support characters from any of the 16 supplementary planes? Ah, saw now that MySQL 5.5 and later supports UTF-16 and UTF-8. http://dev.mysql.com/doc/refman/5.5/en/charset-unicode.html – dalle Mar 09 '11 at 17:39
1

@dalle yeah, I'm baffled too! And I can't see a real reason for this, either. – Pekka Mar 09 '11 at 17:43

Adam Lukaszczyk · Answer 2 · 2011-03-09T14:19:28.200

-1

this '' in what encoding is?? first try to convert from your encoding to mysql table encoding in php using iconv() http://php.net/manual/en/function.iconv.php

UPDATE:

it looks like the '' was encoded on the fly to string \xF0\x90\x80\xA4, if your field is CHAR(1) it don't fit and return error - neeed to find why it is encoded.

edited Mar 09 '11 at 14:19

answered Mar 09 '11 at 14:11

Adam Lukaszczyk

4,898
3
22
22

It's a valid UTF-8 character: http://www.utf8-chartable.de/unicode-utf8-table.pl?start=65280&number=512 – Pekka Mar 09 '11 at 14:14
1

@Dobiatowski nope: A UTF-8 char(1) will hold one UTF-8 character, i.e. up to 4 bytes. Also the OP states he already tried setting the field's width to 4 – Pekka Mar 09 '11 at 14:20
@Pekka but i suppose when you change the field type to varchar(16) it will be saved but not as UTF char but as string of encoded symbol – Adam Lukaszczyk Mar 09 '11 at 14:23
@Dobiatowski I don't think so - why would it? It would be nice though if it did. Apparently, there's no way to store those characters natively. (See the duplicate) – Pekka Mar 09 '11 at 14:38
@Pekka you have right, i though that it was encoded to the string. – Adam Lukaszczyk Mar 09 '11 at 14:47

Can't insert a unicode glyph in mysql

2 Answers2