Mysql – INSERT gives Error Code: 1366. Incorrect string value: ‘\xF0\x9F\x98\x80’ for column

MySQLmysql-5.7mysql-workbench

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.

Best Answer

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.

Related Question