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 character set of the RDS for MySQL instance is incorrectly configured.

• An emoji is a special character and needs to be stored in a 4-byte character set.
• In this scenario, the MySQL character set is utf-8, which supports a maximum of three bytes. You need to change the character set to utf8mb4 to support 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 CONVERT TO CHARACTER 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 RDS for MySQL server to utf8mb4.