MySQL字符集与排序规则：新手必知的基础配置

一、什么是字符集和排序规则？¶

字符集（Character Set）可以理解为MySQL存储字符时使用的“编码规则”，比如我们熟悉的utf8mb4、latin1等，它们规定了MySQL如何将文字（如中文、英文、emoji）转换为二进制数据存储。
排序规则（Collation）则是字符集的“排序规则”，决定了文字的比较和排序方式，比如utf8mb4_general_ci表示按字母顺序不区分大小写排序，utf8mb4_bin则按二进制值严格区分大小写。

二、为什么要配置字符集和排序规则？¶

如果字符集或排序规则配置不当，最直接的问题是乱码或排序错误：
- 乱码：比如用latin1字符集存储中文，MySQL会将中文转为无法识别的二进制，读取时显示乱码。
- 排序错误：比如排序规则选错，“张三”可能排在“李四”前面（如果排序规则按拼音首字母排序，需确认顺序是否符合预期）。
- 兼容性问题：旧版utf8字符集仅支持部分Unicode字符（不支持emoji和某些罕见字符），而utf8mb4才是完整的Unicode支持，推荐优先使用。

三、MySQL字符集与排序规则的配置层级¶

MySQL的字符集和排序规则有多个生效层级，优先级从高到低为：列级 > 表级 > 数据库级 > 服务器级。即：如果列、表、数据库中均未指定，才会使用服务器默认配置。
- 服务器级：全局生效，可通过配置文件（如my.cnf）修改，控制数据库服务器的默认字符集。
- 数据库级：创建数据库时指定，对该数据库下所有表生效（除非表或列单独指定）。
- 表级：创建表时指定，对该表下所有列生效（除非列单独指定）。
- 列级：创建表时对特定列单独指定，优先级最高，覆盖表级配置。

四、如何查看当前配置？¶

新手需先学会查看当前配置，避免配置错误。以下是常用命令：
1. 查看服务器级字符集：

   SHOW VARIABLES LIKE 'character_set_%';

常见结果：character_set_server（服务器字符集，默认utf8mb4）、character_set_database（数据库默认字符集）等。

查看服务器级排序规则：

   SHOW VARIABLES LIKE 'collation_%';

常见结果：collation_server（服务器默认排序规则，如utf8mb4_general_ci）。

查看数据库字符集：

   SHOW CREATE DATABASE your_database;

结果会显示数据库创建时指定的字符集和排序规则。

查看表字符集：

   SHOW TABLE STATUS LIKE 'your_table';

结果中的Collation列显示表的排序规则，Row_format等信息可能需结合其他命令。

查看列字符集：

   SHOW COLUMNS FROM your_table;

结果中Character_set列显示列的字符集（若未单独设置，继承表级配置）。

五、新手必学的配置方法¶

1. 推荐使用`utf8mb4`字符集¶

utf8mb4是MySQL支持完整Unicode字符的编码（包括中文、英文、emoji、生僻字等），而旧版utf8（实际为utf8mb3）仅支持3字节字符，无法存储emoji和部分特殊字符。
- 服务器级配置（修改配置文件）：
在my.cnf（Linux）或my.ini（Windows）中添加以下内容：

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

保存后重启MySQL服务（systemctl restart mysql或net stop mysql && net start mysql）。

2. 数据库级配置¶

创建数据库时指定字符集（推荐）：

CREATE DATABASE your_database 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

如果已创建数据库，可修改数据库字符集：

ALTER DATABASE your_database 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

3. 表级配置¶

创建表时指定字符集（若需覆盖服务器默认）：

CREATE TABLE your_table (
  id INT,
  name VARCHAR(50)
) ENGINE=InnoDB 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;

若需区分大小写，可使用utf8mb4_bin排序规则（严格按二进制值比较，不依赖语言规则）：

COLLATE utf8mb4_bin

4. 列级配置（特殊需求）¶

若某列需单独设置字符集（如存储英文和中文混合，需兼容不同排序）：

CREATE TABLE your_table (
  id INT,
  english_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_english_ci,
  chinese_name VARCHAR(50)
) ENGINE=InnoDB;

（注：列级配置仅适用于需覆盖表级的场景，优先推荐表级统一配置）

六、常见问题与解决¶

乱码问题：
- 原因：列/表/数据库字符集与插入数据的编码不匹配（如用latin1存储中文）。
- 解决：检查数据来源（如程序代码）是否使用utf8mb4编码，确保所有层级字符集统一为utf8mb4。
排序规则选错导致中文排序错误：
- 场景：中文按拼音排序时，utf8mb4_general_ci可能不符合预期（如“李”和“王”的顺序）。
- 解决：若需中文拼音排序，可选utf8mb4_unicode_ci（更精确，支持德语、法语等复杂排序），但性能略低于general_ci。
emoji无法显示：
- 原因：使用旧版utf8（utf8mb3）字符集。
- 解决：修改为utf8mb4字符集即可（utf8mb4支持所有Unicode字符，包括emoji）。

七、总结与最佳实践¶

优先使用utf8mb4字符集，避免utf8（旧版）。
排序规则选utf8mb4_general_ci或utf8mb4_unicode_ci：前者性能好，后者排序更精确（需根据需求选择）。
避免列级单独配置，除非特殊需求，表级统一配置即可。
定期检查配置：通过SHOW VARIABLES和SHOW CREATE DATABASE/ TABLE确认字符集是否生效。

掌握字符集与排序规则的配置，能从根本上避免数据乱码、排序错误等问题，是MySQL使用的基础技能。记住：统一的字符集和排序规则是数据一致性的关键！