了解MySQL中的字符集和排序规则

Lukas Vileikis 【hudson 译】

2021年1月9日

如果你曾经使用过 MySQL ，您不可避免地会遇到字符集和排序规则。在这篇博客中，我们将尝试让您更深入地了解这两个是什么，以及您应该如何使用它们。

什么是字符集和排序规则？

简单地说，MySQL 中的字符集是一组符号和编码 - 排序规则是一组用于比较字符集中字符的规则。换句话说，字符集是字符串中合法的字符集，而排序规则是用于比较特定字符集中字符的一组规则。每个字符集都有一个默认的排序规则，字符集也可以有几个排序规则。MySQL 服务器、每个数据库和每个表都有默认字符集和排序规则。

MySQL中的字符集

通常，MySQL 中的字符集这样工作：

创建数据库时，字符集从服务器范围的character_set_server变量派生。
创建表时，将从数据库中导出字符集。
创建列时，将从表中派生字符集。

就字符集而言，有几个变量需要注意：

Character_set_client 定义客户端发送语句的字符集。
Character_set_connection 定义了服务器从客户端接收语句后将语句转换后的字符集。
Character_set_results 定义服务器将查询结果返回给客户端的字符集。

这三个设置可以通过使用SET NAMES或SET CHARACTER SET语句更改，甚至可以在 MySQL 配置文件中更改。

在处理字符集时，有时您可能还会遇到错误 #1267：

ERROR 1267 (HY000): Illegal mix of collations.

上述错误通常是由于比较了具有不兼容排序规则的两个字符串，或者尝试将具有不同排序规则的数据选择到组合列中而导致的。显示错误是因为当 MySQL 比较具有不同字符集的两个值时，必须将它们转换为相同的字符集进行比较，但字符集不兼容。要解决此问题，请确保每个表及其列的排序规则相同。

MySQL中的排序规则

如上所述，排序规则与字符集密切相关，因为排序规则是一组定义如何比较和排序字符串的规则。每个字符集至少有一个排序规则，有些也有更多。虽然这篇博文不会深入MySQL中与排序相关的所有事情的细节，但有一些事情你应该知道：

如果您使用的是MySQL 5.7，那么默认的MySQL排序规则通常是latin1_swedish_ci，因为MySQL使用latin1作为默认字符集。如果您使用的是MySQL 8.0，默认字符集是utf8mb4。
如果选择使用UTF-8作为排序规则，请始终使用utf8mb4（特别是utf8mb2_unicode_ci）。您不应该使用UTF-8，因为MySQL的UTF-8与正确的UTF-8编码不同。之所以如此，是因为它不提供完整的unicode支持，这可能会导致数据丢失或安全问题。请记住，utf8mb4_general_ci 是一组简化的排序规则，它采用了旨在提高速度的快捷方式，而utf8mb2_unicode_ci可以在多种语言中准确排序。通常，utf8mb4是“最安全”的字符集，因为它还支持4字节unicode，而utf8最多只支持3字节。

选择好的字符集和排序规则

要为MySQL数据选择一个好的排序规则和字符集，请记住保持简单。混合使用不同的字符集和（或）排序规则可能会非常混乱，因为它们非常容易混淆（例如，在某些字符出现之前，一切都可能工作很好，等等），因此最好预先评估您的需求，并选择最佳的排序规则和字符集。例如，MySQL还有一些有用的查询可以帮助您做到这一点，

SELECT * FROM information_schema.CHARACTER_SETS ORDER BY CHARACTER_SET_NAME;

上述查询将返回字符集和可用排序规则的列表以及它们的描述，如果您正在规划数据库设计，这将非常有用。请记住，某些字符集可能需要更多的CPU操作，也可能占用更多的存储空间。使用错误的字符集甚至会导致索引失败，例如，MySQL必须转换字符集，以便在它们不相同时进行比较：转换可能会导致无法使用索引。

此外，请记住，有些人建议“只在全球范围内使用UTF-8”–这可能不是一个好主意，因为许多应用程序根本不需要UTF-8，而且，根据您的数据，UTF-8可能会造成比实际情况更大的麻烦（例如，它可能会在磁盘上使用更多的存储空间），所以请明智地选择。

小结

字符集和排序规则可能是你的朋友，也可能是你做的噩梦之一 — 这取决于你如何使用它们。一般来说，请记住，“好”的字符集和排序取决于数据库保存的数据 – MySQL确实提供了一些查询来帮助您决定使用什么，但为了使字符集和排序规则有效，您还应该理解何时使用某个排序规则以及原因。