理解MySQL中的字符集和排序规则
如果你曾经使用过 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确实提供了一些查询来帮助您决定使用什么,但为了使字符集和排序规则有效,您还应该理解何时使用某个排序规则以及原因。