文档章节

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

 开始奔跑的老农
发布于 2016/06/24 16:45
字数 465
阅读 402
收藏 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.

 

粉丝 0
博文 3
码字总数 502
作品 0
广州
私信 提问
加载中
请先登录后再评论。
访问安全控制解决方案

本文是《轻量级 Java Web 框架架构设计》的系列博文。 今天想和大家简单的分享一下,在 Smart 中是如何做到访问安全控制的。也就是说,当没有登录或 Session 过期时所做的操作,会自动退回到...

黄勇
2013/11/03
3.5K
8
Flappy Bird(安卓版)逆向分析(一)

更改每过一关的增长分数 反编译的步骤就不介绍了,我们直接来看反编译得到的文件夹 方法1:在smali目录下,我们看到org/andengine/,可以知晓游戏是由andengine引擎开发的。打开/res/raw/at...

enimey
2014/03/04
6.1K
18
我的架构演化笔记 功能1: 基本的用户注册

“咚咚”,一阵急促的敲门声, 我从睡梦中惊醒,我靠,这才几点,谁这么早, 开门一看,原来我的小表弟放暑假了,来南京玩,顺便说跟我后面学习一个网站是怎么做出来的。 于是有了下面的一段...

强子哥哥
2014/05/31
976
3
数据库代码辅助工具--MaoCaiJun.Database

MaoCaiJun.DataBase 是一个用于 Microsoft Visual Studio 的数据库代码生成组件。它是基于 xml 文件的代码创建工具,支持sql2000,sql2005,sql2008,access, SQLite MaoCaiJun.Database 数据库...

mccj
2013/02/06
2.4K
1
c-string转换工具集合--stringencoders

c-string转换工具集合,比标准实现快2倍以上(如果有的话)。——该项目被Google Chrome使用。 包括以下C字符串转换工具 base64, standard base64, web/url safe, with configurable alphabe...

江斌
2012/11/07
4.8K
1

没有更多内容

加载失败,请刷新页面

加载更多

Hacker News 简讯 2020-08-12

最后更新时间: 2020-08-12 00:01 Single Page Applications using Rust - (sheshbabu.com) 使用Rust的单页应用程序 得分:126 | 评论:68 The case for why Google should be regulated as a ......

FalconChen
15分钟前
21
0
在关系数据库中存储分层数据有哪些选择? [关闭]

问题: Good Overviews 良好的概述 Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). 一般来说,您......

fyin1314
昨天
7
0
创建myBatis项目

1、简介 1.1、核心组件 SqlSessionFactoryBuilder(构造器):根据配置信息或者代码生成SqlSessionFactory SqlSessionFactory(工厂接口):依靠工厂来生成SqlSession(会话) SqlSession(会话): ...

执键走天涯
昨天
5
0
Tomcat集群带来的问题和解决思路

Tomcat集群 存在问题 解决方案 基于Redis+Cookie+Jackson+Filter的原生解决集群Session共享问题 使用Spring Session零侵入解决Session共享 单点登录实现 Redis构建Session服务器 使用Redis+C...

code-ortaerc
昨天
17
0
小福利

点击有福利 本文分享自微信公众号 - V5codings(gh_c1ec2d16ec93)。 如有侵权,请联系 support@oschina.cn 删除。 本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。...

V5codings
2019/11/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部