文档章节

MYSQL异常のIncorrect string value: '\xF0\x9F\x91\xBD\

 开始奔跑的老农
发布于 2016/06/24 16:45
字数 465
阅读 30
收藏 0

导致这个问题的原因是mssql的utf-8不支持表情字符,同样的问题在stackoverflow上已有人提出:

I have the following string value: "walmart obama 👽💔"

I am using MySQL and Java.

I am getting the following exception: `java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F...'

Here is the variable I am trying to insert into:

var1 varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL`

My Java code that is trying to insert "walmart obama 👽💔" is a preparedStatement. So I am using the setString() method.

It looks like the problem is the encoding of the values 👽💔. How can I fix this? Previously I was using Derby SQL and the values 👽💔 just ended up being two sqaures (I think this is the representation of the null character)

All help is greatly appreciated!

回答如下:

What you have is EXTRATERRESTRIAL ALIEN (U+1F47D) and BROKEN HEART (U+1F494) which are not in the basic multilingual plane(基本多语言平面). They cannot be even represented in java as one char, "👽💔".length() == 4. They are definitely not null characters and one will see squares if you are not using fonts that support them.

MySQL's utf8 only supports basic multilingual plane, and you need to use utf8mb4 instead:

For a supplementary character, utf8 cannot store the character at all, while utf8mb4 requires four bytes to store it. Since utf8 cannot store the character at all, you do not have any supplementary characters in utf8 columns and you need not worry about converting characters or losing data when upgrading utf8 data from older versions of MySQL.

So to support these characters, your MySQL needs to be 5.5+ and you need to use utf8mb4 everywhere. Connection encoding needs to be utf8mb4, character set needs to be utf8mb4 and collaction needs to be utf8mb4. For java it's still just "utf-8", but MySQL needs a distinction.

I don't know what driver you are using but a driver agnostic way to set connection charset is to send the query:

SET NAMES 'utf8mb4'

Right after making the connection.

See also this for Connector/J:

23.3.15.15: How can I use 4-byte UTF8, utf8mb4 with Connector/J?

To use 4-byte UTF8 with Connector/J configure the MySQL server with character_set_server=utf8mb4. Connector/J will then use that setting as long as characterEncoding has not been set in the connection string. This is equivalent to autodetection of the character set.

Adjust your columns and database as well:

var1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL

Again, your MySQL version needs to be relatively up-to-date for utf8mb4 support.

 

本文转载自:http://stackoverflow.com/questions/13653712/java-sql-sqlexception-incorrect-string-value-xf0-x9f-...

共有 人打赏支持
粉丝 0
博文 3
码字总数 502
作品 0
广州
私信 提问
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\x91\xE5\xB0...' for column

Caused by: java.sql.SQLException: Incorrect string value: 'xF0x9Fx91x91xE5xB0...' for column NICK_NAME 发现日志里有挺多这种错误,是什么原因造成的,是写入名称的时候会报这个错,而且...

地瓜干
2016/02/15
1K
2
Python下访问MYSQL的方法总结

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zwgdft/article/details/52822432 在Python下做过服务器开发的小伙伴对ORM技术一定都不陌生,ORM(Object-Re...

Mr-Bruce
2016/10/15
0
0
tf.train.match_filenames_once 获取符合正则表达式的文件列表

import as # 获取符合正则表达式的文件列表 r"C:UsersqykDesktop工作计划网络自研团队工作计划*" with as print(sess.run(file_names)) 结果 [b'C:\Users\qyk\Desktop\xe5xb7xa5xe4xbdx9cxe...

隐士2018
07/10
0
0
Mysql 字符集utf8mb4设置无效

背景:由于要解决java.sql.SQLException: Incorrect string value: 'xF0x9Fx92x94' for column 'name' at row 1异常,所以要修改mysql字符集,改为utf8mb4。 修改字符集参考文章如下(参考修...

心急如焚
08/13
0
0
java后台怎么保存IOS表情

java在后台接受到的表情,都是各种乱码,更不要说存入数据库了,在数据库响应表字段的编码改为utfmb4的情况下,有什么好办法? 插入数据库时提示 Cause: java.sql.SQLException: Incorrect ...

大海
2016/06/23
905
7

没有更多内容

加载失败,请刷新页面

加载更多

Rotate Array(leetCode189)

Given an array, rotate the array to the right by k steps, where k is non-negative. Example 1: Input: [1,2,3,4,5,6,7] and k = 3Output: [5,6,7,1,2,3,4]Explanation:rotate 1 s......

woshixin
38分钟前
1
0
给女朋友讲解什么是Optional【JDK 8特性】

前言 只有光头才能变强 前两天带女朋友去图书馆了,随手就给她来了一本《与孩子一起学编程》的书,于是今天就给女朋友讲解一下什么是Optional类。 至于她能不能看懂,那肯定是看不懂的。(学到...

Java3y
58分钟前
2
0
2019年六大新兴信息安全方向

导读 黑客攻击和网络犯罪的威胁正在不断升级,相应的技术“军备竞赛”正愈演愈烈,对于信息安全从业人员来说,掌握最新的信息安全工具,是在信息安全战争中生存下来的关键所在。 从特朗普的手...

问题终结者
今天
1
0
redis扩展-自定义PropertyPlaceholderConfigurer,在spring属性注入之前,手动将properteis合并到spring容器中

背景:spring容器启动过程中,通过PropertyPlaceholderConfigurer读取properties配置文件,并将properties配置文件中的值注入spring bean的属性中, PropertyPlaceholderConfigurer使用方式多...

燃犀
今天
0
0
PostgreSQL SPI 中的错误处理

PostgreSQL SPI 用于在 C 或是其他编程语言编写的扩展函数(存储过程)中调用数据库本身的解析器、规划器和执行器的功能,以及对 SQL 语句进行执行。 在最重要的一个函数 SPI_execute 的文档...

helloclia
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部