尝试通过 JDBC 将 UTF-8 插入 MySQL 时出现“不正确的字符串值”？

"Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC?

提问by Lior

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

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

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

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

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).

我正在插入数千条记录，当文本包含 \xF0 时，我总是收到此错误（即错误的字符串值总是以 \xF0 开头）。

The column's collation is utf8_general_ci.

该列的排序规则是 utf8_general_ci。

What could be the problem?

可能是什么问题呢？

回答by Joni

MySQL's utf8permits 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).

MySQLutf8只允许 UTF-8 中可以用 3 个字节表示的 Unicode 字符。这里有一个需要 4 个字节的字符：\xF0\x90\x8D\x83 ( U+10343 GOTHIC LETTER SAUIL)。

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

如果您有 MySQL 5.5 或更高版本，您可以将列编码从 更改utf8为utf8mb4. 这种编码允许以 UTF-8 存储占用 4 个字节的字符。

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

您可能还需要服务器属性设置character_set_server到utf8mb4MySQL的配置文件中 似乎Connector/J 默认为 3 字节 Unicode，否则：

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

例如，要在 Connector/J 中使用 4 字节 UTF-8 字符集，请使用 配置 MySQL 服务器character_set_server=utf8mb4，并characterEncoding省略 Connector/J 连接字符串。Connector/J 然后将自动检测 UTF-8 设置。

回答by Eric J.

The strings that contain \xF0are simply characters encoded as multiple bytesusing UTF-8.

包含的字符串\xF0只是使用 UTF-8编码为多个字节的字符。

Although your collation is set to utf8_general_ci, I suspect that the character encoding of the database, table or even column may be different. They are independent settings. Try:

虽然你的collat​​ion设置为utf8_general_ci，但我怀疑数据库、表甚至列的字符编码可能不同。它们是独立的设置。尝试：

ALTER TABLE database.table MODIFY COLUMN col VARCHAR(255)  
    CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Substitute whatever your actual data type is for VARCHAR(255)

将您的实际数据类型替换为 VARCHAR(255)

回答by madtracy

Got the same problem, to save the data with utf8mb4needs to make sure:

遇到同样的问题，保存数据utf8mb4需要确保：

1. character_set_client, character_set_connection, character_set_resultsare utf8mb4: character_set_clientand character_set_connectionindicate the character set in which statements are sent by the client, character_set_resultsindicates the character set in which the server returns query results to the client.
   See charset-connection.

2. the table and column encoding is utf8mb4

1. character_set_client, character_set_connection, character_set_results是utf8mb4：character_set_client和character_set_connection指示字符集，其中的语句由客户端发送，character_set_results指示字符集的服务器查询结果返回给客户端。
   请参阅字符集连接。

2. 表和列编码是 utf8mb4

For JDBC, there are two solutions:

对于JDBC，有两种解决方案：

Solution 1 (need to restart MySQL):

方案一（需要重启MySQL）：

1. modify my.cnflike the following and restart MySQL:

   [mysql]
   default-character-set=utf8mb4
   
   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_unicode_ci
   

1. 修改my.cnf如下并重新启动MySQL：

   [mysql]
   default-character-set=utf8mb4
   
   [mysqld]
   character-set-server=utf8mb4
   collation-server=utf8mb4_unicode_ci
   

this can make sure the database and character_set_client, character_set_connection, character_set_resultsare utf8mb4by default.

这可以确保数据库character_set_client, character_set_connection, character_set_results是utf8mb4在默认情况下。

1. restart MySQL

2. change the table and column encoding to utf8mb4

3. STOP specifying characterEncoding=UTF-8and characterSetResults=UTF-8in the jdbc connector,cause this will override character_set_client, character_set_connection, character_set_resultsto utf8

2. 重启 MySQL

3. 将表和列编码更改为 utf8mb4

4. STOP 指定characterEncoding=UTF-8和characterSetResults=UTF-8在 jdbc 连接器中，因为这将覆盖character_set_client, character_set_connection,character_set_results以utf8

Solution two (don't need to restart MySQL):

解决方案二（不需要重启MySQL）：

1. change the table and column encoding to utf8mb4

2. specifying characterEncoding=UTF-8in the jdbc connector,cause the jdbc connector doesn't suport utf8mb4.

3. write your sql statment like this (need to add allowMultiQueries=trueto jdbc connector):

   'SET NAMES utf8mb4;INSERT INTO Mytable ...';
   

1. 将表和列编码更改为 utf8mb4

2. characterEncoding=UTF-8在 jdbc 连接器中指定，因为 jdbc 连接器不支持utf8mb4.

3. 像这样写你的sql语句（需要添加allowMultiQueries=true到jdbc连接器）：

   'SET NAMES utf8mb4;INSERT INTO Mytable ...';
   

this will make sure each connection to the server, character_set_client,character_set_connection,character_set_resultsare utf8mb4.
Also see charset-connection.

这将确保与服务器的每个连接character_set_client,character_set_connection,character_set_results都是utf8mb4.
另请参阅字符集连接。

回答by V H

I wanted to combine a couple of posts to make a full answer of this since it does appear to be a few steps.

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

1. Above advice by @madtracey

1. 以上建议来自@madtracey

/etc/mysql/my.cnfor /etc/mysql/mysql.conf.d/mysqld.cnf

/etc/mysql/my.cnf或者 /etc/mysql/mysql.conf.d/mysqld.cnf

[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

Again from advice above all jdbc connections had characterEncoding=UTF-8and characterSetResults=UTF-8removed from them

再次来自上述所有 jdbc 连接的建议，characterEncoding=UTF-8并characterSetResults=UTF-8从中删除

With this set -Dfile.encoding=UTF-8appeared to make no difference.

与这一套-Dfile.encoding=UTF-8似乎没有什么区别。

I could still not write international text into db getting same failure as above

我仍然无法将国际文本写入数据库，并出现与上述相同的失败

Now using this how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8

现在使用这个how-to-convert-an-entire-mysql-database-characterset-and-collat​​ion-to-utf-8

Update all your db to use utf8mb4

更新所有数据库以使用 utf8mb4

ALTER DATABASE YOURDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Run this query that gives you what needs to be rung

运行此查询，为您提供需要响铃的内容

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%')

Copy paste output in editor replace all | with nothing post back into mysql when connected to correct db.

在编辑器中复制粘贴输出替换所有 | 连接到正确的数据库时，没有任何内容回发到 mysql。

That is all that had to be done and all seems to work for me. Not the -Dfile.encoding=UTF-8is not enabled and it appears to work as expected

这就是所有必须要做的事情，而且一切似乎都对我有用。不是 -Dfile.encoding=UTF-8未启用，它似乎按预期工作

E2A Still having an issue ?I certainly am in production so it turns out you do need to check over what has been done by above, since it sometimes does not work, here is reason and fix in this scenario:

E2A 仍有问题？我当然在生产中，所以事实证明你确实需要检查上面所做的事情，因为它有时不起作用，这是在这种情况下的原因和修复：

show create table user

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

You can see some are still latin attempting to manually update the record:

您可以看到有些人仍在尝试手动更新记录：

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

So let's narrow it down:

所以让我们缩小范围：

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)

In short I had to reduce the size of that field in order to get the update to work.

简而言之，我必须减小该字段的大小才能使更新生效。

Now when I run:

现在当我运行时：

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

It all works

一切正常

回答by crazy_phage

In my case, I tried everything above, nothing worked. I am pretty sure, my database looks like below.

就我而言，我尝试了上述所有方法，但没有任何效果。我很确定，我的数据库如下所示。

mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

Connection id:      12
Current database:   xxx
Current user:       yo@localhost
SSL:            Not in use
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.17-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:        /var/run/mysqld/mysqld.sock
Uptime:         42 min 49 sec

Threads: 1  Questions: 372  Slow queries: 0  Opens: 166  Flush tables: 1  Open tables: 30  Queries per second avg: 0.144

so, I look up the column charset in every table

所以，我在每个表中查找列字符集

show create table company;

It turns out the column charset is latin. That's why, I can not insert Chinese into database.

事实证明列字符集是拉丁语。这就是为什么我不能在数据库中插入中文。

 ALTER TABLE company CONVERT TO CHARACTER SET utf8;

That might help you. :)

那可能对你有帮助。:)

回答by ravi

I had the same problem in my rails project:

我在 Rails 项目中遇到了同样的问题：

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

Solution 1: before saving to db convert string to base64 by Base64.encode64(subject)and after fetching from db use Base64.decode64(subject)

解决方案 1：在保存到 db 之前将字符串转换为 base64， Base64.encode64(subject)然后从 db 中获取使用Base64.decode64(subject)

Solution 2:

解决方案2：

Step 1: Change the character set (and collation) for subject column by

步骤 1：通过以下方式更改主题列的字符集（和排序规则）

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

Step 2: In database.yml use

第 2 步：在 database.yml 中使用

encoding :utf8mb4

回答by shareef

just do

做就是了

ALTER TABLE `some_table` 
CHARACTER SET = utf8 , COLLATE = utf8_general_ci ;

ALTER TABLE `some_table` 
CHANGE COLUMN `description_with_latin_or_something` `description` TEXT CHARACTER SET 'utf8' NOT NULL ;

回答by Teo Mihaila

Assuming you are using phpmyadminto solve this error, follow these steps:

假设您使用phpmyadmin来解决此错误，请执行以下步骤：

1. phpMyAdmin
2. your_table
3. "Structure tab"
4. change the Collation of your field from latin1_swedish_ci(or whatever it is) to utf8_general_ci

1. phpMyAdmin
2. your_table
3. “结构选项卡”
4. 将您的字段的排序规则从latin1_swedish_ci（或其他任何内容）更改为utf8_general_ci

回答by Md Ashfak Chowdhury

I had this problem with my PLAY Java application. This is my stack trace for that exception:

我的 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.$anonfun$applyOrElse(Routes.scala:690)
  at play.core.routing.HandlerInvokerFactory$$anon.resultCall(HandlerInvoker.scala:134)
  at play.core.routing.HandlerInvokerFactory$$anon.resultCall(HandlerInvoker.scala:133)
  at play.core.routing.HandlerInvokerFactory$JavaActionInvokerFactory$$anon$$anon$$anon.invocation(HandlerInvoker.scala:108)
  at play.core.j.JavaAction$$anon.call(JavaAction.scala:88)
  at play.http.DefaultActionCreator.call(DefaultActionCreator.java:31)
  at play.core.j.JavaAction.$anonfun$apply(JavaAction.scala:138)
  at scala.concurrent.Future$.$anonfun$apply(Future.scala:655)
  at scala.util.Success.$anonfun$map(Try.scala:251)
  at scala.util.Success.map(Try.scala:209)
  at scala.concurrent.Future.$anonfun$map(Future.scala:289)
  at scala.concurrent.impl.Promise.liftedTree1(Promise.scala:29)
  at scala.concurrent.impl.Promise.$anonfun$transform(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.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(Action.scala:96)
  at scala.concurrent.Future.$anonfun$flatMap(Future.scala:304)
  at scala.concurrent.impl.Promise.$anonfun$transformWith(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(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

I was trying to save a record using io.Ebean. I fixed it by re creating my database with utf8mb4 collation, and applied play evolution to re create all tables so that all tables should be recreated with utf-8 collation.

我试图使用 io.Ebean 保存记录。我通过使用 utf8mb4 整理重新创建我的数据库来修复它，并应用游戏进化来重新创建所有表，以便所有表都应使用 utf-8 整理重新创建。

CREATE DATABASE inventory CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

回答by BTR Naidu

Its mostly caused due to some unicode characters. In my case it was the Rupee currency symbol.

它主要是由于一些 unicode 字符引起的。就我而言，它是卢比货币符号。

To quickly fix this, I had to spot the character causing this error. I copy pasted the entire text in a text editor like vi and replaced the troubling character with a text one.

为了快速解决这个问题，我必须找出导致这个错误的角色。我将整个文本复制粘贴到 vi 等文本编辑器中，并用文本替换了令人不安的字符。