mysql create table using select got "Incorrect string value:"-CodePudding

I want to create a table from performance_schema.events_statements_history_long, but got an error.

sql:

create table history select * from performance_schema.events_statements_history_long

error:

Incorrect string value: '\xF0\x9F\x9F\xA8\xE8\xAD...' for column 'SQL_TEXT' at row 1108

character set

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Variable_name   Value
character_set_client    utf8
character_set_connection    utf8
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8
character_set_server    utf8
character_set_system    utf8
collation_connection    utf8_general_ci
collation_database  utf8_general_ci
collation_server    utf8_general_ci

I have tried many times, but didn't work.

character set is utf8, i have also tried utf8mb4, and the result was the same.

Did anybody know what is the reason?

CodePudding user response：

Look at your database default encoding

CodePudding user response：

You need utf8mb4, not utf8. The clue is that 4-byte hex starting with F0.

F09F9FA8