Error 1366 Reported When Data Containing Emojis Is Updated

Scenario

Error 1366 was reported when data containing emojis was inserted or updated.

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xB0\xE5\xA4...' for column 'username' at row 1 ;
uncategorized SQLException for SQL []; SQL state [HY000]; error code [1366];  
Incorrect string value: '\xF0\x9F\x90\xB0\xE5\xA4...' for column 'username' at row 1; 

Possible Causes

The cause is that the character set is incorrectly configured.

• An emoji is a special character and needs to be stored in a 4-byte character set.
• In this scenario, the database character set is utf-8, which supports a maximum of three bytes. The utf8mb4 character set supports a maximum of four bytes.

Solution

1. Change the character set for the field that stores emojis to utf8mb4.

   If a large number of tables and fields are involved, you are advised to set the encoding format of the tables and databases to utf8mb4. Sample commands:

   ALTER DATABASE database_name CHARACTER SET= utf8mb4 COLLATE= utf8mb4_unicode_ci;

   ALTER TABLE table_name CONVERTTOCHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

   ALTER TABLE table_name MODIFY Field name VARCHAR(128) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. If the character set for the field is already utf8mb4, set the character sets of the client and server to utf8mb4.