MySQL角色集和碰撞的初学者指南

什么是字符集和排序规则

在MySQL中，字符集(Character Set)和排序规则(Collation)是两个密切相关的重要概念：

• 字符集：定义了一组字符及其编码方式(如UTF-8、Latin1等)
• 排序规则：定义了字符的比较和排序规则(如是否区分大小写、重音符号等)

常见字符集

MySQL支持多种字符集，最常用的包括：

1. utf8mb4：完整的UTF-8实现(推荐使用，支持emoji表情)
2. utf8：MySQL中的"不完整"UTF-8实现(最大3字节)
3. latin1：西欧字符集
4. ascii：基本的ASCII字符集

常见排序规则

排序规则通常以字符集名开头，后跟排序规则特性：

• _ci：不区分大小写(Case Insensitive)
• _cs：区分大小写(Case Sensitive)
• _bin：二进制比较

例如： - utf8mb4_general_ci：通用排序规则，不区分大小写 - utf8mb4_unicode_ci：基于Unicode标准的排序规则 - utf8mb4_bin：二进制比较

字符集和排序规则的作用范围

MySQL中可以在多个级别设置字符集和排序规则：

1. 服务器级别：默认的服务器设置
2. 数据库级别：创建数据库时指定
3. 表级别：创建表时指定
4. 列级别：创建列时指定
5. 连接级别：客户端连接时指定

如何设置字符集和排序规则

创建数据库时指定

CREATE DATABASE mydb 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

创建表时指定

CREATE TABLE mytable (
    id INT,
    name VARCHAR(100)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

创建列时指定

CREATE TABLE mytable (
    id INT,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
);

修改现有表

ALTER TABLE mytable 
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

查看当前设置

查看支持的字符集

SHOW CHARACTER SET;

查看支持的排序规则

SHOW COLLATION;

查看数据库的字符集

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM INFORMATION_SCHEMA.SCHEMATA 
WHERE SCHEMA_NAME = 'mydb';

查看表的字符集

SELECT TABLE_COLLATION 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'mytable';

字符集冲突问题

当不同字符集的数据交互时可能出现问题，常见场景：

1. 客户端与服务器字符集不一致：可能导致数据存储或显示乱码
2. 表间连接时字符集不一致：可能导致索引失效或查询性能下降
3. 排序规则不一致：可能导致比较操作结果不符合预期

解决方案

1. 统一字符集：尽量在整个应用中使用相同的字符集(推荐utf8mb4)
2. 显式转换：使用CONVERT()或CAST()函数转换字符集
3. 连接时指定字符集：在连接字符串中指定字符集

最佳实践

1. 使用utf8mb4：这是最全面的字符集，支持所有Unicode字符
2. 保持一致性：整个应用使用相同的字符集和排序规则
3. 连接时指定字符集：确保客户端和服务器使用相同的字符集
4. 考虑排序规则的影响：特别是对索引和查询性能的影响

示例问题解决

问题：存储emoji表情出现乱码

解决方案：将字符集改为utf8mb4

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

问题：区分大小写的查询

解决方案：使用_bin排序规则或BINARY关键字

SELECT * FROM mytable WHERE name COLLATE utf8mb4_bin = 'John';
-- 或
SELECT * FROM mytable WHERE BINARY name = 'John';

通过理解MySQL的字符集和排序规则，您可以避免许多数据存储和查询中的常见问题，确保应用程序正确处理多语言数据。