mysql中的字符集

一、简介

MySQL在5.5.3之后增加了这个utf8mb4的编码，mb4就是most bytes 4的意思，专门用来兼容四字节的unicode。好在utf8mb4是utf8的超集，除了将编码改为utf8mb4外不需要做其他转换。当然，为了节省空间，一般情况下使用utf8也就够了。

二、内容描述

那上面说了既然utf8能够存下大部分中文汉字,那为什么还要使用utf8mb4呢? 原来mysql支持的 utf8 编码最大字符长度为 3 字节，如果遇到 4 字节的宽字符就会插入异常了。三个字节的 UTF-8 最大能编码的 Unicode 字符是 0xffff，也就是 Unicode 中的基本多文种平面（BMP）。也就是说，任何不在基本多文本平面的 Unicode字符，都无法使用 Mysql 的 utf8 字符集存储。包括 Emoji 表情（Emoji 是一种特殊的 Unicode 编码，常见于 ios 和 android 手机上），和很多不常用的汉字，以及任何新增的 Unicode 字符等等。

三、问题根源

最初的 UTF-8 格式使用一至六个字节，最大能编码 31 位字符。最新的 UTF-8 规范只使用一到四个字节，最大能编码21位，正好能够表示所有的 17个 Unicode 平面。

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8字符，也就是 Unicode 中的基本多文本平面。

Mysql 中的 utf8 为什么只支持持最长三个字节的 UTF-8字符呢？我想了一下，可能是因为 Mysql 刚开始开发那会，Unicode 还没有辅助平面这一说呢。那时候，Unicode 委员会还做着 “65535 个字符足够全世界用了”的美梦。Mysql 中的字符串长度算的是字符数而非字节数，对于 CHAR 数据类型来说，需要为字符串保留足够的长。当使用 utf8 字符集时，需要保留的长度就是 utf8 最长字符长度乘以字符串长度，所以这里理所当然的限制了 utf8 最大长度为 3，比如 CHAR(100) Mysql 会保留 300字节长度。至于后续的版本为什么不对 4 字节长度的 UTF-8 字符提供支持，我想一个是为了向后兼容性的考虑，还有就是基本多文种平面之外的字符确实很少用到。

要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持（查看版本： select version();）。我觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

 

最近我遇到了一个bug，我试着通过Rails在以“utf8”编码的MariaDB中保存一个UTF-8字符串，然后出现了一个离奇的错误：

Incorrectstringvalue:‘\xF0\x9F\x98\x83<…’forcolumn‘summary’at row1我用的是UTF-8编码的客户端，服务器也是UTF-8编码的，数据库也是，就连要保存的这个字符串“ <…”也是合法的UTF-8。

问题的症结在于，MySQL的“utf8”实际上不是真正的UTF-8。

“utf8”只支持每个字符最多三个字节，而真正的UTF-8是每个字符最多四个字节。

MySQL一直没有修复这个bug，他们在2010年发布了一个叫作“utf8mb4”的字符集，绕过了这个问题。

当然，他们并没有对新的字符集广而告之（可能是因为这个bug让他们觉得很尴尬），以致于现在网络上仍然在建议开发者使用“utf8”，但这些建议都是错误的。

简单概括如下：

1.MySQL的“utf8mb4”是真正的“UTF-8”。2.MySQL的“utf8”是一种“专属的编码”，它能够编码的Unicode字符并不多。我要在这里澄清一下：所有在使用“utf8”的MySQL和MariaDB用户都应该改用“utf8mb4”，尽量不要再使用“utf8”。

那么什么是编码？什么是UTF-8？我们都知道，计算机使用0和1来存储文本。比如字符“C”被存成“01000011”，那么计算机在显示这个字符时需要经过两个步骤：

1.计算机读取“01000011”，得到数字67，因为67被编码成“01000011”。
2.计算机在Unicode字符集中查找67，找到了“C”。
同样的：
1.我的电脑将“C”映射成Unicode字符集中的67。
2.我的电脑将67编码成“01000011”，并发送给Web服务器。几乎所有的网络应用都使用了Unicode字符集，因为没有理由使用其他字符集。

Unicode字符集包含了上百万个字符。最简单的编码是UTF-32，每个字符使用32位。这样做最简单，因为一直以来，计算机将32位视为数字，而计算机最在行的就是处理数字。但问题是，这样太浪费空间了。

UTF-8可以节省空间，在UTF-8中，字符“C”只需要8位，一些不常用的字符，比如“”需要32位。其他的字符可能使用16位或24位。一篇类似本文这样的文章，如果使用UTF-8编码，占用的空间只有UTF-32的四分之一左右。

MySQL的“utf8”字符集与其他程序不兼容，它所谓的“”，可能真的是一坨……

总结如果你在使用MySQL或MariaDB，不要用“utf8”编码，改用“utf8mb4”。这里提供了一个指南用于将现有数据库的字符编码从“utf8”转成“utf8mb4”

应用与拓展
MySQL简史为什么MySQL开发者会让“utf8”失效？我们或许可以从提交日志中寻找答案。

MySQL从4.1版本开始支持UTF-8，也就是2003年，而今天使用的UTF-8标准（RFC 3629）是随后才出现的。

旧版的UTF-8标准（RFC 2279）最多支持每个字符6个字节。2002年3月28日，MySQL开发者在第一个MySQL 4.1预览版中使用了RFC 2279。

同年9月，他们对MySQL源代码进行了一次调整：“UTF8现在最多只支持3个字节的序列”。

是谁提交了这些代码？他为什么要这样做？这个问题不得而知。在迁移到Git后（MySQL最开始使用的是BitKeeper），MySQL代码库中的很多提交者的名字都丢失了。2003年9月的邮件列表中也找不到可以解释这一变更的线索。

不过我可以试着猜测一下。

2002年，MySQL做出了一个决定：如果用户可以保证数据表的每一行都使用相同的字节数，那么MySQL就可以在性能方面来一个大提升。为此，用户需要将文本列定义为“CHAR”，每个“CHAR”列总是拥有相同数量的字符。如果插入的字符少于定义的数量，MySQL就会在后面填充空格，如果插入的字符超过了定义的数量，后面超出部分会被截断。

MySQL开发者在最开始尝试UTF-8时使用了每个字符6个字节，CHAR(1)使用6个字节，CHAR(2)使用12个字节，并以此类推。

应该说，他们最初的行为才是正确的，可惜这一版本一直没有发布。但是文档上却这么写了，而且广为流传，所有了解UTF-8的人都认同文档里写的东西。

不过很显然，MySQL开发者或厂商担心会有用户做这两件事：

1.使用CHAR定义列（在现在看来，CHAR已经是老古董了，但在那时，在MySQL中使用CHAR会更快，不过从2005年以后就不是这样子了）。2.将CHAR列的编码设置为“utf8”。我的猜测是MySQL开发者本来想帮助那些希望在空间和速度上双赢的用户，但他们搞砸了“utf8”编码。

所以结果就是没有赢家。那些希望在空间和速度上双赢的用户，当他们在使用“utf8”的CHAR列时，实际上使用的空间比预期的更大，速度也比预期的慢。而想要正确性的用户，当他们使用“utf8”编码时，却无法保存像“”这样的字符。

在这个不合法的字符集发布了之后，MySQL就无法修复它，因为这样需要要求所有用户重新构建他们的数据库。最终，MySQL在2010年重新发布了“utf8mb4”来支持真正的UTF-8。

为什么这件事情会让人如此抓狂因为这个问题，我整整抓狂了一个礼拜。我被“utf8”愚弄了，花了很多时间才找到这个bug。但我一定不是唯一的一个，网络上几乎所有的文章都把“utf8”当成是真正的UTF-8。

“utf8”只能算是个专有的字符集，它给我们带来了新问题，却一直没有得到解决。

原文链接：https://blog.csdn.net/weixin_44269886/article/details/89364733

 

 

我在几年前设计应用程序数据库时犯了一个错误，我的表和表字段的排序规则设置混合。其中一些是utf8_general_ci，其中一些是utf8_unicode_ci。

当连接具有不同排序规则的表时会导致问题。现在，我打算更改排序规则设置，并使它们相同：utf8_unicode_ci。我将在所有的表上运行这两个SQL查询。

ALTER TABLE`table1` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE`table1` CHANGE`Action`` Action VARCHAR（250）CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;

我的问题是，运行这两个SQL查询会破坏任何字段值，特别是包含重音字符？还是可以安全地运行这两个查询？

 

 

对于任何Unicode字符集，使用_general_ci排序规则执行的操作比排序规则更快_unicode_ci。例如，utf8_general_ci排序比较比对比更快，但稍微不正确utf8_unicode_ci。其原因是utf8_unicode_ci支持映射，如扩展; 也就是说，当一个字符比较与其他字符的组合相等时。例如，在德语和其他一些语言中，“ ß”等于“ ss”。utf8_unicode_ci还支持缩略和可忽略的字符。utf8_general_ci是不支持扩展，缩小或可忽略字符的传统归类。它只能进行字符之间的一对一比较。

utf8_general_ci是非常简单的 - 而且在Unicode上，非常破碎 - 整理，在通用的Unicode文本上给出不正确的结果。它所做的是：

• 转换为Unicode规范化形式D以进行规范分解
• 删除任何组合字符
• 转换为大写

这在Unicode上无法正常工作，因为它不理解Unicode套管。单独使用Unicode框架要比使用ASCII的方法可以处理更复杂。例如：

• “ẞ”的小写字母是“ß”，但“ß”的大写字母是“SS”。
• 有两个小写的希腊sigma，但只有一个大写; 考虑“Σίσυφος”。
• 像“ø”这样的字母不会分解为“o”加上变音符号，这意味着它不会正确排序。

还有很多其他的细节。

1. utf8_unicode_ci使用标准的Unicode排序算法，支持所谓的扩展和连字，例如：德语字母（U + 00DF LETTER SHARP S）排序在“ss”字母附近（U + 0152 LATIN CAPITAL LIGATURE OE）排序在“OE ”。

utf8_general_ci 不支持扩展/连字，它将所有这些字母排序为单个字符，有时按错误的顺序排列。

1. utf8_unicode_ci是一般所有脚本更准确。例如，在西里尔文块： utf8_unicode_ci对所有这些语言都适用：俄语，保加利亚语，白俄罗斯语，马其顿语，塞尔维亚语和乌克兰语。虽然utf8_general_ci只适用于俄罗斯和保加利亚的西里尔语子集。白俄罗斯语，马其顿语，塞尔维亚语和乌克兰语中使用的额外字母排序不正确。

 

UTF 8_General_ci与UTF 8_Unicode_ci有什么区别？

这两个排序规则都用于UTF-8字符编码.不同之处在于如何对文本进行排序和比较。

注意：既然MySQL 5.5.3，您应该使用utf8mb4而不是utf8..它们都是指Utf-8编码，但是旧的编码。utf8有一个特定于MySQL的限制，防止使用编号高于0xFFFD的字符。

• 准确度

  utf8mb4_unicode_ci是基于Unicode标准进行排序和比较，它在非常广泛的语言中进行精确排序。

  utf8mb4_general_ci未能实现所有Unicode排序规则，这将导致在某些情况下(例如在使用特定语言或字符时)不受欢迎的排序。

• 性能

  utf8mb4_general_ci比较和排序更快，因为它需要一系列与性能相关的快捷方式。

  在现代服务器上，这种性能提升几乎可以忽略不计。它是在服务器只占当今计算机CPU性能的很小一部分的时代设计的。

  utf8mb4_unicode_ci，它使用Unicode规则进行排序和比较，在使用范围广泛的特殊字符时，使用了相当复杂的算法来正确排序。这些规则需要考虑到特定于语言的惯例；并不是每个人都按照我们所说的“字母顺序”对字符进行排序。

就拉丁语(即“欧洲”)语言而言，Unicode排序和简化排序没有多大区别。utf8mb4_general_ci在MySQL中进行排序，但是仍然有一些不同之处：

• 例如，Unicode排序规则按照使用这些字符的人通常希望的那样，对“Œ”进行排序，如“ss”和“OE”。utf8mb4_general_ci将它们排序为单个字符(可能分别类似于“s”和“e”)。

• 有些Unicode字符被定义为可忽略的字符，这意味着它们不应该计算到排序顺序，并且比较应该移到下一个字符。utf8mb4_unicode_ci处理好这些。

在非拉丁语言，如亚洲语言或不同字母的语言中，可能有很多。更多Unicode排序与简化排序的区别utf8mb4_general_ci分拣。适合性utf8mb4_general_ci将在很大程度上取决于所使用的语言。对于某些语言来说，这是相当不够的。

你应该用什么？

几乎可以肯定的是，没有理由使用utf8mb4_general_ci现在，我们已经留下了CPU速度足够低的问题，因此性能差异将是非常重要的。您的数据库几乎肯定会受到其他瓶颈的限制。

在非常特殊的情况下，性能上的差异是可以衡量的，如果是你，你可能已经知道了。如果您正在经历缓慢的排序，那么在几乎所有情况下，索引/查询计划都会出现问题。更改您的排序规则函数不应该在要排除故障的事情列表中占据很高的位置。

过去，有些人建议使用utf8mb4_general_ci除非准确的排序将是足够重要，以证明性能成本。今天，性能成本几乎已经消失，开发人员正在更加认真地对待国际化。

我还要补充的另一件事是，即使您知道您的应用程序只支持英语，它可能仍然需要处理人名，而人名通常包含在其他语言中使用的字符，在这些字符中，正确排序同样重要。一切都使用Unicode规则有助于为非常聪明的Unicode用户提供一种安心，即他们为使排序工作正常而付出了很大的努力。