MySQL抛出不正确的字符串值错误 [英] MySQL throws Incorrect string value error

问题描述

我试图将以下推文存储到长文本列/ utf8 charset / MySQL 5.5中。数据库与MyISAM存储上。

我们也试过utf8mb4，utf16，utf32字符集，但无法超越这个问题。

  tweet =@ Dorable_Dimples：好吧那些#IfYouWereMines我得到dep 
 ressed。#foreveralone？ lol是
 
 mysql> ALTER DATABASE foo CHARACTER SET utf8 COLLATE utf8_bin; 
 
 mysql>显示像'char％'这样的变量; 
 + -------------------------- + ------------------ ------------------------- + 
 |变量名|值| 
 + -------------------------- + ------------------ ------------------------- + 
 | character_set_client | utf8 | 
 | character_set_connection | utf8 | 
 | character_set_database | utf8 | 
 | character_set_filesystem |二进制| 
 | character_set_results | utf8 | 
 | character_set_server | latin1 | 
 | character_set_system | utf8 | 
 | character_sets_dir | /rdsdbbin/mysql-5.5.12.R1/share/charsets/ | 
 
错误的字符串值：'\ xF0\x9F\x98\x94\xE2\x80 ...'用于第1行的'tweet'列
 
无法存储推文@Dorable_Dimples：好的那些#IfYouWereM 
ines我变得很沮丧。#foreveralone？笑是
 javax.persistence.PersistenceException：org.hibernate.exception.GenericJDBCExcept 
离子：在org.hibernate.ejb.AbstractEntityManagerImpl.convert（AbstractEntityMana 
 gerImpl无法插入
。 Java的：1387）
在org.hibernate.ejb.AbstractEntityManagerImpl.convert（AbstractEntityMana 
 gerImpl.java:1315）
在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 ）
在java.util.TimerThread.run（Timer.java:462）
 
在org.hibernate.exception.SQLStateConverter.handledNonSpecificException（
 SQLStateConverter.java:140）
 at org.hibernate.exception.JDBCExceptionHelper.convert（JDBCExceptionHelpe 
 r.java:66）org.hibernate.exception.SQLStateConverter.convert（SQLStateConverter.ja 
 va：128）
 ）在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）
在org.hibernate.engine.ActionQueue.execute（ActionQueue.java:273）
。在org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplica 
 TE（AbstractSaveEventListener.java:320）
。在org.hibernate.event.def.AbstractSaveEventListener.performSave（摘要
 SaveEventListener.java:203）
。在org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId（
 AbstractSaveEventListener.java:129）在org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId 
（E 
 JB3PersistEventListener.java:69）
在org.hibernate.event.def.DefaultPersistEventListener.entityIsTrans ient（
 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 
导致：java.sql.SQLException：错误的字符串值：'\xF0\x9F\x98\x94\xE2\\ \\ x 
 80 ...'列1'列'tweet'
列出com.mysql.jdbc.SQLError.createSQLException（SQLError.java:1073）
列出com.mysql.jdbc .MysqlIO.checkErrorPacket（MysqlIO.java:3609）
在com.mysql.jdbc.MysqlIO.checkErrorPacket（MysqlIO.java:3541）
在com.mysql.jdbc.My sqlIO.sendCommand（MysqlIO.java:2002）
在com.mysql.jdbc.MysqlIO.sqlQueryDirect（MysqlIO.java:2163）
在com.mysql.jdbc.ConnectionImpl.execSQL（ConnectionImpl.java： 
 at com.mysql.jdbc.PreparedStatement.executeInternal（PreparedStatement.jav 
a：2127）
 at com.mysql.jdbc.PreparedStatement.executeUpdate（PreparedStatement.java：
 （PreparedStatement.java：
 2345）
 at com.mysql.jdbc.PreparedStatement.executeUpdate（PreparedStatement.java：
在org.hibernate.id.insert.AbstractReturningDelegate.performInsert在org.hibernate.id.IdentityGenerator $ GetGeneratedKeysDelegate.executeAnd 
提取2330）
（IdentityGenerator.java:94）
（Abstra 
 ctReturningDelegate.java:57）
  

解决方案

在引发问题的鸣叫结束时。

它看起来像一个'表情符号'字符aka日本笑脸，但它不是在任一Chrome或Safari显示我。

有已知的问题存储在MySQL的一些版本4个字节的UTF字符。显然你必须使用utf8mb4来表示4个字节的UTF字符，因为普通的utf8字符集只能表示最多3个字节的字符，所以不能存储超出基本多语言平面

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

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

有如何处理这里的建议
包括：

还要确保你的应用程序层设置其数据库ase connections'字符集设置为utf8mb4。仔细检查一下，这实际上是在发生 - 如果你正在运行你选择的框架的mysql客户端库的一个老版本，它可能没有被编译为支持utf8mb4，它不会正确设置字符集。如果不是，您可能需要自行更新或编译它。

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

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

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

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

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)

解决方案

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.

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

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.

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

"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"

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

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

这篇关于MySQL抛出不正确的字符串值错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！