mysql的字符集和比较规则

深度解读mysql是怎样运行的

MySQL的字符集和比较规则是其处理字符串存储、传输及比较的核心机制：

---

一、字符集（Character Set）

1. 定义

• 字符集是字符与二进制数据的映射规则，用于编码（文字→二进制）和解码（二进制→文字）。
• 常见字符集包括ASCII、GB2312、GBK、UTF-8等。MySQL中UTF-8分为utf8mb3（1-3字节）和utf8mb4（1-4字节），后者支持更广泛字符（如Emoji）。

2. 级别设置MySQL支持四个级别的字符集配置：

• 服务器级别：通过character_set_server和collation_server系统变量设置，默认值可能为latin1（旧版本）或utf8mb4（MySQL 8.0+）。
• 数据库级别：创建或修改数据库时指定，例如：
  CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
  若未指定，则继承服务器设置。
• 表级别：继承数据库设置，也可单独指定。
• 列级别：同一表中不同列可设置不同字符集。

3. 查看与修改

• 查看当前字符集：
  SHOW VARIABLES LIKE ‘character_set_%’; – 查看所有字符集相关变量
  SHOW CHARACTER SET; – 查看支持的字符集
• 修改字符集：
• 配置文件：修改my.cnf或my.ini，设置character_set_server=utf8mb4。
• 运行时修改：
  SET character_set_server = ‘utf8mb4’; – 修改服务器级别字符集

---

二、比较规则（Collation）

1. 定义

• 比较规则定义字符串的排序和比较方式，例如是否区分大小写、重音符号等。
• 每个字符集有多个比较规则，默认规则通常以_ci（case-insensitive，不区分大小写）结尾，如utf8mb4_0900_ai_ci。

2. 级别设置

• 比较规则与字符集绑定，修改字符集时比较规则会自动调整为该字符集的默认值，反之亦然。例如：
  ALTER TABLE t MODIFY col VARCHAR(10) COLLATE utf8mb4_bin; – 修改列的排序规则
  此时字符集会同步调整为utf8mb4。

3. 查看与修改

• 查看比较规则：
  SHOW COLLATION; – 查看所有比较规则
  SHOW VARIABLES LIKE ‘collation_%’; – 查看当前比较规则设置

---

三、客户端与服务器的字符集转换

1. 通信流程

• 请求阶段：客户端使用character_set_client编码请求，服务器解码后转换为character_set_connection处理。
• 响应阶段：服务器将结果按character_set_results编码返回，客户端解码显示。
• 变量关联：
• character_set_client：客户端请求的字符集。
• character_set_connection：服务器处理时的中间字符集。
• character_set_results：响应字符集。

2. 乱码问题

• 若客户端与服务器字符集不一致，可能导致乱码。需确保三者统一，例如：
  SET NAMES ‘utf8mb4’; – 同时设置client/connection/results的字符集

---

四、注意事项

1. 兼容性问题

• 修改字符集时需确保现有数据兼容新字符集，否则可能转换失败。例如：将包含中文字符的列从utf8mb4改为ascii会报错。
• 推荐使用utf8mb4替代utf8mb3，以支持更全面的Unicode字符（如Emoji）。

2. 默认值变化

• MySQL 8.0+默认字符集为utf8mb4，而旧版本可能为latin1。

---

总结

MySQL通过多级别字符集和比较规则实现灵活的字符串管理。正确配置需注意客户端与服务器的一致性，优先使用utf8mb4以兼容现代应用需求，同时避免因字符集转换导致的数据错误。

示例：
假设表结构如下：
CREATE TABLE example (
id INT PRIMARY KEY,
name VARCHAR(20) CHARSET utf8mb4,
code CHAR(10) CHARSET latin1
) ROW_FORMAT=COMPACT;

• 插入数据：id=1, name=‘张三’, code=‘A123’
• 存储过程：
  1. 变长字段长度列表：
• name字段实际长度：张三在utf8mb4中占6字节（0xE5BCA0 0xE4B889），长度值0x06。
• code字段为latin1定长，不记录长度。
• 变长字段列表逆序存储：[0x06]。
  1. 记录头信息：next_record指向下一条记录的偏移量（如0x0123）。
  2. 真实数据：
• id=1（4字节）、name的6字节数据、code的10字节数据（定长，不足部分填充空格）。
  3. 解码：按utf8mb4解码name字段，按latin1解码code字段。