MySQL utf8mb4: Errors Occurring While Storing Emojis

You encounter errors when attempting to save names containing emojis to your MySQL database using utf8mb4 encoding. The issue stems from differences in the database-specific variables between the global settings and your specific database.

Database Configuration Verification

1. Verify that the global database variables are set to "utf8mb4" for "character_set" and "utf8mb4_unicode_ci" for "collation" using the query:

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

2. For the relevant database, ensure that the above variables are also set to "utf8mb4" and "utf8mb4_unicode_ci," respectively, using the same query within phpMyAdmin.

my.cnf Configuration

Inspect your my.cnf file to ensure the following settings are present:

[mysql]
default-character-set = utf8mb4

[mysqld]
...
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

mysqli/PDO Settings

If you're connecting via mysqli/PDO, set the following options:

$mysqli->set_charset('utf8mb4');

$dbh->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, 'SET NAMES utf8mb4');

Potential Solution

Executing the SQL statement "SET NAMES utf8mb4;" within a MySQL session sets the client, connection, and results character sets to utf8mb4, potentially resolving the issue.