How to Fix the \'Incorrect string value\' Error When Storing Emojis in MySQL?

Ambiguous Characters in MySQL: Resolving ''xF0x9Fx8ExB6xF0x9F...'' Error

When attempting to store a tweet containing the "MULTIPLE MUSICAL NOTES" emoji in a MySQL table with utf8mb4 encoding, an error message may occur indicating "Incorrect string value: 'xF0x9Fx8ExB6xF0x9F...'" for the tweet_text column.

The issue arises due to insufficient character set configuration. To resolve this, the following steps are necessary:

1. Modify MySQL Configuration (my.ini)

Update the my.ini file to change the character set to utf8mb4. For instance, add or modify the following line:

[mysql]
character-set-server=utf8mb4

2. Execute MySQL Commands

Connect to the MySQL client and run the following commands to adjust the database and table character sets:

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

3. Verify Changes

To ensure the modifications have taken effect, execute the following command:

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

The output should reflect that the character set and collation are both set to utf8mb4.

With these steps, the "MULTIPLE MUSICAL NOTES" emoji and other similar special characters can be stored successfully without encountering the "Incorrect string value" error.

The above is the detailed content of How to Fix the \'Incorrect string value\' Error When Storing Emojis in MySQL?. For more information, please follow other related articles on the PHP Chinese website!