mysql的字符集和比较规则
- 一、字符集（Character Set）
- 二、比较规则（Collation）
- 三、客户端与服务器的字符集转换
- 四、注意事项
- 总结
深度解读mysql是怎样运行的
MySQL的字符集和比较规则是其处理字符串存储、传输及比较的核心机制：
一、字符集（Character Set）
- 定义
- 字符集是字符与二进制数据的映射规则，用于编码（文字→二进制）和解码（二进制→文字）。
- 常见字符集包括ASCII、GB2312、GBK、UTF-8等。MySQL中UTF-8分为utf8mb3（1-3字节）和utf8mb4（1-4字节），后者支持更广泛字符（如Emoji）。
- 级别设置MySQL支持四个级别的字符集配置：
- 服务器级别：通过character_set_server和collation_server系统变量设置，默认值可能为latin1（旧版本）或utf8mb4（MySQL 8.0+）。
- 数据库级别：创建或修改数据库时指定，例如：
CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
若未指定，则继承服务器设置。 - 表级别：继承数据库设置，也可单独指定。
- 列级别：同一表中不同列可设置不同字符集。
- 查看与修改
- 查看当前字符集：
SHOW VARIABLES LIKE ‘character_set_%’; – 查看所有字符集相关变量
SHOW CHARACTER SET; – 查看支持的字符集 - 修改字符集：
- 配置文件：修改my.cnf或my.ini，设置character_set_server=utf8mb4。
- 运行时修改：
SET character_set_server = ‘utf8mb4’; – 修改服务器级别字符集
二、比较规则（Collation）
- 定义
- 比较规则定义字符串的排序和比较方式，例如是否区分大小写、重音符号等。
- 每个字符集有多个比较规则，默认规则通常以_ci（case-insensitive，不区分大小写）结尾，如utf8mb4_0900_ai_ci。
- 级别设置
- 比较规则与字符集绑定，修改字符集时比较规则会自动调整为该字符集的默认值，反之亦然。例如：
ALTER TABLE t MODIFY col VARCHAR(10) COLLATE utf8mb4_bin; – 修改列的排序规则
此时字符集会同步调整为utf8mb4。
- 查看与修改
- 查看比较规则：
SHOW COLLATION; – 查看所有比较规则
SHOW VARIABLES LIKE ‘collation_%’; – 查看当前比较规则设置
三、客户端与服务器的字符集转换
- 通信流程
- 请求阶段：客户端使用character_set_client编码请求，服务器解码后转换为character_set_connection处理。
- 响应阶段：服务器将结果按character_set_results编码返回，客户端解码显示。
- 变量关联：
- character_set_client：客户端请求的字符集。
- character_set_connection：服务器处理时的中间字符集。
- character_set_results：响应字符集。
- 乱码问题
- 若客户端与服务器字符集不一致，可能导致乱码。需确保三者统一，例如：
SET NAMES ‘utf8mb4’; – 同时设置client/connection/results的字符集
四、注意事项
- 兼容性问题
- 修改字符集时需确保现有数据兼容新字符集，否则可能转换失败。例如：将包含中文字符的列从utf8mb4改为ascii会报错。
- 推荐使用utf8mb4替代utf8mb3，以支持更全面的Unicode字符（如Emoji）。
- 默认值变化
- 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’
- 存储过程：
- 变长字段长度列表：
- name字段实际长度：张三在utf8mb4中占6字节（0xE5BCA0 0xE4B889），长度值0x06。
- code字段为latin1定长，不记录长度。
- 变长字段列表逆序存储：[0x06]。
- 记录头信息：next_record指向下一条记录的偏移量（如0x0123）。
- 真实数据：
- id=1（4字节）、name的6字节数据、code的10字节数据（定长，不足部分填充空格）。
3. 解码：按utf8mb4解码name字段，按latin1解码code字段。
