当前位置：首页 > news >正文

【MySQL系列】深入理解MySQL中的存储、排序字符集

news 文章来源：https://blog.csdn.net/duleilewuhen/article/details/143869836 2024/11/29 23:39:34

前言

在创建数据库时，我们经常会需要填写数据库的所用字符集、排序规则，字符集和排序规则是两个非常重要的概念，它们决定了数据库如何存储和比较字符串数据。在 MySQL 中，常用的存储字符集有 utf8、utf8mb4，而排序字符集有 utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_bin、utf8mb4_0900_ai_ci，如下图所示。今天我们来探讨一下MySQL中几种常用的字符集和排序规则之间的区别和适用场景。

一、存储字符集

utf8mb4 和 utf8 是 MySQL 中两种常用的字符集，它们都可以用来存储 Unicode 字符，但是有一些区别和联系。接下来，我们将从以下几个方面对比 utf8mb4 和 utf8。

1.1 字符集简介

utf8 是 Mysql 中最早支持的 Unicode 字符集，是一种可变长度的字符编码方式，可以表示世界上几乎所有的字符。它使用 1 到 3 个字节的 UTF-8 字符，具体取决于字符的编码范围。

要在 Mysql 中保存 4 字节长度的 UTF-8 字符，需要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。个人觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8。

1.2 区别详解

编码范围
- UTF-8 可以表示大多数常见的字符，但对于一些罕见的字符和 emoji 表情等，可能无法正确表示。这些字符需要 4 个字节来编码，所以 utf8 会在遇到这些字符时报错或者出现乱码。
- utf8mb4 则可以表示几乎所有的 Unicode 字符，能够存储 Emoji 表情、部分罕用汉字、新增的 Unicode 字符等。
存储需求
- 由于 utf8mb4 可以表示更多的字符，所以它通常需要比 UTF-8 更多的存储空间。具体来说，UTF8MB4 可能需要使用 1 到 4 个字节来表示一个字符，而 UTF-8 通常只需要使用 1 到 3 个字节。
- 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。例如，一个 CHAR(10) 类型的字段，如果使用 utf8 字符集，那么它需要保留 10 * 3 = 30 个字节的空间。如果使用 utf8mb4 字符集，那么它需要保留 10 * 4 = 40 个字节的空间。
- 对于 VARCHAR 类型的字段，如果使用 utf8 字符集，那么它需要额外使用一个字节来记录字符串的长度。如果使用 utf8mb4 字符集，那么它需要额外使用两个字节来记录字符串的长度。
兼容性
- UTF-8 是一种广泛使用的字符集，几乎所有的操作系统和编程语言都支持它。因此，如果需要与其他系统进行数据交换，UTF-8 可能是一个更好的选择。
- 如果需要支持更广泛的字符范围，或者需要更高的安全性和兼容性，那么应该使用 utf8mb4 字符集。utf8mb4 虽然可以表示更多的字符，但并不是所有的系统都支持它。在使用 utf8mb4 时，需要确保应用程序和数据库服务器都支持这个字符集。

1.3 选择建议

如果应用程序只需要处理常见的字符，那么 UTF-8 可能是一个不错的选择，它具有广泛的兼容性和较低的存储需求。
如果应用程序需要处理一些罕见的字符或 emoji 表情等，那么 utf8mb4 可能是更好的选择，它可以确保数据能够正确地存储和显示所有的字符。
在选择字符集时，还需要考虑数据库的性能和存储需求。如果数据库中存储了大量的文本数据，那么选择一个合适的字符集可以提高数据库的性能和存储效率

总之，UTF-8 和 utf8mb4 都是 MySQL 中常用的字符集选项，都可以用来存储 Unicode 字符，它们之间的区别主要在于编码范围、存储需求和兼容性。在选择字符集时，需要根据应用程序的具体业务需求和实际情况来进行选择，以确保数据能够正确地存储和显示。虽然，utf8mb4 比 utf8 需要更多的存储空间，并且在某些情况下可能会导致性能略有下降。不过在现代硬件和应用场景下，utf8mb4 的性能影响通常可以忽略不计。

二、排序规则

2.1 排序规则概述

排序规则是指在比较和排序字符串时所遵循的规则，用于定义字符的排序和比较方式，而排序规则会影响字符串的比较、排序等操作。不同的字符集可以有不同的排序规则，甚至同一个字符集也可以有多种排序规则。在 MySQL中，比较常用的字符集是utf8和utf8mb4，这两个字符集是类似的，查看下 utf8mb4 字符集下支持的所有比较规则：

SHOW COLLATION LIKE 'utf8\_%';
SHOW COLLATION LIKE 'utf8mb4\_%';

Collation(排序方式)	Charset(字符集)	Id	Default(默认值)	Compiled	Sortlen(排序)
utf8mb4_general_ci	utf8mb4	45	Yes	Yes	1
utf8mb4_bin	utf8mb4	46		Yes	1
utf8mb4_unicode_ci	utf8mb4	224		Yes	8
utf8mb4_icelandic_ci	utf8mb4	225		Yes	8
utf8mb4_latvian_ci	utf8mb4	226		Yes	8
utf8mb4_romanian_ci	utf8mb4	227		Yes	8
utf8mb4_slovenian_ci	utf8mb4	228		Yes	8
utf8mb4_polish_ci	utf8mb4	229		Yes	8
utf8mb4_estonian_ci	utf8mb4	230		Yes	8
utf8mb4_spanish_ci	utf8mb4	231		Yes	8
utf8mb4_swedish_ci	utf8mb4	232		Yes	8
utf8mb4_turkish_ci	utf8mb4	233		Yes	8
utf8mb4_czech_ci	utf8mb4	234		Yes	8
utf8mb4_danish_ci	utf8mb4	235		Yes	8
utf8mb4_lithuanian_ci	utf8mb4	236		Yes	8
utf8mb4_slovak_ci	utf8mb4	237		Yes	8
utf8mb4_spanish2_ci	utf8mb4	238		Yes	8
utf8mb4_roman_ci	utf8mb4	239		Yes	8
utf8mb4_persian_ci	utf8mb4	240		Yes	8
utf8mb4_esperanto_ci	utf8mb4	241		Yes	8
utf8mb4_hungarian_ci	utf8mb4	242		Yes	8
utf8mb4_sinhala_ci	utf8mb4	243		Yes	8
utf8mb4_german2_ci	utf8mb4	244		Yes	8
utf8mb4_croatian_ci	utf8mb4	245		Yes	8
utf8mb4_unicode_520_ci	utf8mb4	246		Yes	8
utf8mb4_vietnamese_ci	utf8mb4	247		Yes	8

这些比较规则的命名还挺有规律的，具体规律如下：

比较规则名称以与其关联的字符集的名称开头，如上述查询结果的比较规则名称都是以utf8mb4开头的。
后边紧跟着该比较规则主要作用于哪种语言，比如 utf8_polish_ci 表示以波兰语的规则比较，utf8_spanish_ci 是以西班牙语的规则比较，utf8_general_ci 是一种通用的比较规则。

名称后缀意味着该比较规则是否区分语言中的重音、大小写啥的，具体可以用的值如下：

后缀	英文释义	描述
_ai	accent insensitive	不区分重音。也就是说，排序时 e、è、é、ê 和 ë 之间没有区别。
_as	accent sensitive	区分重音。
_ci	case insensitive	不区分大小写，即 “大小写不敏感”。
_cs	case sensitive	区分大小写，即大小写敏感。
_bin	binary	以二进制方式比较，区分大小写和重音符号。

比如 utf8mb4_general_ci 这个比较规则是以 ci 结尾的，说明不区分大小写。每种字符集都有一种默认的比较规则，SHOW COLLATION 返回结果中的Default 列的值为 YES 的就是该字符集的默认比较规则，比如 utf8mb4 字符集默认规则就是utf8mb4_general_ci，又比如utf8字符集默认规则就是 utf8_general_ci。排序规则的不同会影响字符串的比较和排序的结果，进而影响索引的效率和查询的性能。一般来说，_bin 排序规则的性能最高，因为它只需要按照二进制方式比较字符串，不需要考虑字符的大小写和重音符号等因素。_general_ci 排序规则的性能次之，因为它只需要按照一般方式比较字符串，不需要考虑字符的语言或地区等因素。_unicode_ci 和 _ci 排序规则的性能最低，因为它们需要按照 Unicode 标准或特定语言或地区的方式比较字符串，需要考虑字符的大小写和重音符号等因素。

2.2 比较规则

比较规则可以作用于四个级别，分别是服务器级别、数据库级别、表级别、列级别。服务器级别的比较规则由collation_server参数控制，如果创建数据库、表、列时没有显式的指定比较规则，则会继承上一级的比较规则。下面给出创建及修改库、表、列的比较规则的示例语句：

-- 创建数据库指定比较规则
CREATE DATABASE 数据库名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称];-- 修改数据库的比较规则
ALTER DATABASE 数据库名[[DEFAULT] CHARACTER SET 字符集名称][[DEFAULT] COLLATE 比较规则名称];-- 创建表时指定比较规则
CREATE TABLE 表名 (列的信息)[[DEFAULT] CHARACTER SET 字符集名称][COLLATE 比较规则名称]]-- 修改表的比较规则
ALTER TABLE 表名[[DEFAULT] CHARACTER SET 字符集名称][COLLATE 比较规则名称]# 创建时指定列的比较规则
CREATE TABLE 表名(列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],其他列...
);-- 修改列的比较规则
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

2.3 排序字符集

utf8 字符集默认规则是 utf8_general_ci，常用到的有utf8_general_ci、utf8_unicode_ci、utf8_bin等。而 utf8mb4 字符集默认规则是utf8mb4_general_ci，常用到的有utf8mb4_general_ci、utf8mb4_unicode_ci、utf8mb4_bin等，其他比较规则基本很少会用，下面简单了解下这几种规则的异同。

utf8_bin、utf8mb4_bin

将字符串每个字符用二进制数据编译存储，区分大小写，也区分重音符号。_bin 的比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对，所以很显然它是区分大小写的。

utf8_general_ci、utf8mb4_general_ci

utf8_general_ci 是 utf8 字符集的一种排序规则，不区分大小写，也不区分重音符号。utf8mb4_general_ci 是utf8字符集的一种排序规则，不支持扩展，它仅能够在字符之间进行逐个比较，不区分大小写，但会区分重音符号。utf8_general_ci 校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci 的校对规则相比，比较正确性较差。

注意：utf8mb4_general_ci 与 utf8_general_ci 基本兼容，只是前者支持更多字符。

utf8_unicode_ci、utf8mb4_unicode_ci

是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

utf8mb4_0900_ai_ci

MySQL 8.0 默认的是 utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci 中的一种。

以前，utf8mb4_general_ci 是默认排序规则。由于 utf8mb4_0900_ai_ci 排序规则现在是默认排序规则，因此默认情况下新表格可以存储基本多语言平面之外的字符。现在可以默认存储表情符号。如果需要重音灵敏度和区分大小写，则可以使用 utf8mb4_0900_as_cs 代替。

三、附录

3.1 utf8mb4_unicode_ci 和 utf8mb4_general_ci 的区别

准确性

排序字符集	说明
utf8mb4_unicode_ci	是基于标准的 Unicode 来排序和比较，能够在各种语言之间精确排序。
utf8mb4_general_ci	没有实现 Unicode 排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。

注意：在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

性能

排序字符集	说明
utf8mb4_general_ci	在比较和排序的时候更快。
utf8mb4_unicode_ci	在特殊情况下，Unicode 排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

注意：在绝大多数情况下，不会发生此类复杂比较。

3.2 实践建议

在开发时，个人建议:

尽可能使用 utf8mb4 字符集，以确保能够支持完整的 Unicode 字符集。
根据具体需求选择合适的排序规则，大多数情况下可以使用 utf8mb4_general_ci。
在创建表和列时就指定字符集和排序规则，避免以后出现编码问题。
如果需要存储大小写或重音符号敏感的数据，使用 utf8mb4_bin 排序规则。
对于加密数据或需要按二进制方式比较的场景，也应该使用 utf8mb4_bin。

四、总结

虽然推荐用 utf8mb4_unicode_ci，但是用 utf8mb4_general_ci 也没啥问题。因为 utf8mb4_unicode_ci 比较准确，utf8mb4_general_ci 速度比较快。通常情况下，新建数据库和创建 varchar 字符集类型字段时，一般选用 utf8mb4_general_ci 就可以。

通过正确设置和使用字符集及排序规则，我们可以有效地在 MySQL 中存储和处理 Unicode 字符数据，确保数据的完整性和一致性。编码问题一直是web开发中的一个棘手问题，希望这篇文章能够帮助大家更好地理解和应对编码相关的挑战。

前言