MySQL 5.7 版本的 UTF8 字符集调研

发布于 1970年 01月 01日 08:00

一、故事背景

记一次 sql_mode 非严格模式下的业务事故排查。当时数据库没有开启 sql_mode 为严格模式,并且数据表的编码是 utf8,表现为业务侧的 Insert SQL 语句执行成功,但是,
查询表记录的时候,发现字段的数据值缺失。示例:写入一条有特殊字符 𝑥  的记录,记录里面字段值在 𝑥 之后的字符都丢失了。

下面是,开启了严格模式:

问题原因定位到后,解决方案是,在不对数据库做任何配置调整的前提下,业务逻辑中增加对特殊字符的检测,过滤掉数据库不支持的特殊字符,从而杜绝写入数据表后出现数据缺失的事故。

那么,哪些字符是 MySQL 不支持的嘞?由此引出本文的探讨主题。

二、认识 MySQL UTF8 字符集

我们带着两个问题,去调研 MySQL 5.7 版本 UTF8 字符集。

2.1. MySQL 不支持的特殊字符有哪些?

PS: 这里贴的 MySQL 官方文档也是 5.7。

从文档提取下关键信息:

  • 在 MySQL 中 utf8 是 utf8mb3 的别名
  • utf8mb3 编码的每个字符最多三个字节

示例:特殊字符 𝑥 特殊字符:

可以观察到这个字符,需要使用四个字节编码,因此这个字符不能被数据库 utf8mb3 编码支持。

说点题外话,在 Java 中 String 是 UTF-16 格式的,当我们用鼠标复制 𝑥 字符到一个双引号中时,idea 编辑器,会自动转换为这样的格式:

那么,MySQL 的 utf8mb3 不支持哪些字符 ?

继续看 MySQL官方文档

可以看到,文档中已经给出了比较明确的描述:

  • 仅支持 BMP 字符
  • 一个字符的编码最多三个字节。

到这里,你可能又会问是什么 BMP 字符嘞,Wiki 百科看不懂啊!

在介绍这个问题之前,首先要了解一点基础知识 Code point

大家应该都认识这张表,ASCLL 包含 128 个 Code point 表示 128 个字符(也就是 0 ~ 127)。

在标准的 Unicode 中容纳了 1,114,112 code points,其中前 65,536 个 Code point (也就是 0 ~ 65535)称为 Basic Multilingual Plane(缩写:BMP

  • 查看一个字符的 Code point 可以使用 charbase.com,示例,查看大写字母 A :
  • 判断一个字符是否是 BMP
    首先计算出字符的 Code point,然后检查其范围,如果在 0 ~ 65535 内,就是 BMP 字符。

2.2. MySQL UTF8 和 标准 UTF-8 编码是一个概念吗?

通过上一个问题,我们了解到,MySQL 5.7 版本中 UTF8utf8mb3 的别名,utf8mb3 是使用 1 ~ 3 个字节对 Unicode 字符进行编码,仅支持 BMP 字符。

在 Wiki 百科里面对 UTF-8 的定义是:

简言之:使用 1 ~ 4 个字节对标准 Unicode 1,112,064 个有效的字符 Code point 进行编码。

因此,这两个 utf8 在不同的上下文背景下不是一个概念,很多开发人员包括我,经常在没有对事物做详细调研之前,凭借主观经验对事物妄下结论。

三、编程语言最佳实践

通过上面分析,我们知道问题的背景和原因。下面的给出最佳编程实践,选取前/后端使用的两门语言:

3.1. 在 Java 语言中检测字符串中的非 BMP 字符

public class Main {

    public static void main(String[] args) {
        String str = "𝑥方程";
        boolean contain = isContainsNonBmpUnicodeCharacter(str);
        if (contain) {
            System.out.println("The string contains non-BMP Unicode character.");
        }
    }

    private static boolean isContainsNonBmpUnicodeCharacter(String str) {
        return str.length() != str.codePointCount(0, str.length());
    }
}

3.2.在 Javascript 中检测字符串中非 BMP 字符

function main() {
    let str = "𝑥方程";
    let contains = isContainsNonBmpUnicodeCharacter(str);
    if (contains) {
        console.log("The string contains non-BMP Unicode character.");
    }
}

function isContainsNonBmpUnicodeCharacter(str) {
    return str.split(/[\uD800-\uDBFF][\uDC00-\uDFFF]/g).length != 1;
}

参考文献

  1. U+1D465: MATHEMATICAL ITALIC SMALL X - charbase.com

  2. How to convert a byte array to a hex string in Java? - stackoverflow.com

  3. Check if a String contains characters which aren't UTF-8 encoded in Java - stackoverflow.com

  4. Unicode Character Sets

  5. The utf8mb3 Character Set (3-Byte UTF-8 Unicode Encoding)

  6. Basic Multilingual Plane - wikipedia.org

  7. Code point - wikipedia.org

  8. How do I convert unicode codepoints to their character representation?

  9. UTF-8 - wikipedia.org

  10. JavaScript strings outside of the BMP - stackoverflow.com