0

For all I know it must be utf8mb4_unicode_ci, but:

> create table t1 (f1 varchar(255) collate utf8mb4_unicode_ci)
> insert into t1 (f1) values ('𢄂')

And what I get is:

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

The symbol I'm inserting into the database is this one.

  • Context? What client are you using? – Rick James Nov 18 '16 at 2:32
  • The commands above I executed in mysql client. I ran into the issue when running a ruby script. But like I said in my answer, it didn't cross my mind to change connection character set as well. – x-yuri Nov 18 '16 at 19:48
0

The issue was with connection character set. After setting it to utf8mb4, it succeeded. One way is to add default-character-set = utf8mb4 to client or mysql sections of ~/.my.cnf.

UPD Before adding default-character-set to ~/.my.cnf I had:

> show variables like 'character_set_%'
character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8

And insert into t1 (f1) values ('𢄂') gave me:

ERROR 1366 (22007) at line 1: Incorrect string value: '\xF0\xA2\x84\x82'
    for column 'f1' at row 1

After adding default-character-set = utf8mb4 I have:

> show variables like 'character_set_%'
character_set_client    utf8mb4
character_set_connection    utf8mb4
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8mb4
character_set_server    latin1
character_set_system    utf8

And insert into t1 (f1) values ('𢄂') succeeds.

  • Is that sufficient? Or do you still have problems? – Rick James Nov 18 '16 at 20:32
  • Actually yes. And now that I look at the title, the answer seems a bit off. But for all I know utf8mb4 is generally the best option, when you deal with all kinds of characters. So the answer can be read between the lines. Or feel free to give yours. – x-yuri Nov 18 '16 at 22:50
  • I worry that a single setting in my.cnf may not suffice. – Rick James Nov 19 '16 at 0:47
  • From my experience it will. There are two mandatory things to do: 1) change column character set, 2) change connection character set. What else do you think is needed? – x-yuri Nov 19 '16 at 11:58
  • 3 settings by client (what SET NAMES does); html meta. But I will experiment with your single-setting solution. See Best Practice. – Rick James Nov 19 '16 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.