1

I am facing issues with my table structure :

My_Table_Name1

CREATE TABLE `My_Table_Name1` (
  `twitter_id_str` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `twitter_screen_name` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  UNIQUE KEY `twitter_id_str` (`twitter_id_str`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

For database I also have same charset and collation:

CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I'm trying to insert an emoji into this table:

insert into My_Table_Name1 values("2","😀") 

However, I get an error:

Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column 'twitter_screen_name' at row 1 0.00027 sec

How to solve this?

Thanks in advance.

  • When are you facing issues? While creating the table? Or when entering data? Please provide more details in your question by editing it. – hot2use May 11 '18 at 12:05
  • at the time of data insertion, i am trying to enter an emoji. 17:44:18 insert into My_Table_Name1 values("2","😀") Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column 'twitter_screen_name' at row 1 0.00027 sec – RAJ KASHWAN May 11 '18 at 12:12
  • Just tried this on MySQL 5.7 - The smiley gets converted to "\U+1F600" as I paste it into the mysql client, and running the insert then just works. – dbdemon May 11 '18 at 13:55
  • @RAJKASHWAN Maybe the problem is with mysql-workbench? It seems to work fine with the command-line client, but haven't tested with mysql-workbench. Are you using the most recent version? – dbdemon May 11 '18 at 14:17
  • I am using mysql5.7 – RAJ KASHWAN May 11 '18 at 18:38
2

The problem appears to be in the Client.

When the client connects to the MySQL server, it needs to announce that the bytes in the client are utf8mb4. This can be done in several ways:

  • In the connection parameters (client-dependent).
  • SET NAMES utf8mb4.

\U+1F600 is the Unicode representation for that Emoji.
\xF0\x9F\x98\x80 is the equivalent Hex.
F09F9880 is the UTF-8 (utf8mb4) in Hex.

Avoid the Unicode representation.

1

This seems like a problem with mysql-workbench (tested with version 6.3.9 on Fedora and MySQL 5.7 (CentOS)) where emojis are not converted to the correct unicode.

In the command-line mysql client the emojis get converted to codes when they're pasted in, so the query

INSERT INTO My_Table_Name1 values("2","😀"); 

becomes:

INSERT INTO My_Table_Name1 values("4","\U+1F600");

which works fine.

However, in mysql-workbench, the emoji is not converted as you paste it in, and the query result is:

Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column 'twitter_screen_name' at row 1

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.