mysqljson中文_MySQL对JSON类型UTF8编码导致中文乱码探讨

前言

继上文发表之后&＃xff0c;结合评论意见并亲自验证最终发现是编码的问题&＃xff0c;但是对于字符编码还是有点不解&＃xff0c;于是乎&＃xff0c;有了本文&＃xff0c;我们来学习字符编码&＃xff0c;在学习的过程中&＃xff0c;我发现对于MySQL中JSON类型的编码导致数据中文出现乱码还有可深挖之处&＃xff0c;接下来我们来分析一下&＃xff0c;若有错误之处&＃xff0c;还请批评指出。

字符编码

评论中指出任何不在基本多文本平面的Unicode字符&＃xff0c;都无法使用MySQL的utf8字符集存储&＃xff0c;包括Emoji 表情(Emoji 是一种特殊的Unicode 编码&＃xff0c;常见于IOS和Android 手机上)和很多不常用的汉字&＃xff0c;以及任何新增的 Unicode 字符等等(utf8的缺点)&＃xff0c;然而啥是多文本平面&＃xff0c;详情维基百科《https://en.wikipedia.org/wiki/Plane_(Unicode)》。首先我们了解下什么是Unicode&＃xff0c;Unicode是通用字符集&＃xff0c;它是一种标准&＃xff0c;该标准在一处定义了编写在计算机上使用的大多数活动语言所需的所有字符&＃xff0c;它的目标是成为并且在很大程度上已经是已编码的所有其他字符集的超集。在计算机或网络中的文本我们通过字符组成&＃xff0c;字符代表字母、标点符号或其他符号。不同的组织收集了不同的字符集并为其创建了编码-一个字符集可能仅覆盖基于拉丁语的西欧语言(不包括保加利亚或希腊等欧盟国家)&＃xff0c;另一个可能覆盖特定的远东语言(例如(例如日语)&＃xff0c;其他语言可能是以特殊方式设计的&＃xff0c;代表世界上某处其他语言的众多语言之一。但是我们并不能保证应用程序将支持所有编码&＃xff0c;也不能保证给定的编码将满足我们代表给定语言的所有需求&＃xff0c;另外&＃xff0c;通常不可能在同一网页或数据库中组合不同的编码&＃xff0c;因此使用“传统”编码方法来支持多语言页面通常非常困难&＃xff0c;所以Unicode协会提供了一个大的&＃xff0c;单字节字符集&＃xff0c;旨在包括所有需要的世界上任何书写系统&＃xff0c;包括古老的脚本(如楔形文字&＃xff0c;哥特式和埃及的象形文字)的字符&＃xff0c;所以统一字符编码&＃xff0c;将其作为Web和操作系统体系结构的基础&＃xff0c;并且得到所有主要Web浏览器和应用程序的支持。当前的Unicode字符分为17组编排&＃xff0c;每组被称之为一个平面(Plane)&＃xff0c;所以将字符划分为0-16号的平面&＃xff0c;而每平面拥有65536(即216)个代码点即范围区间在0x000到0xFFFF之间&＃xff0c;而0号平面就是基本多语言平面(BMP&＃xff1a;Basic Mutiingual Plane)。在基本多文本平面上针对每一种文字或者其补充或者其扩展都给出了一个编码范围&＃xff0c;比如拉丁文【0000-007F】&＃xff0c;拉丁文-补充【0080-00FF】等等。说了这么多&＃xff0c;我们只需要记住一点即可&＃xff1a;在Unicode字符集中前65536个代码点构成了基本多语言平面简称BMP&＃xff0c;BMP中包含了大多常用的字符&＃xff0c;另外Unicode字符集还包含了一百万个其他代码点的位置空间&＃xff0c;我们称之为补充字符。我们需要区分字符集、编码字符集和编码的概念&＃xff0c;字符集或字符串包含可能用于特定目的的字符集&＃xff0c;它是支持计算机上的西欧语言所需的字符集&＃xff0c;与计算机完全无关&＃xff0c;而编码字符集是一组用于该唯一的号码被分配给每个字符的字符&＃xff0c;有时候我们将编码字符集也可称作为代码页&＃xff0c;编码字符集的单位称为代码点&＃xff0c;代码点值表示字符在编码字符集中的位置。例如&＃xff0c;Unicode编码字符集中字母á的代码点为十进制225&＃xff0c;十六进制表示法为0xE1。而字符编码反映编码字符集被映射到用于在计算机操纵字节的方式。一个字符集可以有多种编码&＃xff0c;许多字符编码标准&＃xff0c;例如ISO 8859系列中定义的标准&＃xff0c;都为给定字符使用单个字节&＃xff0c;并且编码是对编码字符集中字符标量位置的直接映射。例如&＃xff0c;ISO 8859-1编码字符集中的字母A在第65个字符位置(从零开始)&＃xff0c;并且使用值为65的字节进行编码并以此在计算机中表示&＃xff0c;对于ISO 8859-1而言&＃xff0c;这将永远不会再改变&＃xff0c;但是&＃xff0c;对于Unicode&＃xff0c;事情并没有如此简单&＃xff0c;尽管Unicode编码字符集中字母á的代码点始终为225(十进制)&＃xff0c;但在UTF-8中&＃xff0c;它在计算机中由两个字节表示&＃xff0c;换句话说&＃xff0c;在此字符的编码字符集值和编码值之间不是简单的一对一映射&＃xff0c;另外&＃xff0c;在Unicode中&＃xff0c;针对同一字符可以有多种编码的方式。例如&＃xff0c;字母á可以用一种编码形式的两个字节表示&＃xff0c;而用另一种编码形式的四个字节表示。可以与Unicode一起使用的编码形式称为UTF-8&＃xff0c;UTF-16和UTF-32。

UTF-8使用1个字节表示ASCII集中的字符&＃xff0c;使用2个字节表示其他几个字母块中的字符&＃xff0c;使用3个字节表示BMP的其余部分&＃xff0c;补充字符使用4个字节。UTF-16对BMP中的任何字符使用2个字节&＃xff0c;对补充字符使用4个字节。UTF-32对所有字符使用4个字节。基本多语言平面对应代码点存储的是常用字符&＃xff0c;上述针对不同字符在其对应代码点&＃xff0c;然后计算出该字符的16进制的字符串&＃xff0c;举个栗子&＃xff0c;将【好】字进行UTF-8编码看看该字符的字节值和字节数&＃xff0c;如下&＃xff1a;

var bytes &＃61; Encoding.UTF8.GetBytes("好");var hexString &＃61; BitConverter.ToString(bytes);

到此我们大概了解完了字符编码&＃xff0c;接下来我们再次回到上一节的问题&＃xff0c;上一节将我姓名作为JSON存储到数据库中去&＃xff0c;但是最终获取数据时&＃xff0c;将出现乱码&＃xff0c;因为其表编码为utf8&＃xff0c;最终将表编码修改为utf8mb4才好使&＃xff0c;为啥utf8就不行呢&＃xff1f;通过上述对utf8的定义最多可以有4个字节&＃xff0c;支持补充字符&＃xff0c;所以MySQL根本就没有实现标准的utf8编码&＃xff0c;换句话说只是部分实现了utf8编码&＃xff0c;MySQL中的utf8又名为utf8mb3&＃xff0c;也就是一个字符最多可通过3个字节表示且包含BMP字符&＃xff0c;而不包含补充字符。所以无论是我的姓还是名虽然是3个字节&＃xff0c;但是并非常用BMP字符导致。但是针对列类型为JSON类型&＃xff0c;事实是对于获取中文真的会乱码吗&＃xff1f;上文我用到的MySQL版本为5.7&＃43;&＃xff0c;如下&＃xff1a;

接下来我们利用MySQL 8.0再来进行测试发现不会乱码&＃xff0c;创建类和表配置编码如下&＃xff1a;

public classt1

{public int id { get; set; }public string jdoc { get; set; }

}

static void Main(string[] args)

{

SetDialect(Dialect.MySQL);var con &＃61; new MySqlConnection(&＃64;"Server&＃61;localhost;Database&＃61;user;Uid&＃61;root;Pwd&＃61;root;");var id &＃61; con.Insert(new t1() { jdoc &＃61; JsonConvert.SerializeObject(new { Data &＃61; "汪鹏"}) });var result &＃61; con.QueryFirstOrDefault("select * from t1 where id &＃61; &＃64;id", new{ id });

Console.ReadKey();

}

随着移动端的兴起&＃xff0c;有了表情的出现&＃xff0c;所以从MySQL 5.5.3开始&＃xff0c;引入utf8mb4字符集每个字符最多可使用4个字节&＃xff0c;支持补充字符&＃xff0c;对于BMP字符&＃xff0c;utf8 [utf8mb3]和utf8mb4具有相同的存储特征&＃xff1a;相同的代码值&＃xff0c;相同的编码&＃xff0c;相同的长度&＃xff0c;对于补充字符&＃xff0c;utf8 [utf8mb3]根本无法存储该字符&＃xff0c;而utf8mb4需要4个字节来存储它&＃xff0c;由于utf8 [utf8mb3]根本无法存储字符&＃xff0c;因此在utf8 [utf8mb3]列中没有任何补充字符。接下来我们在针对JSON类型配置为utf8编码的情况下&＃xff0c;我们来插入表情&＃xff0c;此时会发现也是可以的。我们是可以获取对应字符的字节数&＃xff0c;比如如下哭笑不得的表情为4个字节&＃xff1a;

var emotion &＃61; Encoding.UTF8.GetByteCount("&＃x1f602;");

其实针对JSON类型获取数据乱码的情况早就有人提出过相关bug&＃xff0c;详见地址《https://bugs.mysql.com/bug.php?id&＃61;81677》&＃xff0c;不过官方一直没有任何回复&＃xff0c;至少通过上述测试出来的结果对于utf8存储表情也可以&＃xff0c;到底具体情况咋回事&＃xff0c;我们还是看看8.0版本以对utf8编码描述为准&＃xff0c;详情请见《https://dev.mysql.com/doc/refman/8.0/en/charset-unicode.html》&＃xff0c;对于utf8编码的描述依然还是最多可存储3个字节&＃xff0c;如下&＃xff1a;

别忘记&＃xff0c;还有注意&＃xff1a;utf8[utf8mb3]字符集已被弃用&＃xff0c;并会在将来的MySQL版本中移除&＃xff0c;请改用utf8mb4&＃xff0c;尽管utf8当前是utf8mb3的别名&＃xff0c;但在某些时候utf8将成为对utf8mb4的引用&＃xff0c;为避免对utf8的含义含糊不清&＃xff0c;请考虑为字符集引用显式指定utf8mb4而不是utf8。所以到此我们已明了&＃xff0c;针对8.0版本中的utf8编码虽说最多可支持3个字节&＃xff0c;但是&＃xff0c;会将utf8成为utf8mb4的引用&＃xff0c;如此就不难理解为何上述将表配置为utf8编码时&＃xff0c;对于JSON类型的不在常用BMP字符进行数据存储和表情皆没问题。

总结

通过此文对utf8编码的JSON类型的数据出现中文乱码的问题的学习才算告一段落&＃xff0c; 原来版本问题使得utf8存在对utf8mb4编码的引用&＃xff0c;知其然&＃xff0c;知其所以然&＃xff0c;嗯&＃xff0c;大概是这么个道理。发表博客的好处就在这里&＃xff0c;没有批评和指正&＃xff0c;哪来的更进一步呢。