How to Fix \'Incorrect string value\' Exception When Inserting Emojis into MySQL?

Resolving Incorrect String Value Exception When Inserting Emoji

When attempting to insert a string containing emoji characters into a MySQL database using JDBC, you may encounter the following exception:

java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...'

This error arises because MySQL's default utf8 character set only supports characters within the Basic Multilingual Plane. To resolve this issue, you need to enable support for the utf8mb4 character set, which extends utf8 to cover the extended plane where emoji characters reside.

Connection Initialization

Set the connection encoding to utf8mb4 immediately after establishing the connection:

DriverManager.getConnection("jdbc:mysql://localhost:3306/database", "username", "password").prepareStatement("SET NAMES 'utf8mb4'");

Database and Table Modifications

Update the character set and collation of your database and affected tables to utf8mb4:

ALTER DATABASE `database` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Java Code

Ensure that your Java code is using the utf8mb4 charset for string character encoding. For example, with Connector/J:

DriverManager.getConnection("jdbc:mysql://localhost:3306/database?characterEncoding=utf8mb4", "username", "password");
// ...
preparedStatement.setString(1, "walmart obama ?"); // Emoji characters should be supported now

By following these steps, you can enable support for utf8mb4 and resolve the incorrect string value exception when inserting emoji characters into your MySQL database.

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