MySQL 字符集和排序规则详解

字符集(Character Set)

字符集是数据库中用于定义字符编码的规则集合，它决定了MySQL如何存储和处理文本数据。

常见字符集

1. latin1: 西欧字符集，单字节编码
2. utf8: Unicode字符集的变长编码(MySQL中的utf8实际上是UTF-8的子集，最多3字节)
3. utf8mb4: 完整的UTF-8实现(支持4字节字符，如emoji表情)
4. gbk: 简体中文编码
5. big5: 繁体中文编码

查看支持的字符集

SHOW CHARACTER SET;

设置字符集

可以在不同级别设置字符集：

1. 服务器级别:

   SET character_set_server = 'utf8mb4';
   

   或在my.cnf配置文件中设置：

   [mysqld]
   character-set-server=utf8mb4
   

2. 数据库级别:

   CREATE DATABASE db_name CHARACTER SET utf8mb4;
   ALTER DATABASE db_name CHARACTER SET utf8mb4;
   

3. 表级别:

   CREATE TABLE table_name (...) CHARACTER SET utf8mb4;
   ALTER TABLE table_name CHARACTER SET utf8mb4;
   

4. 列级别:

   CREATE TABLE table_name (
     col_name VARCHAR(100) CHARACTER SET utf8mb4
   );
   ALTER TABLE table_name MODIFY col_name VARCHAR(100) CHARACTER SET utf8mb4;
   

排序规则(Collation)

排序规则定义了字符集中字符的比较和排序规则，包括大小写敏感性和重音敏感性等。

常见排序规则

1. utf8mb4_general_ci: 不区分大小写，不区分重音
2. utf8mb4_unicode_ci: 基于Unicode标准排序，更准确但稍慢
3. utf8mb4_bin: 二进制比较，区分大小写
4. utf8mb4_0900_ai_ci: MySQL 8.0引入，基于Unicode 9.0标准

查看支持的排序规则

SHOW COLLATION;

排序规则命名规则

排序规则名称通常遵循以下格式：

字符集_语言_后缀

后缀含义：

• ci: 不区分大小写(Case Insensitive)
• cs: 区分大小写(Case Sensitive)
• bin: 二进制比较
• ai: 不区分重音(Accent Insensitive)
• as: 区分重音(Accent Sensitive)

设置排序规则

与字符集类似，可以在不同级别设置排序规则：

-- 数据库级别
CREATE DATABASE db_name COLLATE utf8mb4_unicode_ci;
ALTER DATABASE db_name COLLATE utf8mb4_unicode_ci;

-- 表级别
CREATE TABLE table_name (...) COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name COLLATE utf8mb4_unicode_ci;

-- 列级别
CREATE TABLE table_name (
  col_name VARCHAR(100) COLLATE utf8mb4_bin
);
ALTER TABLE table_name MODIFY col_name VARCHAR(100) COLLATE utf8mb4_bin;

连接相关字符集设置

客户端连接时涉及多个字符集变量：

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

重要变量：

• character_set_client: 客户端发送的语句编码
• character_set_connection: 连接使用的编码
• character_set_results: 返回结果的编码
• character_set_database: 当前数据库的默认编码
• character_set_server: 服务器默认编码

可以统一设置：

SET NAMES 'utf8mb4';

这相当于：

SET character_set_client = 'utf8mb4';
SET character_set_connection = 'utf8mb4';
SET character_set_results = 'utf8mb4';

最佳实践

1. 推荐使用utf8mb4:

   • 支持所有Unicode字符，包括emoji
   • 避免"utf8"的3字节限制问题

2. 一致性:

   • 确保应用、连接、数据库、表和列的字符集设置一致
   • 避免混合使用不同字符集

3. 排序规则选择:

   • 一般情况: utf8mb4_unicode_ci
   • 需要精确比较: utf8mb4_bin
   • MySQL 8.0+: 考虑使用 utf8mb4_0900_ai_ci

4. 性能考虑:

   • _bin排序规则通常最快
   • _general_ci比_unicode_ci快但准确性稍低

常见问题

1. 乱码问题:

   • 确保连接字符集与数据库字符集一致
   • 检查应用、数据库和表的多级字符集设置

2. 索引使用问题:

   • 排序规则影响索引使用，如WHERE col = 'value'在_ci排序规则下不区分大小写

3. emoji支持:

   • 必须使用utf8mb4字符集
   • 确保连接也使用utf8mb4

4. 字符集转换:

   CONVERT(column_name USING utf8mb4)
   

通过合理设置字符集和排序规则，可以确保MySQL正确处理多语言文本数据，并满足应用的排序和比较需求。