Why Am I Getting 'Invalid utf8 Character String' Errors When Saving Emojis in My MySQL utf8mb4 Database?

Saving Emojis in MySQL utf8mb4 Database: Troubleshooting Invalid Character Errors

When attempting to store names containing emojis in a MySQL database, errors like "Invalid utf8 character string" may arise despite proper configuration. One potential issue lies in inconsistent character set and collation settings.

To verify the configuration, execute the following query:

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

The output should display the following settings for the database in question:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Ensure that the values for character_set_client, character_set_connection, and character_set_results are all set to utf8mb4. If not, adjust these settings accordingly.

Additionally, check if the my.cnf configuration file contains any inconsistencies. Search for settings that specify character sets and collations and make sure they are consistent with the database configuration. For example, verify that these lines are present:

character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

If these settings are not present or incorrect, modify them as needed and restart the MySQL service.

Finally, consider executing the following query to set the character set and collation for the current connection:

SET NAMES utf8mb4;

This will ensure that all three character set variables are set correctly.

By following these steps, you can resolve the issue of invalid UTF-8 character errors when saving emojis in a MySQL database with utf8mb4 configuration.

The above is the detailed content of Why Am I Getting 'Invalid utf8 Character String' Errors When Saving Emojis in My MySQL utf8mb4 Database?. For more information, please follow other related articles on the PHP Chinese website!