“不正确的字符串值"尝试通过JDBC将UTF-8插入MySQL时? [英] "Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC?

问题描述

这是设置我的连接的方式:
Connection conn = DriverManager.getConnection(url + dbName + "?useUnicode=true&characterEncoding=utf-8", userName, password);

This is how my connection is set:
Connection conn = DriverManager.getConnection(url + dbName + "?useUnicode=true&characterEncoding=utf-8", userName, password);

在尝试向表中添加行时出现以下错误:
Incorrect string value: '\xF0\x90\x8D\x83\xF0\x90...' for column 'content' at row 1

And I'm getting the following error when tyring to add a row to a table:
Incorrect string value: '\xF0\x90\x8D\x83\xF0\x90...' for column 'content' at row 1

我要插入数千条记录，并且当文本包含\ xF0时总是会出现此错误(即，不正确的字符串值始终以\ xF0开头).

I'm inserting thousands of records, and I always get this error when the text contains \xF0 (i.e. the the incorrect string value always starts with \xF0).

该列的排序规则是utf8_general_ci.

The column's collation is utf8_general_ci.

可能是什么问题?

推荐答案

MySQL的utf8仅允许使用UTF-8中的3个字节表示的Unicode字符.在这里，您有一个需要4个字节的字符:\ xF0 \ x90 \ x8D \ x83(

MySQL's utf8 permits only the Unicode characters that can be represented with 3 bytes in UTF-8. Here you have a character that needs 4 bytes: \xF0\x90\x8D\x83 (U+10343 GOTHIC LETTER SAUIL).

如果您具有MySQL 5.5或更高版本，则可以将列编码从utf8更改为

If you have MySQL 5.5 or later you can change the column encoding from utf8 to utf8mb4. This encoding allows storage of characters that occupy 4 bytes in UTF-8.

您可能还必须在MySQL配置文件中将服务器属性character_set_server设置为utf8mb4.似乎 Connector/J默认为3字节否则为Unicode :

You may also have to set the server property character_set_server to utf8mb4 in the MySQL configuration file. It seems that Connector/J defaults to 3-byte Unicode otherwise:

例如，要将4字节UTF-8字符集与Connector/J一起使用，请将MySQL服务器配置为character_set_server=utf8mb4，并将characterEncoding保留在Connector/J连接字符串之外.然后，Connector/J将自动检测UTF-8设置.

For example, to use 4-byte UTF-8 character sets with Connector/J, configure the MySQL server with character_set_server=utf8mb4, and leave characterEncoding out of the Connector/J connection string. Connector/J will then autodetect the UTF-8 setting.

这篇关于“不正确的字符串值"尝试通过JDBC将UTF-8插入MySQL时?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！