【重学MySQL】九十四、MySQL请求到响应过程中字符集的变化

一、客户端连接字符集设置

1.1 客户端指定字符集的方式

• 连接时指定：

  mysql --default-character-set=utf8mb4 -u 用户名 -p
  

• 配置文件设置：
  在my.cnf或my.ini的[client]部分添加：

  [client]
  default-character-set=utf8mb4
  

• 会话中设置：

  SET NAMES utf8mb4;  -- 等价于同时设置 character_set_client、connection、results
  

二、服务器连接字符集默认值及配置

2.1 服务器级默认字符集

• 配置文件设置：
  在my.cnf的[mysqld]部分配置：

  [mysqld]
  character-set-server=utf8mb4
  collation-server=utf8mb4_unicode_ci
  

• 默认值：
  若未显式配置，服务器默认使用latin1字符集和对应的校对规则（如latin1_swedish_ci）。

2.2 查询处理中的字符集转换规则

2.2.1 客户端到服务器的转换

• 客户端发送的SQL语句：使用character_set_client编码。
• 服务器转换：
  将SQL语句从character_set_client转换为character_set_connection编码（除非字符串中显式指定了字符集，如_utf8'字符串'）。

2.2.2 服务器内部的比较和存储

• 字符串比较：使用collation_connection规则。
• 字段值比较：优先使用字段自身的校对规则（若显式指定）。
• 数据存储：
  • 字段使用其显式指定的字符集（若未指定，则继承表、数据库或服务器的字符集）。

2.3 结果集返回时的字符集编码机制

• 服务器返回的结果集（包括字段值和元数据）使用character_set_results编码。
• 特殊情况：
  若character_set_results=NULL，则不进行转换，直接使用字段或元数据的原始编码。

三、字符集转换流程总结

阶段	字符集/规则	作用
客户端发送请求	character_set_client	定义客户端发送的SQL语句的编码。
服务器转换请求	character_set_connection collation_connection	将SQL语句转换为连接字符集编码，并使用连接校对规则进行比较。
服务器处理请求	字段/表的字符集 字段/表的校对规则	存储/检索数据时使用字段或表的字符集，并应用相应的校对规则。
服务器返回结果	character_set_results	将结果转换为客户端指定的编码，返回给客户端。

四、常见问题及解决方法

4.1 乱码问题

• 原因：客户端、连接、服务器、数据库、表、字段的字符集不一致。
• 解决方法：
  • 统一各层级的字符集设置（如全部使用utf8mb4）。
  • 在客户端连接时显式指定字符集：SET NAMES utf8mb4;。

4.2 比较错误

• 原因：校对规则不符合需求（如中文排序未使用支持中文的规则）。
• 解决方法：
  • 显式指定校对规则（如utf8mb4_unicode_ci）。
  • 修改表或字段的校对规则：

    ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    

4.3 性能优化

• 对性能敏感的场景：选择utf8_general_ci（速度快，但排序可能不准确）。
• 需要准确排序时：使用utf8mb4_unicode_ci或utf8mb4_0900_ai_ci（遵循Unicode标准，排序更准确）。

五、示例配置

5.1 服务器级配置（my.cnf）

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci[client]
default-character-set=utf8mb4[mysql]
default-character-set=utf8mb4

5.2 创建数据库与表

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

5.3 修改现有表

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

六、总结

• 字符集转换的核心流程：客户端发送的SQL语句经过连接字符集转换、服务器内部处理、结果集编码返回三个阶段。
• 一致性是关键：确保客户端、连接、服务器、数据库、表、字段的字符集一致，避免乱码和比较错误。
• 性能与准确性的平衡：根据场景选择合适的字符集和校对规则（如utf8mb4支持emoji，utf8mb4_unicode_ci支持准确排序）。