MYSQL数据库与Emoji表情的故事

问题背景

手机上众多输入法和键盘支持输入 emoji 表情，给早期设计的程序造成了越来越多的干扰。

  1. 移动端购物的流行，2018 年 “双十一”全网移动端交易达到 93.6%
  2. 微信年度报告里 80 后爱用的“龇牙”表情，早在 2017 年 QQ 发布的统计数据就超过 303 亿。

最近我们团队就遇到了一个线上问题，就是由于用户下单备注使用 emoji 表情引起的问题。

通过解决这个问题，主要了解下面 3 个方面内容：

  1. Mysql 编码概念
  2. Mysql“乱码”是怎么来的：字符编码转换
  3. Emoji 在 Mysql 使用问题

问题分析

昨天突然接到业务反馈，监控告警了，有几个订单卡单了，一直在某个系统里面没有推到下游，最终导致错过了配送时间。

查询日志中心，ES 搜索到了某系统 Job 拉数据保存数据库的异常信息。

展开详细日志发现了关键日志：

ERROR 1366: Incorrect string value: '\xF0\x9F\x99\x8F...' for column 'Remark' at row 2。

试图将一个 4 字节的字符写入到一个 3 字节的列 Remark, 当然是报错了。

然后我们看了一下用户的备注 Remark 信息，**上午家里没人，请下午送，谢谢😊

这个谢谢真心不容易啊，老铁。emoji 表情，下单完全没有问题。DBA 搜索了一下历史订单数据库，是支持的。找了一下报错的 Job 服务，发现操作的库是最近从 SQL Server 转到 mysql 的，拉取打印发货单信息的时候出错了。

问题原因

相关字符和编码知识点

Unicode 字符集有好几种编码方式，比如常见的 utf-8，utf-16,utf-32 等。
utf-8 是它是一种变长的编码方式，采用 1-4 个字节表示字符，utf-16 采用固定的 2 个字节，utf-32 则采用 4 个字节存储。

C#里面 Encoding.Unicode 实现为 2 个字节的 Little-Endian UTF-16，如果是 4 个字节使用 UTF-32。

Unicode 在第二个面板（Plane 1，SMP）规定了 emoji 的码点和含义。每一个表情使用 4 个字节。 所谓 Emoji 就是一种在 Unicode 位于、u1F601-\u1F64F 区段的字符。

SQL Server 的数据库表，nvarchar 类型字符串默认就是可变长度 Unicode 字符串。

MySQL 版本 5.5.3 以下版本 utf8 字符集 utf8 最多表示 3 个字节，5.5.3 以上版本支持 4 个字节的 utf8 编码字符集 utf8mb4,MySQL8.0 版默认字符集为 utf8mb4。

MYSQL 存储 utf8 字符默认为无 BOM 的 Big-Endian 方式编码。

MySQL 中的字符集转换过程

字符集转换

了解字符编码的转换规则，我们就可以理解为何会产生乱码以及字符插入失败等问题。

https://www.ibm.com/developerworks/cn/java/analysis-and-summary-of-common-random-code-problems/index.html

解决方案

1. 了解当前字符编码设置

我们先查看一下系统配置的相关结果：

mysql> show variables like '%char%';
+--------------------------+----------------------------------------------+
| Variable_name            | Value                                        |
+--------------------------+----------------------------------------------+
| character_set_client     | utf8                                         |
| character_set_connection | utf8                                         |
| character_set_database   | utf8mb4                                      |
| character_set_filesystem | binary                                       |
| character_set_results    | utf8                                         |
| character_set_server     | utf8mb4                                      |
| character_set_system     | utf8                                         |
| character_sets_dir       | D:\mysql\mysql-8.0.11-winx64\share\charsets\ |
+--------------------------+-------------------------------------   ---------+
8 rows in set

2. 设计数据库时明确指定字符集

包括库，表，字段三个层级都要明确，否则按照从低到高原则使用 my.ini 默认配置。具体的创建语句不细说，请自行搜索。

3. 统一字符集

目的是减少不必要的转换，除非有特殊要求。特别注意保证转换的时候不会由于字符集不兼容而导致不可逆的转换。例如部分 Unicode 字符在 Utf8 里面是没有的，部分 gbk 编码也是。
具体来说就是：

客户端， character-set-client，table charset 三个字符集完全一致就可以保证不会产生乱码。

SQL 语句中的裸字符串会受到 character_set_connection 字符集或 introducer 设置的影响，对于比较之类的操作可能产生完全不同的结果，需要小心！解决方法是在发送查询前执行一下下面这句： SET NAMES '***'

相当于下面的三句指令：
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;

4. 修复编码损坏数据不可强行转换字符集

通过 ALTER TABLE ... CHARSET=xxx 或 ALTER TABLE … CONVERT TO CHARACTER SET … 有可能把数据完全破坏，可行的做法可以参考卢钧轶的博客（参见引文5）。

写在后面

关于 emoji 表情，随着手机输入的支持和年轻人的热爱，想要不出问题必须思考好以下几个问题：

  1. 功能设计明确该输入框是否需要支持 emoji 表情
  2. 上下游链路约定好如何存储和展示，字符串截取
  3. 运维和升级数据库字符集相关问题需要谨慎，防止数据丢失
  4. 主从同步，注意低版本不支持的字符集 utf8mb4 的情况会导致同步失败

资料来源

  1. 前瞻产业研究院报告解读：移动支付便利化 十张图了解 2018 年全球购物狂欢节高速增长背后的女人
  2. 阮一峰入门文章：Emoji 简介
  3. 36 氪一篇有趣的文章：一波又一波 Emoji 表情推出，你的表情符号键盘还好吗？
  4. 有意思的国外相关数据：你正在参与的语言革命｜Emoji：再建巴别塔
  5. 卢钧轶：10分钟学会理解和解决MySQL乱码问题：10分钟学会理解和解决MySQL乱码问题
  6. MYSQL Help About Unicode:charset-unicode
  7. 常见乱码问题分析和总结：乱码分析

本文同步发布在公众号：MYSQL乱码问题整理

posted @ 2019-03-12 22:27  云是风的梦  阅读(482)  评论(0)  编辑  收藏  举报