文档章节

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

 开始奔跑的老农
发布于 2016/06/24 16:45
字数 465
阅读 25
收藏 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
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
Python下访问MYSQL的方法总结

在Python下做过服务器开发的小伙伴对ORM技术一定都不陌生,ORM(Object-Relational Mapping),将关系数据库的表结构映射到对象上,隐藏了数据库操作背后的细节,简化了对数据操作的写法,使...

zwgdft
2016/10/15
0
0
java后台怎么保存IOS表情

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

大海
2016/06/23
905
7
Mysql 字符集utf8mb4设置无效

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

心急如焚
08/13
0
0

没有更多内容

加载失败,请刷新页面

加载更多

js实现产生n个随机数,并且随机数之和是固定值

function getrandom(minnum , maxnum ,total,size){ var num = total; //定义整数 var length= size; //定义多个整数的数量 var numArr = []; while(length > 1){ var rnd = Math.floor(Mat......

开源昕昕
7分钟前
0
0
精选Spring Boot三十五道必知必会知识点!

Spring Boot 是微服务中最好的 Java 框架. 我们建议你能够成为一名 Spring Boot 的专家。本文精选了三十五个常见的Spring Boot知识点,祝你一臂之力! 问题一 Spring Boot、Spring MVC 和 Sp...

Java填坑之路
8分钟前
1
0
MyBatis学习笔记

相关文档 mybatis深入理解(一)之 # 与 $ 区别以及 sql 预编译 MyBatis 处理sql中的 大于,小于,大于等于,小于等于

OSC_fly
9分钟前
0
0
Gradle从不同地方复制文件到一个文件夹/打zip包

复制 task copySDK(type: Copy, dependsOn: [":fatJarTask"]) { delete JAR_NAME delete SDK_OUT_PATH delete "$ROOT_BUILD_PATH/$SDK_ZIP_NAME" into('/jniLibs') {......

SuShine
9分钟前
0
0
CentOS关闭占用端口,修改Apache默认端口,并重启Apache

查找并关闭进程 在Linux系统中可以使用lsof命令和kill命令,两个命令配合查找并关闭占用端口的进程 查看某一端口使用情况的命令: lsof -i:端口号 效果如下,PID即进程的ID 根据PID关闭进程,...

临江仙卜算子
16分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部