MySQL架构与底层原理之：`MySQL`的字符集和校对规则：从`latin1`到`utf8mb4`的底层编码。

好的，我们开始今天的讲座，主题是 MySQL 的字符集和校对规则，重点讲解从 latin1 到 utf8mb4 的底层编码。

一、字符集与校对规则：MySQL 的文本基石

在 MySQL 中，字符集（Character Set）决定了你能存储哪些字符，而校对规则（Collation）则决定了这些字符如何比较和排序。 理解这两者对于正确处理文本数据至关重要。

• 字符集 (Character Set): 字符集是一个字符集合，每个字符都有一个唯一的数字编码。 例如，ASCII 字符集包含了 128 个字符，包括字母、数字和一些控制字符。
• 校对规则 (Collation): 校对规则定义了字符集中字符的比较方式。 它包括大小写敏感性、重音符号处理等规则。 一个字符集可以有多个校对规则。

二、为什么需要字符集和校对规则？

想象一下，如果没有字符集，计算机如何知道 A 对应哪个二进制代码？ 如果没有校对规则，计算机又如何知道 a 和 A 是否应该被认为是相同的字符？ 字符集和校对规则解决了这些问题，使得计算机可以正确地存储、比较和排序文本数据。

三、latin1: MySQL 的默认字符集

在 MySQL 的早期版本中，latin1 (也称为 ISO-8859-1) 是默认字符集。latin1 是一个单字节字符集，它包含了 ASCII 字符集，并扩展了一些西欧字符，总共有 256 个字符。

• 优点:
  • 简单，易于实现。
  • 存储空间小，每个字符只需要一个字节。
• 缺点:
  • 只能表示有限的字符，无法表示中文、日文等字符。
  • 对一些特殊字符的支持不完整。

四、utf8: 一个有缺陷的过渡方案

为了支持更多的字符，MySQL 引入了 utf8 字符集。理论上，utf8 应该是一个可变长度的字符集，可以使用 1 到 4 个字节来表示一个字符。但是，MySQL 的 utf8 字符集实际上只支持 1 到 3 个字节的编码，这意味着它无法表示所有 Unicode 字符，尤其是 Unicode 补充平面（Supplementary Planes）中的字符，比如一些 emoji 表情。

• utf8 的编码方式:

utf8 使用变长编码，编码规则如下：

Unicode 码点范围 (十六进制)	编码方式 (二进制)
0000 – 007F	0xxxxxxx
0080 – 07FF	110xxxxx 10xxxxxx
0800 – FFFF	1110xxxx 10xxxxxx 10xxxxxx

例如，字符 A 的 Unicode 码点是 0x41，它落在 0000 - 007F 的范围内，因此它的 utf8 编码就是 01000001 (二进制)，也就是 0x41 (十六进制)。

中文 "你" 的 Unicode 码点是 0x4F60，它落在 0800 - FFFF 的范围内，因此它的 utf8 编码需要三个字节。

• utf8 的问题:

由于 MySQL 的 utf8 只能表示 BMP (Basic Multilingual Plane) 中的字符，因此无法存储一些 Unicode 字符，比如 emoji 表情。 这会导致数据丢失或存储错误。

五、utf8mb4: 真正的 Unicode 支持

为了解决 utf8 的问题，MySQL 引入了 utf8mb4 字符集。 utf8mb4 是一个真正的 UTF-8 实现，它支持 1 到 4 个字节的编码，可以表示所有 Unicode 字符，包括 Unicode 补充平面中的字符。

• utf8mb4 的编码方式:

utf8mb4 的编码方式与标准的 UTF-8 编码方式完全一致，可以使用 1 到 4 个字节来表示一个字符。

Unicode 码点范围 (十六进制)	编码方式 (二进制)
0000 – 007F	0xxxxxxx
0080 – 07FF	110xxxxx 10xxxxxx
0800 – FFFF	1110xxxx 10xxxxxx 10xxxxxx
10000 – 10FFFF	11110xxx 10xxxxxx 10xxxxxx 10xxxxxx

例如，emoji 表情 "😀" 的 Unicode 码点是 0x1F600，它落在 10000 - 10FFFF 的范围内，因此它的 utf8mb4 编码需要四个字节。

• 为什么选择 utf8mb4?

utf8mb4 是 MySQL 中推荐使用的字符集，因为它能够完整地支持 Unicode 字符，避免了数据丢失或存储错误的问题。

六、字符集与校对规则的设置

在 MySQL 中，可以在多个级别设置字符集和校对规则：

• 服务器级别: 影响所有数据库的默认字符集和校对规则。
• 数据库级别: 影响数据库中所有表的默认字符集和校对规则。
• 表级别: 影响表中所有列的默认字符集和校对规则。
• 列级别: 影响单个列的字符集和校对规则。

设置字符集和校对规则的 SQL 语句:

• 创建数据库时指定字符集和校对规则:

CREATE DATABASE mydatabase
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_unicode_ci;

• 创建表时指定字符集和校对规则:

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• 修改数据库的字符集和校对规则:

ALTER DATABASE mydatabase
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

• 修改表的字符集和校对规则:

ALTER TABLE mytable
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• 修改列的字符集和校对规则:

ALTER TABLE mytable
  MODIFY name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看字符集和校对规则:

SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';
SHOW CREATE DATABASE mydatabase;
SHOW CREATE TABLE mytable;

七、校对规则详解

校对规则定义了字符的比较方式，影响了排序和搜索的结果。 MySQL 提供了多种校对规则，常见的有：

• utf8mb4_general_ci: 不区分大小写 (Case Insensitive)，不区分重音符号 (Accent Insensitive)。 性能较好，但比较结果可能不准确。
• utf8mb4_unicode_ci: 不区分大小写，不区分重音符号，使用 Unicode 排序规则。 比较结果更准确，但性能稍差。
• utf8mb4_bin: 区分大小写，基于二进制值进行比较。 性能最好，但比较结果最严格。

校对规则的选择:

选择校对规则需要根据实际需求。 如果需要不区分大小写和重音符号的比较，可以选择 utf8mb4_general_ci 或 utf8mb4_unicode_ci。 如果需要区分大小写，可以选择 utf8mb4_bin。 通常情况下，utf8mb4_unicode_ci 是一个比较好的选择，因为它在准确性和性能之间取得了平衡。

校对规则的后缀含义:

• _ci (Case Insensitive): 不区分大小写。
• _cs (Case Sensitive): 区分大小写。
• _ai (Accent Insensitive): 不区分重音符号。
• _as (Accent Sensitive): 区分重音符号。
• _bin: 基于二进制值进行比较。

示例:

假设我们有一个表 users，其中包含一个 username 列，字符集为 utf8mb4。

CREATE TABLE users (
  id INT PRIMARY KEY,
  username VARCHAR(255)
);

INSERT INTO users (id, username) VALUES
(1, 'John'),
(2, 'john'),
(3, 'Jöhn');

如果我们使用 utf8mb4_general_ci 进行排序：

SELECT * FROM users ORDER BY username COLLATE utf8mb4_general_ci;

结果可能是：

id | username
---|---------
1  | John
2  | john
3  | Jöhn

可以看到，John 和 john 被认为是相同的，并且 Jöhn 也被排在它们之后，因为 utf8mb4_general_ci 不区分大小写和重音符号。

如果我们使用 utf8mb4_bin 进行排序：

SELECT * FROM users ORDER BY username COLLATE utf8mb4_bin;

结果可能是：

id | username
---|---------
1  | John
3  | Jöhn
2  | john

可以看到，John、Jöhn 和 john 被认为是不同的，并且按照二进制值进行了排序。

八、latin1 到 utf8mb4 的迁移

将数据库从 latin1 迁移到 utf8mb4 需要谨慎操作，以避免数据丢失或损坏。 以下是一些建议步骤：

1. 备份数据库: 在进行任何更改之前，务必备份数据库。
2. 修改数据库、表和列的字符集和校对规则: 使用 ALTER DATABASE、ALTER TABLE 和 ALTER TABLE MODIFY 语句修改字符集和校对规则。
3. 检查数据: 迁移完成后，检查数据是否正确显示。 特别是包含非 ASCII 字符的数据。
4. 修改应用程序代码: 确保应用程序代码使用正确的字符集连接到数据库。

示例代码 (Python):

import mysql.connector

# 连接到数据库
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase",
  charset="utf8mb4"  # 确保连接使用 utf8mb4 字符集
)

mycursor = mydb.cursor()

# 查询数据
mycursor.execute("SELECT * FROM mytable")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

mydb.close()

九、常见问题与注意事项

• 存储空间: utf8mb4 比 latin1 占用更多的存储空间，因为每个字符可能需要 1 到 4 个字节。
• 性能: utf8mb4 的性能可能比 latin1 稍差，因为需要处理变长编码。
• 兼容性: 一些旧的应用程序可能不支持 utf8mb4。
• 乱码问题: 如果字符集设置不正确，可能会出现乱码问题。 请确保数据库、表、列和应用程序代码都使用相同的字符集。
• 字符集转换: 可以使用 CONVERT 函数进行字符集转换。 例如：SELECT CONVERT('你好' USING latin1) FROM dual; 将 ‘你好’ 从当前字符集转换为 latin1。

十、关于字符编码的底层原理

理解字符编码的底层原理对于解决字符集相关问题至关重要。

• ASCII: ASCII 是最简单的字符编码，使用 7 位二进制数 (0-127) 表示 128 个字符。
• ISO-8859-1 (latin1): ISO-8859-1 是 ASCII 的扩展，使用 8 位二进制数 (0-255) 表示 256 个字符。
• Unicode: Unicode 是一个字符集，它为世界上几乎所有的字符都分配了一个唯一的数字编码 (码点)。 Unicode 的码点范围是 U+0000 到 U+10FFFF。
• UTF-8: UTF-8 是一种变长编码，它使用 1 到 4 个字节来表示 Unicode 字符。 UTF-8 是互联网上最常用的字符编码。
• UTF-16: UTF-16 是一种变长编码，它使用 2 或 4 个字节来表示 Unicode 字符。
• UTF-32: UTF-32 是一种定长编码，它使用 4 个字节来表示 Unicode 字符。

字符编码的过程:

1. 字符 -> Unicode 码点: 将字符转换为对应的 Unicode 码点。
2. Unicode 码点 -> 字节序列: 根据 UTF-8、UTF-16 或 UTF-32 等编码方式，将 Unicode 码点转换为字节序列。
3. 字节序列 -> 存储: 将字节序列存储到计算机中。

字符解码的过程:

1. 读取字节序列: 从计算机中读取字节序列。
2. 字节序列 -> Unicode 码点: 根据 UTF-8、UTF-16 或 UTF-32 等编码方式，将字节序列转换为 Unicode 码点。
3. Unicode 码点 -> 字符: 将 Unicode 码点转换为对应的字符。

理解这些底层原理可以帮助你更好地理解字符集和校对规则，并解决相关的编码问题。

十一、字符集选择的考量

在选择字符集时，需要考虑以下几个因素：

• 应用程序的需求: 应用程序需要支持哪些字符？
• 存储空间: 字符集会占用多少存储空间？
• 性能: 字符集的性能如何？
• 兼容性: 字符集是否与现有系统兼容？
• 未来扩展性: 字符集是否能够支持未来的字符？

通常情况下，utf8mb4 是一个比较好的选择，因为它能够完整地支持 Unicode 字符，并且具有良好的兼容性和扩展性。 但是，在某些特殊情况下，可能需要选择其他的字符集。

十二、总结：字符集与校对规则是数据正确显示的基础

字符集决定了数据的表示范围，校对规则决定了数据的比较方式。从 latin1 到 utf8mb4 的演进，是数据库更好地支持多语言和更丰富的字符的必然过程。理解和正确配置字符集和校对规则，是保证数据库数据正确存储和显示的基础。