文档章节

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

 开始奔跑的老农
发布于 2016/06/24 16:45
字数 465
阅读 33
收藏 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: '\xF0\x9F\x91\x91\xE5\xB0...' for column NICK_NAME 发现日志里有挺多这种错误,是什么原因造成的,是写入名称的时候会报这个......

地瓜干
2016/02/15
1K
2
tf.train.match_filenames_once 获取符合正则表达式的文件列表

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

隐士2018
2018/07/10
0
0
mysql 插入数据时,出现"\xF0\x9F\x8F\x80"这种情况的处理!

问题 Incorrect string value: ‘xF0x9Fx98x82xF0x9F…’ for column ‘question’ at row 1 我们知道设置数据库编码格式,表编码格式和字段编码格式的时候,一般设置为“utf-8”,这对于汉字...

灌南高手No1
01/14
0
0
再次遇到的不得不解决的问题

我在做使用java对数据库mysql插入数据的时候报出来的错误如下:java.sql.SQLException: Incorrect string value: '\xF0\x9F\x90\xB7\xE8\xBE...' for column 'commentDesc' at row 1 at com......

awang
2012/11/14
1K
3
java后台怎么保存IOS表情

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

大海
2016/06/23
956
7

没有更多内容

加载失败,请刷新页面

加载更多

cnetos7+docker+rancher构建基于DevOps的全自动CI【01】

来自DevOps实践分享,分享从开发代码到生产环境部署的一条龙操作的实践及经验, 包含工具技术的选型及考量、私有代码库与私有镜像库的应用等。 1、环境选择 安装Rancher环境,一定要在干净的...

Elson
19分钟前
1
0
21分钟教会你分析MaxCompute账单

背景 阿里云大计算服务MaxCompute是一款商业化的大数据分析平台,其计算资源有预付费和后付费两种计费方式。并且产品每天按照project为维度进行计量计费(账单基本情况下会第二天6点前产出)...

zhaowei121
23分钟前
0
0
CTO职场解惑指南系列(一)

基于科技能够改变世界的事实,几乎每个公司的程序员都自带闪光灯。程序员的手和普通人的手自然是有区别的,“我们可是用双手改变了世界” 。(码农真的是靠双手吃饭,呵呵) 这个世界上但凡靠...

阿里云云栖社区
27分钟前
2
0
css实现图片自适应容器宽高

css实现图片自适应容器宽高的做法一般如下所示 <style>div{width: 200px; height: 200px}div img{width: 100%; height: 100%}</style><div><img src="xxxx.png" /></div> 当外层容......

小草先森
28分钟前
3
0
PlatON在CentOS上编译部署

本文作者为万向区块链CTO罗荣阁。 目录 PlatON在CentOS上编译部署 1. CentOS 环境准备 1.1. 使用rpm 安装devtoolset-7 1.2. 使用rpm 安装dos2unix 1.3. 准备PlatON代码 1.4. 确保build脚本正...

万向区块链
36分钟前
10
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部