Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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.
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.
SET NAMES does); html meta. But I will experiment with your single-setting solution. See Best Practice.
Nov 19, 2016 at 16:00
mysqlclient. I ran into the issue when running arubyscript. But like I said in my answer, it didn't cross my mind to change connection character set as well.