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

前言

  在创建数据库时，我们经常会需要填写数据库的所用字符集、排序规则，字符集和排序规则是两个非常重要的概念，它们决定了数据库如何存储和比较字符串数据。在 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\_%';

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

• 比较规则名称以与其关联的字符集的名称开头，如上述查询结果的比较规则名称都是以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开发中的一个棘手问题，希望这篇文章能够帮助大家更好地理解和应对编码相关的挑战。