MySQL MySQL抛出不正确的字符串值错误

MySQL throws Incorrect string value error

提问by priya

I'm trying to store the following tweet into a longtext column / utf8 charset / MySQL 5.5. database with MyISAM storage on.

我正在尝试将以下推文存储到长文本列 / utf8 字符集 / MySQL 5.5 中。启用 MyISAM 存储的数据库。

We also tried utf8mb4, utf16, utf32 charsets but are unable to get past this issue.

我们还尝试了 utf8mb4、utf16、utf32 字符集，但无法解决这个问题。

tweet="@Dorable_Dimples: Okay enough of those #IfYouWereMines I'm getting dep
ressed. #foreveralone ?" lol yes

mysql> ALTER DATABASE foo CHARACTER SET utf8 COLLATE utf8_bin;

mysql> show variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/mysql-5.5.12.R1/share/charsets/ |

Incorrect string value: '\xF0\x9F\x98\x94\xE2\x80...' for column 'tweet' at row 1

Unable to store tweet "@Dorable_Dimples: Okay enough of those #IfYouWereM
ines I'm getting depressed. #foreveralone ?" lol yes
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCExcept
ion: could not insert
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMana
gerImpl.java:1387)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMana
gerImpl.java:1315)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMana
gerImpl.java:1321)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityMana
gerImpl.java:843)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(
SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.ja
va:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelpe
r.java:66)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(Abstra
ctReturningDelegate.java:64)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstract
EntityPersister.java:2345)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstract
EntityPersister.java:2852)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentity
InsertAction.java:71)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplica
te(AbstractSaveEventListener.java:320)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(Abstract
SaveEventListener.java:203)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(
AbstractSaveEventListener.java:129)
at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(E
JB3PersistEventListener.java:69)
at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(
DefaultPersistEventListener.java:179)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultP
ersistEventListener.java:135)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultP
ersistEventListener.java:61)
at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:808)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:782)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:786)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityMana
gerImpl.java:837)
... 5 more
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x94\xE2\x
80...' for column 'tweet' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.jav
a:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2330)
at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAnd
Extract(IdentityGenerator.java:94)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(Abstra
ctReturningDelegate.java:57)

回答by Danack

It's the character at the end of the tweet that's causing the problem.

导致问题的是推文末尾的角色。

It looks like an 'emoji' character aka japanese smiley face but it's not displaying for me in either Chrome or Safari.

它看起来像一个“表情符号”角色，也就是日本笑脸，但它在 Chrome 或 Safari 中都没有显示。

There are known issues storing 4byte utf characters in some versions of MySQL. Apparently you must use utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length and so can't store character which are outside of the Basic Multilingual Plane

在某些版本的 MySQL 中存储 4byte utf 字符存在已知问题。显然你必须使用 utf8mb4 来表示 4 字节的 UTF 字符，因为普通的 utf8 字符集只能表示最多 3 个字节的字符，因此不能存储基本多语言平面之外的字符

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

Which is news to me as it basically means that the utf8 datatype in MySQL isn't really proper utf8.

这对我来说是个新闻，因为它基本上意味着 MySQL 中的 utf8 数据类型并不是真正正确的 utf8。

There are suggestions of how to handle this here How to insert utf-8 mb4 character(emoji in ios5) in mysql?including:

这里有如何处理这个问题的建议 How to insert utf-8 mb4 character(emoji in ios5) in mysql? 包含：

"Also make sure your app layer sets its database connections' character set to utf8mb4. Double-check this is actually happening – if you're running an older version of your chosen framework's mysql client library, it may not have been compiled with utf8mb4 support and it won't set the charset properly. If not, you may have to update it or compile it yourself"

“还要确保您的应用程序层将其数据库连接的字符集设置为 utf8mb4。仔细检查是否确实发生了这种情况——如果您运行的是所选框架的 mysql 客户端库的旧版本，则它可能没有使用 utf8mb4 支持进行编译并且它不会正确设置字符集。如果没有，您可能需要更新它或自己编译”

If you're using Connector/J you need to set character_set_server=utf8mb4 in the connection config.

如果您使用 Connector/J，则需要在连接配置中设置 character_set_server=utf8mb4。

All your character sets should be utf8mb4, which you may have tried but aren't currently set.

您所有的字符集都应该是 utf8mb4，您可能已经尝试过，但目前尚未设置。

回答by FlipMcF

I like Danask57's answer - it's correct and the 'right' way to do it. (I up voted it myself)

我喜欢 Danask57 的回答——它是正确的，而且是“正确”的方法。（我自己投票）

However, another quick-and-dirty solution is to change the schema. use a varbinary or binary to store the tweet string:

然而，另一个快速而肮脏的解决方案是更改架构。使用 varbinary 或 binary 来存储推文字符串：

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

The upside is that you won't get any character set problems.

好处是你不会遇到任何字符集问题。

The downside is that your string comparison and sorting will be lost, and you won't be able to full text index the column.

缺点是您的字符串比较和排序将丢失，并且您将无法对该列进行全文索引。

Just a suggestion, but this is not the 'right' answer, just a quick and dirty solution that gets things working.

只是一个建议，但这不是“正确”的答案，只是一个快速而肮脏的解决方案，可以让事情发挥作用。

回答by mancini0

I had this exact issue. To solve, change the default encoding on the mysql server side to utf8mb4 following this excellent guide: http://mathiasbynens.be/notes/mysql-utf8mb4.

我有这个确切的问题。要解决此问题，请按照以下优秀指南将 mysql 服务器端的默认编码更改为 utf8mb4：http://mathiasbynens.be/notes/mysql-utf8mb4 。

Remember to restart your mysqld service after making changes to the configuration file.

请记住在更改配置文件后重新启动 mysqld 服务。

For me, I also needed to update the mysql jdbc driver to version 5.1.18 (from version 5.1.6). I have read somewhere that you must use at least version 5.1.14 for the mysql jdbc driver to play nicely with utf8mb4 character encoding. Hope this helps!

对我来说，我还需要将 mysql jdbc 驱动程序更新到 5.1.18 版（从 5.1.6 版开始）。我在某处读到过，你必须至少使用 5.1.14 版本的 mysql jdbc 驱动程序才能很好地使用 utf8mb4 字符编码。希望这可以帮助！

回答by Adrian Cornish

Why do you have text outside of the quotes in your example - ie 'lol yes'

为什么你的例子中有引号之外的文本 - 即'lol yes'

tweet="@Dorable_Dimples: Okay enough of those #IfYouWereMines I'm getting depressed. #foreveralone ?" lol yes

回答by chenio

the problem is in string "@". the engine database interprete like a special character. i do:

问题出在字符串“@”中。引擎数据库解释为一个特殊字符。我愿意：

   tweet="Dorable_Dimples: Okay enough of those #IfYouWereMines I'm getting dep

ressed. #foreveralone ?" lol yes

被拒绝。#foreveralone 吗？”哈哈是的