How to Properly Store Emoji Characters in a MySQL Database?

Storing Emoji Characters in a MySQL Database

When attempting to store emoji characters in a MySQL database configured with the default utf8mb4_general_ci collation, you may encounter errors such as "Incorrect string value" or "Character set is not valid" due to the storage limitations of certain character sets.

To resolve this issue, follow these steps:

1. Database Collation:

• Change the default database collation to utf8mb4 by executing the following query:

  ALTER DATABASE `your_database_name` COLLATE utf8mb4_unicode_520_ci;

2. Table Collation:

• Change the table collation to utf8mb4_bin by modifying the table structure:

  ALTER TABLE `your_table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

3. Query:

• Use the utf8mb4 character set explicitly in your INSERT query:

  INSERT INTO `your_table_name` (column1, column2, column3)
  VALUES ('273', '3', 'Hdhdhdh????hzhzhzzhjzj 我爱你 ❌')

4. Database Connection:

• Set the utf8mb4 character set in your database connection:

  $mysqli = new mysqli($server, $username, $password, $database);
  $mysqli->set_charset('utf8mb4');

By implementing these changes, MySQL will be able to correctly store and retrieve emoji characters within the specified columns.

The above is the detailed content of How to Properly Store Emoji Characters in a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!