在尝试了许多查询之后，终于成功了

ALTER TABLE
table_name
CHANGE column_name column_name 
varchar(256)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

我在我的rails项目中遇到了同样的问题:

Incorrect string value: '\xF0\xA9\xB8\xBDs ...' for column 'subject' at row1

解决方案1:在保存到db之前将字符串转换为base64通过base64 .encode64(subject) 然后使用Base64.decode64(subject)

解决方案2:

步骤1: 更改主题列的字符集(和排序规则)

ALTER TABLE t1 MODIFY
subject VARCHAR(255)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

步骤2:在数据库中。yml使用

encoding :utf8mb4

我的PLAY Java应用程序有这个问题。这是我对该异常的堆栈跟踪:

javax.persistence.PersistenceException: Error[Incorrect string value: '\xE0\xA6\xAC\xE0\xA6\xBE...' for column 'product_name' at row 1]
  at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:52)
  at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:192)
  at io.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:83)
  at io.ebeaninternal.server.persist.dml.DmlBeanPersister.insert(DmlBeanPersister.java:49)
  at io.ebeaninternal.server.core.PersistRequestBean.executeInsert(PersistRequestBean.java:1136)
  at io.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:723)
  at io.ebeaninternal.server.core.PersistRequestBean.executeNoBatch(PersistRequestBean.java:778)
  at io.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:769)
  at io.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:456)
  at io.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:406)
  at io.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:393)
  at io.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1602)
  at io.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1594)
  at io.ebean.Model.save(Model.java:190)
  at models.Product.create(Product.java:147)
  at controllers.PushData.xlsupload(PushData.java:67)
  at router.Routes$$anonfun$routes$1.$anonfun$applyOrElse$40(Routes.scala:690)
  at play.core.routing.HandlerInvokerFactory$$anon$3.resultCall(HandlerInvoker.scala:134)
  at play.core.routing.HandlerInvokerFactory$$anon$3.resultCall(HandlerInvoker.scala:133)
  at play.core.routing.HandlerInvokerFactory$JavaActionInvokerFactory$$anon$8$$anon$2$$anon$1.invocation(HandlerInvoker.scala:108)
  at play.core.j.JavaAction$$anon$1.call(JavaAction.scala:88)
  at play.http.DefaultActionCreator$1.call(DefaultActionCreator.java:31)
  at play.core.j.JavaAction.$anonfun$apply$8(JavaAction.scala:138)
  at scala.concurrent.Future$.$anonfun$apply$1(Future.scala:655)
  at scala.util.Success.$anonfun$map$1(Try.scala:251)
  at scala.util.Success.map(Try.scala:209)
  at scala.concurrent.Future.$anonfun$map$1(Future.scala:289)
  at scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:29)
  at scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:29)
  at scala.concurrent.impl.CallbackRunnable.run$$$capture(Promise.scala:60)
  at scala.concurrent.impl.CallbackRunnable.run(Promise.scala)
  at play.core.j.HttpExecutionContext$$anon$2.run(HttpExecutionContext.scala:56)
  at play.api.libs.streams.Execution$trampoline$.execute(Execution.scala:70)
  at play.core.j.HttpExecutionContext.execute(HttpExecutionContext.scala:48)
  at scala.concurrent.impl.CallbackRunnable.executeWithValue(Promise.scala:68)
  at scala.concurrent.impl.Promise$KeptPromise$Kept.onComplete(Promise.scala:368)
  at scala.concurrent.impl.Promise$KeptPromise$Kept.onComplete$(Promise.scala:367)
  at scala.concurrent.impl.Promise$KeptPromise$Successful.onComplete(Promise.scala:375)
  at scala.concurrent.impl.Promise.transform(Promise.scala:29)
  at scala.concurrent.impl.Promise.transform$(Promise.scala:27)
  at scala.concurrent.impl.Promise$KeptPromise$Successful.transform(Promise.scala:375)
  at scala.concurrent.Future.map(Future.scala:289)
  at scala.concurrent.Future.map$(Future.scala:289)
  at scala.concurrent.impl.Promise$KeptPromise$Successful.map(Promise.scala:375)
  at scala.concurrent.Future$.apply(Future.scala:655)
  at play.core.j.JavaAction.apply(JavaAction.scala:138)
  at play.api.mvc.Action.$anonfun$apply$2(Action.scala:96)
  at scala.concurrent.Future.$anonfun$flatMap$1(Future.scala:304)
  at scala.concurrent.impl.Promise.$anonfun$transformWith$1(Promise.scala:37)
  at scala.concurrent.impl.CallbackRunnable.run$$$capture(Promise.scala:60)
  at scala.concurrent.impl.CallbackRunnable.run(Promise.scala)
  at akka.dispatch.BatchingExecutor$AbstractBatch.processBatch(BatchingExecutor.scala:55)
  at akka.dispatch.BatchingExecutor$BlockableBatch.$anonfun$run$1(BatchingExecutor.scala:91)
  at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12)
  at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:81)
  at akka.dispatch.BatchingExecutor$BlockableBatch.run(BatchingExecutor.scala:91)
  at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:40)
  at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(ForkJoinExecutorConfigurator.scala:43)
  at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
  at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
  at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
  at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: java.sql.SQLException: Incorrect string value: '\xE0\xA6\xAC\xE0\xA6\xBE...' for column 'product_name' at row 1
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
  at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
  at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
  at io.ebeaninternal.server.type.DataBind.executeUpdate(DataBind.java:82)
  at io.ebeaninternal.server.persist.dml.InsertHandler.execute(InsertHandler.java:122)
  at io.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:73)
  ... 59 more

我想用io.Ebean保存一条记录。我通过使用utf8mb4排序规则重新创建我的数据库来修复它，并应用play evolution重新创建所有表，以便所有表都应该使用utf-8排序规则重新创建。

CREATE DATABASE inventory CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

提示:在AWS RDS上，你需要一个新的参数组为你的MySQL数据库的参数(而不是编辑my.cnf)

collation_connection: utf8mb4_unicode_ci collation_database: utf8mb4_unicode_ci collation_server: utf8mb4_unicode_ci character_set_client: utf8mb4 character_set_connection: utf8mb4 character_set_database: utf8mb4 character_set_results: utf8mb4 character_set_server: utf8mb4

注意:character_set_system保持“utf8”

这些SQL命令不会永久工作-只在会话中:

set character_set_server = utf8mb4;
set collation_server = utf8mb4_unicode_ci;

但是，需要注意的是，mysql连接器驱动程序版本必须高于5.1.47及以上。

我想结合几篇文章来完整地回答这个问题，因为它看起来确实是几个步骤。

以上建议来自@madtracey

-等一下-等一下

[mysql]
default-character-set=utf8mb4

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
##
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

同样，从上面的建议来看，所有jdbc连接都从它们中删除了characterEncoding=UTF-8和characterSetResults=UTF-8

使用这个set -Dfile。encoding=UTF-8似乎没有什么区别。

我仍然不能写入国际文本到db得到同样的失败，如上所述

现在使用这个如何将整个mysql数据库字符集和排序规则转换为utf-8

更新所有的数据库，使用utf8mb4

ALTER DATABASE YOURDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

运行这个查询，它将为您提供需要调用的内容

SELECT CONCAT(
'ALTER TABLE ',  table_name, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  ', 
'ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  ')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'YOURDB'
AND
(C.CHARACTER_SET_NAME != 'utf8mb4'
    OR
 C.COLLATION_NAME not like 'utf8mb4%')

在编辑器中复制粘贴输出，当连接到正确的db时，将所有|替换为无post回mysql。

这就是我要做的一切，而且似乎对我很有效。不是-Dfile。encoding=UTF-8未启用，它似乎按预期工作

还有问题吗? 我当然是在生产中，所以事实证明你确实需要检查上面所做的事情，因为它有时不起作用，这里是原因和解决方案:

show create table user

  `password` varchar(255) CHARACTER SET latin1 NOT NULL,
  `username` varchar(255) CHARACTER SET latin1 NOT NULL,

你可以看到一些仍然是拉丁文 试图手动更新记录:

ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

让我们缩小范围:

mysql> ALTER TABLE user change username username varchar(255) CHARACTER SET utf8mb4 not NULL;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes
mysql> ALTER TABLE user change username username varchar(100) CHARACTER SET utf8mb4 not NULL;
Query OK, 5 rows affected (0.01 sec)

简而言之，为了让更新工作，我必须减小字段的大小。

现在当我跑步的时候:

mysql> ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

都能正常工作