INSERT gives Error Code: 1366. Incorrect string value: '\xF0\x9F\x98\x80' for column

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.

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.

StackExchange Database Administrators Q#206478, answer score: 6