How to Fix the MySQL 'Incorrect String Value' Error When Inserting UTF-8 Data via JDBC?

Troubleshooting "Incorrect String Value" Error When Inserting UTF-8 Into MySQL via JDBC

The "Incorrect string value" error can occur when attempting to insert UTF-8 data into a MySQL database using JDBC. This error typically indicates that the character encoding is not properly configured.

Issue Explanation

In your case, you have configured your JDBC connection with the following parameters:

Connection conn = DriverManager.getConnection(url + dbName + "?useUnicode=true&characterEncoding=utf-8", userName, password);

This configuration specifies that Unicode characters should be used and that the character encoding should be UTF-8. However, the error message suggests that the incorrect string value starts with "xF0," which is a 4-byte UTF-8 character.

Solution

By default, MySQL's utf8 encoding allows only characters that can be represented with 3 bytes in UTF-8. To resolve this issue, you need to specify the utf8mb4 encoding for your MySQL database. This encoding supports characters that occupy 4 bytes in UTF-8.

Steps to Resolve:

1. Change the column encoding of the problematic column to utf8mb4 using the following query:

ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET utf8mb4;

2. If you are using MySQL 5.5 or later, set the server property character_set_server to utf8mb4 in the MySQL configuration file.
3. Ensure that Connector/J autodetects the UTF-8 setting by leaving characterEncoding out of the connection string.

Additional Information:

• For more information on Unicode character sets in MySQL, refer to the official MySQL documentation: [https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html](https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html)
• Connector/J also provides detailed instructions on configuring UTF-8 support: [https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charset.html](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charset.html)

The above is the detailed content of How to Fix the MySQL 'Incorrect String Value' Error When Inserting UTF-8 Data via JDBC?. For more information, please follow other related articles on the PHP Chinese website!