相关推荐recommended
SQL执行报错Incorrect string value: ‘xF0x9Fx98x81xF0x9F...‘ for column ‘XXX‘ at row 1...
作者:mmseoamin日期:2023-12-11

目录

  • 一、报错
  • 二、原因
  • 三、示例
  • 四、解决办法
  • 总结

    一、报错

    分享一个数据库执行插入和修改语句可能会出现的bug

    Incorrect string value: ‘\xF0\x9F\x98\x81\xF0\x9F…’ for column ‘name’ at row 1…

    SQL执行报错Incorrect string value: ‘xF0x9Fx98x81xF0x9F...‘ for column ‘XXX‘ at row 1...,在这里插入图片描述,第1张

    再来看下实际项目中服务器报错打印的日志

    SQL执行报错Incorrect string value: ‘xF0x9Fx98x81xF0x9F...‘ for column ‘XXX‘ at row 1...,在这里插入图片描述,第2张

    二、原因

    数据库某字段设置的是utf8字符集,在执行插入或修改语句时该字段传入的值是非utf8格式(表情或特殊字符)的内容。其实能出现这种问题的场景一般多是手机端。

    SQL执行报错Incorrect string value: ‘xF0x9Fx98x81xF0x9F...‘ for column ‘XXX‘ at row 1...,在这里插入图片描述,第3张

    我们都知道MySQL的utf8只支持最大3字节每字符,已经包含了控制符、拉丁文,中、日、韩等绝大多数国际字符,但还不是所有,例如手机端常用的表情字符 emoji表情和一些特殊字符,这些需要四个字节才能编码出来的,就需要对utf8进行扩展,于是就有了utf8mb4。

    MySQL想要插入 4 字节长度的 UTF-8 字符,就需要使用 utf8mb4 字符集(mb4就是most bytes 4的意思,专门用来兼容四字节的unicode),但只有 5.5.3 版本以后的才支持。

    三、示例

    我有这么一张表

    CREATE TABLE `user` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名',
      `password` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
      `role` varchar(15) DEFAULT NULL COMMENT '角色',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET = utf8;
    

    我的插入SQL这样写(name字段传入表情)

    INSERT INTO `user` (name, password, role) 
    VALUE ('😁😂😃👻', 'mypassword', 'admin');
    

    那么执行的结果就会报错

    INSERT INTO `user` (id, name, password, role) VALUE (NULL, '😁😂😃👻', 'mypassword', 'admin')
    > 1366 - Incorrect string value: '\xF0\x9F\x98\x81\xF0\x9F...' for column 'name' at row 1
    > 时间: 0.001s
    

    报错信息很明显地告诉我们了,是name字段传入了不恰当的字符类型

    四、解决办法

    1. 将MySQL版本升级到5.5.3及以上。

    -- 执行sql查询当前数据库的版本
    select version();
    

    关于MySQL的卸载与安装可以参我的考往期博文 —> 点击跳转 <—

    2. 将字段的utf8字符集升级到utf8mb4

    SQL执行报错Incorrect string value: ‘xF0x9Fx98x81xF0x9F...‘ for column ‘XXX‘ at row 1...,在这里插入图片描述,第4张

    数据库的字符集编码是有优先级的:

    优先级顺序为:数据库字符集 < 表字符集 < 字段字符集

    当字符集不一致时,以小范围的为准,即字段设置的字符集优先级最高!

    下面是通过sql修改字符编码:

    --修改数据库字符集
    ALTER DATABASE `数据库名` CHARACTER SET = utf8mb4;
    --修改表字符集
    ALTER TABLE `表名` convert to character set utf8mb4;
    --修改字段字符集
    ALTER TABLE `数据库名`.`表名` MODIFY COLUMN `字段名` VARCHAR(20) CHARACTER SET 
    utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '字段名称注释';
    

    想要更多了解MySQL常用排序规则utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin、utf8mb4_0900_ai_ci等,以及存储字符集 utf8 和 utf8mb4,可以看下这篇博文,总结的比较详尽 —> 点击跳转 <—

    总结

     utf8mb4是utf8的超集,理论上原来使用utf8,然后将字符集修改为utf8mb4,也基本不会对已有的utf8编码数据的读取产生什么影响,不过为了utf8确实是更节省空间,我们一般情况下使用utf8也就够了。

     像一些不会对用户限制输入内容的varchar字段,还是尽量使用utf8mb4编码,正如我在文中说到的,这种使用utf8mb4的情况多是在手机端,因为我们限制不了用户往输入框里输入了什么字符,那么我们就必须向上或向下兼容了,通过牺牲部分空间和性能来换取平台的可靠性。

     一般来说,只要能找到满足当前需求的最好解决方案,那么技术选型就是正确的,如何取舍就看个人了。