How to Fix 'Incorrect String Value' Errors When Inserting Unicode Text into MySQL using JDBC?

Incorrect String Value Error with UTF-8 Insertion in MySQL via JDBC

When attempting to insert Unicode text into a MySQL database using JDBC, users may encounter an "Incorrect string value" error, specifically when the text contains the byte sequence xF0. This issue often manifests with large datasets and can be attributed to MySQL's default encoding limitations.

Cause of the Error:

MySQL's utf8 encoding supports only Unicode characters representable in three bytes. However, certain Unicode characters, such as xF0x90x8Dx83, require four bytes in UTF-8. Attempting to insert such characters into a utf8-encoded column triggers the error.

Solution:

To resolve the issue, two approaches can be taken:

1. Change Column Encoding:

   Update the column's encoding to utf8mb4, which supports four-byte Unicode characters. This can be done using the following SQL statement:

   ALTER TABLE <table_name> MODIFY COLUMN <column_name> TEXT COLLATE utf8mb4;</column_name></table_name>

2. Configure Server Properties:

   If using MySQL version 5.5 or later, set the character_set_server property in the MySQL configuration file to utf8mb4 to enable the server to handle four-byte Unicode characters.

   character_set_server = utf8mb4

Connector/J Configuration:

Connector/J autodetects the UTF-8 setting when the characterEncoding parameter is omitted from the connection string. Therefore, for four-byte UTF-8 character sets, leave characterEncoding out and set character_set_server as described above.

The above is the detailed content of How to Fix 'Incorrect String Value' Errors When Inserting Unicode Text into MySQL using JDBC?. For more information, please follow other related articles on the PHP Chinese website!