java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F…'

数据库 Java MySQL
创建时间:
字数:2.7k 阅读: 评论:
  1. 问题描述
    1. 错误详情
    2. 升级步骤
  2. 1118 - Row size too large.
    1. mysql 数据库中varchar的长度与字节，字符串的关系
  3. 如何检测、替换、去除4个字节的utf-8编码
    1. 是否包含4字节UTF-8编码的字符1
    2. 是否包含4字节UTF-8编码的字符2
    3. 去除4字节UTF-8编码的字符
    4. 将每个字符的16进制 分隔格式化(空格分隔)
    5. 字节数组转十六进制
    6. 字节转十六进制

参考：mysql/Java服务端对emoji的支持
mysql 数据库中varchar的长度与字节，字符串的关系
如何检测、替换4个字节的utf-8编码（此范围编码包含emoji表情）

问题描述

最近在做oracle库数据向MySQL数据库中迁移时，出现了这个问题，主要原因就是字符集不支持的异常。因为UTF-8编码有可能是一个、两个、三个、四个字节，其中Emoji表情是4个字节，而MySQL的utf8编码最多3个字节，所以导致了数据插不进去。需要修改为MySQL编码为utf8mb4即可。

错误详情

### Cause: java.sql.SQLException: Incorrect string value: '\xF2\xB5\xBC\xBA\xEF\xBF...' for column 'content' at row 40

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)ERROR [extractThread reportInfo ] -
    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)

    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:79)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
    at com.sun.proxy.$Proxy0.insertforMySQL(Unknown Source)
    at com.oracletomysql.service.imp.ReportinfoService.insertforMySQL(ReportinfoService.java:189)
    at com.oracletomysql.thread.ReportInfoThread.run(ReportInfoThread.java:63)
    at java.lang.Thread.run(Thread.java:744)

升级步骤

  1. utf8mb4的最低mysql版本支持版本为5.5.3+，若不是，请升级到较新版本。

  2. 修改database、table和column字符集。参考以下语句：

    ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name CHANGE column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  3. 修改mysql配置文件my.cnf（windows为my.ini）
    my.cnf一般在etc/mysql/my.cnf位置。找到后请在以下三部分里添加如下内容：

    [client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

4. 重启 MySQL Server、检查字符集
 1. 重启命令参考：`/etc/init.d/mysql restart`
 2. 输入命令：`mysql`，进入mysql命令行（如果提示没权限，可以尝试输入`mysql -uroot -p你的密码`）
 3. 在mysql命令行中输入：`SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';`

检查是否如下：
```sql
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client    | utf8mb4            |  客户端来源数据使用的字符集
| character_set_connection | utf8mb4            |  连接层字符集
| character_set_database  | utf8mb4            |  当前选中数据库的默认字符集
| character_set_filesystem | binary            |
| character_set_results    | utf8mb4            |  查询结果字符集
| character_set_server    | utf8mb4            |  默认的内部操作字符集
| character_set_system    | utf8              |  系统元数据(字段名等)字符集
| collation_connection    | utf8mb4_unicode_ci |
| collation_database      | utf8mb4_unicode_ci |
| collation_server        | utf8mb4_unicode_ci |
+--------------------------+--------------------+
rows in set (0.00 sec)

特别说明下：collation_connection/collation_database/collation_server如果是utf8mb4_general_ci，没有关系。但必须保证character_set_client/character_set_connection/character_set_database/character_set_results/character_set_server为utf8mb4。关于这些字符集配置是干什么用的，有什么区别，请参考：深入Mysql字符集设置

5.如果你用的是java服务器，升级或确保你的mysql connector版本高于5.1.13，否则仍然无法使用utf8mb4
这是mysql官方release note，大家可以查看说明，并下载最新的mysql connector for java的jar包。

6.检查你服务端的db配置文件：

jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE
jdbc.username=root
jdbc.password=password

特别说明其中的jdbc.url配置：如果你已经升级好了mysql-connector，其中的characterEncoding=utf8可以被自动被识别为utf8mb4（当然也兼容原来的utf8），而autoReconnect配置我强烈建议配上，我之前就是忽略了这个属性，导致因为缓存缘故，没有读取到DB最新配置，导致一直无法使用utf8mb4字符集，多么痛的领悟！！

1118 - Row size too large.

在上面的修改表的字符集时，出现了这个错误。

1118 - Row size too large.
The maximum row size for the used table type, not counting BLOBs, is 65535.
This includes storage overhead, check the manual.
You have to change some columns to TEXT or BLOBs.

MySQL要求一个行的定义长度不能超过65535

mysql 数据库中varchar的长度与字节，字符串的关系

CREATE TABLE `test` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `content` varchar(5) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
INSERT INTO `test`(`content`) VALUES ('123456');
INSERT INTO `test`(`content`) VALUES ('中国人民银行');
SELECT * FROM `test`;
返回:
id  content
1   12345
2   中国人民银

可见,varchar(5)能存储5个字符,不管是数字,字母,还是汉字.

CREATE TABLE `test2` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `content` varchar(21842) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

该表中varchar类型的字段能容纳的最大字符数21842是怎么得来的?
21842 = (65535-1-2-4)/3(如果是utf8mb4，则除以4)

  • MySQL要求一个行的定义长度不能超过65535(包括多个字段),所以有65535.
  • varchar的最大有效长度取决于最大行大小.
  • 减1的原因是实际行的存储从第2个字节开始.
  • 减2的原因是varchar头部的2个字节表示长度.
  • 减4的原因是字段id的int类型占用4个字节.
  • 除以3的原因是一个utf8字符占用3个字节.

如果你在test2表里把varchar(21842)改为varchar(21844),那么就会报上面的错误: Row size too large

MySQL中char,varchar与text类型的选用:

  • 知道固定长度的用char,比如MD5串固定是32位。
  • 经常变化的字段用varchar。
  • 超过255字符的只能用varchar或者text,不能用char。
  • 能用varchar的地方不用text。

如何检测、替换、去除4个字节的utf-8编码

由于上面的错误，需要修改表字段的类型，新项目基本已经完工，所以老大决定不支持4个字节UTF-8编码的字符。而且这些数据并不重要，这就要对旧数据中4个字节UTF-8编码的字符进行除去。

请先阅读UTF-8编码规则

public class UTF8Utils {

    public static Map<String, Integer> hexMap = new HashMap<String, Integer>();
    public static Map<String, Integer> byteMap = new HashMap<String, Integer>();

    static {
        hexMap.put("0", 2);
        hexMap.put("1", 2);
        hexMap.put("2", 2);
        hexMap.put("3", 2);
        hexMap.put("4", 2);
        hexMap.put("5", 2);
        hexMap.put("6", 2);
        hexMap.put("7", 2);
        hexMap.put("c", 4);
        hexMap.put("d", 4);
        hexMap.put("e", 6);
        hexMap.put("f", 8);

        byteMap.put("0", 1);
        byteMap.put("1", 1);
        byteMap.put("2", 1);
        byteMap.put("3", 1);
        byteMap.put("4", 1);
        byteMap.put("5", 1);
        byteMap.put("6", 1);
        byteMap.put("7", 1);
        byteMap.put("c", 2);
        byteMap.put("d", 2);
        byteMap.put("e", 3);
        byteMap.put("f", 4);
    }
}

是否包含4字节UTF-8编码的字符1

/**
 * 是否包含4字节UTF-8编码的字符（先转换16进制再判断）
 * @param s 字符串
 * @return 是否包含4字节UTF-8编码的字符
 */
public static boolean contains4BytesChar(String s) {
    if (s == null || s.trim().length() == 0) {
        return false;
    }

    String hex = UTF8Utils.bytesToHex(s.getBytes());
    System.out.println("full hex : " + hex);

    String firstChar = null;
    while (hex != null && hex.length() > 1) {
        firstChar = hex.substring(0, 1);
        System.out.println("firstChar : " + firstChar);

        if ("f".equals(firstChar)) {
            System.out.println("it is f start, it is 4 bytes, return.");
            return true;
        }

        if (hexMap.get(firstChar) == null) {
            System.out.println("it is f start, it is 4 bytes, return.");
            // todo, throw exception for this case
            return false;
        }

        hex = hex.substring(hexMap.get(firstChar), hex.length());
        System.out.println("remain hex : " + hex);
    }

    return false;
}

是否包含4字节UTF-8编码的字符2

/**
 * 是否包含4字节UTF-8编码的字符
 * @param s 字符串
 * @return 是否包含4字节UTF-8编码的字符
 */
public static boolean contains4BytesChar2(String s) {
    if (s == null || s.trim().length() == 0) {
        return false;
    }

    byte[] bytes = s.getBytes();

    if (bytes == null || bytes.length == 0) {
        return false;
    }

    int index = 0;
    byte b;
    String hex = null;
    String firstChar = null;
    int step;
    while (index <= bytes.length - 1) {
        System.out.println("while loop, index : " + index);
        b = bytes[index];

        hex = byteToHex(b);
        if (hex == null || hex.length() < 2) {
            System.out.println("fail to check whether contains 4 bytes char(1 byte hex char too short), default return false.");
            // todo, throw exception for this case
            return false;
        }

        firstChar = hex.substring(0, 1);

        if (firstChar.equals("f")) {
            return true;
        }

        if (byteMap.get(firstChar) == null) {
            System.out.println("fail to check whether contains 4 bytes char(no firstchar mapping), default return false.");
            // todo, throw exception for this case
            return false;
        }

        step = byteMap.get(firstChar);
        System.out.println("while loop, index : " + index + ", step : " + step);
        index = index + step;
    }

    return false;
}

去除4字节UTF-8编码的字符

/**
 * 去除4字节UTF-8编码的字符
 * @param s 字符串
 * @return 已去除4字节UTF-8编码的字符
 */
public static byte[] remove4BytesUTF8Char(String s) {
    byte[] bytes = s.getBytes();
    byte[] removedBytes = new byte[bytes.length];
    int index = 0;

    String hex = null;
    String firstChar = null;
    for (int i = 0; i < bytes.length; ) {
        hex = UTF8Utils.byteToHex(bytes[i]);

        if (hex == null || hex.length() < 2) {
            System.out.println("fail to check whether contains 4 bytes char(1 byte hex char too short), default return false.");
            // todo, throw exception for this case
            return null;
        }

        firstChar = hex.substring(0, 1);

        if (byteMap.get(firstChar) == null) {
            System.out.println("fail to check whether contains 4 bytes char(no firstchar mapping), default return false.");
            // todo, throw exception for this case
            return null;
        }

        if (firstChar.equals("f")) {
            for (int j = 0; j < byteMap.get(firstChar); j++) {
                i++;
            }
            continue;
        }

        for (int j = 0; j < byteMap.get(firstChar); j++) {
            removedBytes[index++] = bytes[i++];
        }
    }

    return Arrays.copyOfRange(removedBytes, 0, index);
}

将每个字符的16进制 分隔格式化(空格分隔)

/**
 * 将字符串的16进制转换为HEX，并按每个字符的16进制分隔格式化
 * @param s 字符串
 */
public static String splitForReading(String s) {
    if (s == null || s.trim().length() == 0) {
        return "";
    }

    String hex = UTF8Utils.bytesToHex(s.getBytes());
    System.out.println("full hex : " + hex);

    if (hex == null || hex.length() == 0) {
        System.out.println("fail to translate the bytes to hex.");
        // todo, throw exception for this case
        return "";
    }

    StringBuilder sb = new StringBuilder();
    int index = 0;

    String firstChar = null;
    String splittedString = null;
    while (index < hex.length()) {
        firstChar = hex.substring(index, index + 1);

        if (hexMap.get(firstChar) == null) {
            System.out.println("fail to check whether contains 4 bytes char(no firstchar mapping), default return false.");
            // todo, throw exception for this case
            return "";
        }

        splittedString = hex.substring(index, index + hexMap.get(firstChar));
        sb.append(splittedString).append(" ");
        index = index + hexMap.get(firstChar);
    }

    System.out.println("formated sb : " + sb);
    return sb.toString();
}

字节数组转十六进制

/**
 * 字节数组转十六进制
 * @param bytes 字节数组
 * @return 十六进制
 */
public static String bytesToHex(byte[] bytes) {
    if (bytes == null || bytes.length == 0) {
        return null;
    }

    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < bytes.length; i++) {
        int r = bytes[i] & 0xFF;

        String hexResult = Integer.toHexString(r);
        if (hexResult.length() < 2) {
            sb.append(0); // 前补0
        }
        sb.append(hexResult);
    }

    return sb.toString();
}

字节转十六进制

/**
 * 字节转十六进制
 * @param b 字节
 * @return 十六进制
 */
public static String byteToHex(byte b) {
    int r = b & 0xFF;//获得低8位
    String hexResult = Integer.toHexString(r);

    StringBuilder sb = new StringBuilder();
    if (hexResult.length() < 2) {
        sb.append(0); // 前补0
    }
    sb.append(hexResult);
    return sb.toString();
}

int r = b & 0xFF; 如有疑问，可以查看：java中byte转换int时为何与0xff进行与运算
主要原因是
1.byte的大小为8bits而int的大小为32bits
2.java的二进制采用的是补码形式
Java中的一个byte，其范围是-128~127的，而Integer.toHexString的参数本来是int，如果不进行&0xff，那么当一个byte会转换成int时，对于负数，会做位扩展，举例来说，一个byte的-1（即0xff），会被转换成int的-1（即0xffffffff），那么转化出的结果就不是我们想要的了。

而0xff默认是整形，所以，一个byte跟0xff相与会先将那个byte转化成整形运算，这样，结果中的高的24个比特就总会被清0，于是结果总是我们想要的。

转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 bin07280@qq.com

文章标题:java.sql.SQLException: Incorrect string value: '\xF0\x9F\x91\xBD\xF0\x9F…'

文章字数:2.7k

本文作者:Bin

发布时间:2016-05-26, 21:52:11

最后更新:2019-08-06, 00:51:50

原始链接:http://coolview.github.io/2016/05/26/Java/java-sql-SQLException-Incorrect-string-value-xF0-x9F-x91-xBD-xF0-x9F%E2%80%A6/

版权声明: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。

©2016-2019 CoolView

Built with Hexo and 3-hexo theme

目录