Incorrect String Value Error with MySQL Emojis

When attempting to store a tweet containing emojis in a MySQL table, an error may occur due to incorrect string value encoding. The error message "Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' for column 'tweet_text' at row 1." indicates that the character set in use is unable to handle the special characters.

Solution:

To resolve this issue, the database character set must be changed.

1. Modify MySQL Configuration (my.ini):

   • Open the my.ini file and add the following setting:

     character-set-server=utf8mb4

2. Set Character Set and Collation:

   • Connect to MySQL and execute the following commands:

     SET NAMES utf8mb4;
     ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;

3. Verify Changes:

   • Use the following command to confirm that the changes are applied:

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

After completing these steps, the MySQL database will be able to handle the storage of emojis and other special characters in UTF-8 encoding without encountering the "Incorrect string value" error.