MySQL高阶讲座之：`MySQL`的`Character Set`：`utf8`、`utf8mb4`与`Collation`的深层理解。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷们，大家好！今天咱们聊聊MySQL里那些让人头疼，但又不得不面对的字符集和校对规则，也就是Character Set和Collation。别害怕，我会尽量用大白话，加上代码示例，把这俩玩意儿给您讲明白。

一、字符集 (Character Set)：存啥玩意儿？

简单来说，字符集就是MySQL用来存储字符的一套编码规则。您可以把它想象成一个翻译器，把我们看到的文字（比如汉字、英文、表情符号）转换成计算机能理解的二进制数字。

1. 常见的字符集：

latin1 (也叫 iso-8859-1): 这是MySQL默认的字符集，历史悠久，但只能存储西欧字符，不支持中文。您要是用它来存中文，那画面太美我不敢看，全是乱码！
gbk: 支持简体中文和一些常用字符，但范围有限。
utf8: 曾经是MySQL里最常用的Unicode字符集，注意，我说的是曾经。它只能存储一部分Unicode字符，对于一些罕见字符（比如表情符号）就无能为力了。
utf8mb4: 这才是MySQL里真正完整支持Unicode的字符集！它能存储所有Unicode字符，包括表情符号、特殊符号等等。所以，现在推荐您用它。

2. 为什么要用 utf8mb4 而不是 utf8？

这涉及到Unicode编码的细节。Unicode用不同的编码方式来表示字符，其中UTF-8就是一种常见的编码方式。UTF-8是一种变长编码，可以用1到4个字节来表示一个字符。

utf8 (MySQL): 最初的utf8在MySQL里，只允许使用1-3个字节来存储Unicode字符。这就导致它无法存储一些需要4个字节才能表示的Unicode字符，比如一些表情符号。
utf8mb4: 真正的UTF-8，它可以使用1-4个字节来存储Unicode字符，完美支持所有Unicode字符。

代码示例：创建表时指定字符集

CREATE TABLE `my_table` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `content` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CHARACTER SET utf8mb4: 指定name和content列的字符集为utf8mb4。
COLLATE utf8mb4_unicode_ci: 指定name和content列的校对规则为utf8mb4_unicode_ci。
DEFAULT CHARSET=utf8mb4: 指定整个表的默认字符集为utf8mb4。

二、校对规则 (Collation)：怎么比大小？

校对规则定义了字符的排序和比较方式。比如，在英语里，A排在B前面，但在某些语言里，可能不是这样。校对规则还决定了是否区分大小写、是否忽略重音符号等等。

1. 校对规则的命名规则：

校对规则的命名通常遵循一定的模式：charset_language_ci/cs/bin

charset: 字符集，比如utf8mb4。
language: 语言，比如unicode（通用Unicode校对规则），general（通用校对规则），german2（德语）。
ci/cs/bin:
- ci (case insensitive): 不区分大小写。
- cs (case sensitive): 区分大小写。
- bin (binary): 二进制比较，区分大小写，性能最高。

2. 常见的校对规则：

utf8mb4_general_ci: utf8mb4字符集的通用校对规则，不区分大小写。性能相对较高，但准确性稍差。
utf8mb4_unicode_ci: utf8mb4字符集的Unicode校对规则，不区分大小写。准确性更高，但性能相对较低。
utf8mb4_bin: utf8mb4字符集的二进制校对规则，区分大小写。性能最高，但只能进行简单的二进制比较。

3. 如何选择校对规则？

性能优先，且不区分大小写： utf8mb4_general_ci
准确性优先，且不区分大小写： utf8mb4_unicode_ci
需要区分大小写： utf8mb4_bin

代码示例：查询时指定校对规则

SELECT * FROM `my_table` WHERE `name` = 'test' COLLATE utf8mb4_general_ci;

这条SQL语句在比较name列的值时，使用utf8mb4_general_ci校对规则，不区分大小写。

三、 MySQL里字符集和校对规则在哪里设置？

MySQL里有多个地方可以设置字符集和校对规则：

服务器级别 (Server Level):
- 影响所有数据库的默认字符集和校对规则。
- 可以通过修改MySQL配置文件 (my.cnf 或 my.ini) 来设置。
```
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
```
数据库级别 (Database Level):
- 影响数据库中所有表的默认字符集和校对规则。
- 可以在创建数据库时指定，或者修改数据库的字符集和校对规则。
```
CREATE DATABASE `my_database` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER DATABASE `my_database` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

表级别 (Table Level):

影响表中所有列的默认字符集和校对规则。
可以在创建表时指定，或者修改表的字符集和校对规则。

CREATE TABLE `my_table` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `my_table` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

列级别 (Column Level):

影响特定列的字符集和校对规则。
可以在创建列时指定，或者修改列的字符集和校对规则。

CREATE TABLE `my_table` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`)
);

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

连接级别 (Connection Level):

影响当前连接的字符集。
可以通过SQL语句设置。

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET collation_connection = utf8mb4_unicode_ci; -- 可选

优先级顺序： 列级别 > 表级别 > 数据库级别 > 服务器级别。连接级别的设置只影响当前连接。

四、常见问题及解决方案

乱码问题：
- 原因： 字符集设置不一致，或者客户端使用的字符集与数据库存储的字符集不匹配。
- 解决方案：
  - 确保所有级别（服务器、数据库、表、列、连接）的字符集都设置为utf8mb4。
  - 检查客户端连接MySQL时的字符集设置。
  - 检查网页或应用程序的编码设置。
表情符号无法存储：
- 原因： 使用了utf8字符集。
- 解决方案： 将字符集升级到utf8mb4。
排序结果不符合预期：
- 原因： 使用了不合适的校对规则。
- 解决方案： 选择合适的校对规则，比如utf8mb4_unicode_ci 或 utf8mb4_bin。

五、字符集转换和迁移

如果您的数据库已经使用了latin1或utf8字符集，需要将其转换为utf8mb4，可以按照以下步骤操作：

备份数据库： 确保在进行任何修改之前备份数据库，以防万一。
修改配置文件： 在MySQL配置文件 (my.cnf 或 my.ini) 中设置服务器级别的字符集和校对规则。
```
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4
```
重启MySQL服务器： 使配置文件生效。

修改数据库、表和列的字符集和校对规则： 使用ALTER语句修改数据库、表和列的字符集和校对规则。

-- 修改数据库
ALTER DATABASE `my_database` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改表
ALTER TABLE `my_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 修改列
ALTER TABLE `my_table` MODIFY `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

注意： CONVERT TO CHARACTER SET语句会将表中的数据转换为新的字符集。如果数据量很大，这个过程可能会比较耗时。

修改客户端连接字符集： 确保客户端连接MySQL时使用的字符集也设置为utf8mb4。

六、最佳实践总结

始终使用 utf8mb4 字符集： 避免未来出现表情符号或特殊字符无法存储的问题。
根据需求选择合适的校对规则： 在性能和准确性之间做出权衡。
保持字符集设置一致： 确保所有级别（服务器、数据库、表、列、连接）的字符集都设置为utf8mb4。
备份数据： 在进行任何字符集转换或迁移之前备份数据。

七、深入理解 Collation 的排序原理 (以 utf8mb4_unicode_ci 为例)

虽然 utf8mb4_unicode_ci 不区分大小写，但它并不是简单地将所有字符转换为小写后再进行比较。它的排序原理基于 Unicode Collation Algorithm (UCA)，这是一个复杂的标准，旨在提供跨语言的、一致的排序结果。

UCA 的核心思想是为每个 Unicode 字符分配一个或多个权重，这些权重用于比较字符的优先级。权重分为多个级别，级别越高，优先级越高。 utf8mb4_unicode_ci 通常使用前三个级别：

Level 1 (Primary Level): 主要权重，用于区分不同的字母。例如，a 和 b 的 primary weight 不同。 utf8mb4_unicode_ci 在这个级别会忽略大小写和重音符号。
Level 2 (Secondary Level): 次要权重，用于区分带重音符号的字母。例如，a 和 á 的 secondary weight 不同。 utf8mb4_unicode_ci 会忽略重音符号。
Level 3 (Tertiary Level): 第三级权重，用于区分大小写。例如，a 和 A 的 tertiary weight 不同。 utf8mb4_unicode_ci 会忽略大小写。

当比较两个字符串时，utf8mb4_unicode_ci 会逐个字符地比较它们的权重。首先比较 primary weight，如果不同，则可以确定排序顺序。如果 primary weight 相同，则比较 secondary weight，依此类推。

代码示例 (模拟 UCA 排序):

虽然我们无法直接在 MySQL 中访问 UCA 的权重，但可以通过一些技巧来模拟它的排序行为。以下是一个 Python 代码示例，用于演示 utf8mb4_unicode_ci 如何处理大小写和重音符号：

import unicodedata

def normalize_string(s):
  """Remove accents and case."""
  s = ''.join(c for c in unicodedata.normalize('NFD', s)
                if unicodedata.category(c) != 'Mn')
  return s.lower()

def compare_strings(s1, s2):
  """Compare two strings using a simplified UCA-like approach."""
  s1_normalized = normalize_string(s1)
  s2_normalized = normalize_string(s2)

  if s1_normalized < s2_normalized:
    return -1
  elif s1_normalized > s2_normalized:
    return 1
  else:
    return 0

# 示例
string1 = "café"
string2 = "Cafe"
result = compare_strings(string1, string2)

if result < 0:
  print(f"'{string1}' comes before '{string2}'")
elif result > 0:
  print(f"'{string1}' comes after '{string2}'")
else:
  print(f"'{string1}' is equal to '{string2}'") # 输出这个

这个 Python 代码首先使用 unicodedata.normalize 函数将字符串转换为 NFD 形式，然后移除所有重音符号。接下来，它将字符串转换为小写。最后，它比较两个字符串的标准化形式。

注意: 这只是一个简化的模拟，UCA 实际上比这复杂得多。但它能帮助您理解 utf8mb4_unicode_ci 的排序原理。

总结:

utf8mb4_unicode_ci 的排序原理基于 Unicode Collation Algorithm，它通过为每个字符分配权重来实现跨语言的、一致的排序。它会忽略大小写和重音符号，但在其他方面会尽可能保持准确。

好了，今天的讲座就到这里。希望通过今天的讲解，您对MySQL的字符集和校对规则有了更深入的了解。记住，选择合适的字符集和校对规则是保证数据正确存储和排序的关键！感谢各位的收看，咱们下期再见！

发表回复 取消回复

发表回复取消回复