MySQL 字符集：MySQL 的字符集继承规则

0 当前数据库字符集、校对规则参数

0.1. 字符集参数

mysql> SHOW VARIABLES LIKE '%char%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | utf8                                  |
| character_set_connection | utf8                                  |
| character_set_database   | utf8                                  |
| character_set_filesystem | binary                                |
| character_set_results    | utf8                                  |
| character_set_server     | utf8                                  |
| character_set_system     | utf8                                  |
| character_sets_dir       | /u01/mysql57_20250430/share/charsets/ |
+--------------------------+---------------------------------------+

0.2. 校对规则参数

mysql> SHOW VARIABLES LIKE '%collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

1 字符集/校对规则继承顺序（建表时未指定）

继承层级实际使用的变量您的当前配置值说明
1. 列级定义 列定义中显式指定 - 如 VARCHAR(100) CHARACTER SET ... COLLATE ...
2. 表级定义 表定义中的 DEFAULT CHARSET/COLLATE - 如 CREATE TABLE ... DEFAULT CHARSET=... COLLATE=...
3. 数据库级定义 character_set_database
collation_database		 utf8
utf8_general_ci		 您的表将默认继承此配置
4. 服务器级定义 character_set_server
collation_server		 utf8
utf8_general_ci		 仅当数据库未指定时生效（您的数据库已指定）

📌 结论：
在您的环境中，建表时若未显式指定字符集/校对规则：

  • 字符集将继承 character_set_database = utf8

  • 校对规则将继承 collation_database = utf8_general_ci

2 关键系统变量分析

2.1. 客户端相关变量（不影响表存储）

变量名您的配置值作用
character_set_client utf8 客户端发送语句的编码
character_set_connection utf8 服务器处理语句时转换的编码
character_set_results utf8 服务器返回结果的编码
collation_connection utf8_general_ci 连接层校对规则

⚠️ 注意：这些变量只影响 客户端与服务器的交互，不影响表本身的存储结构。

2.2. 存储相关变量（直接影响表默认值）

变量名您的配置值作用
character_set_database utf8 当前数据库的默认字符集（建表时继承）
collation_database utf8_general_ci 当前数据库的默认校对规则（建表时继承）
character_set_server utf8 服务器的全局默认字符集（仅当数据库未指定时生效）
collation_server utf8_general_ci 服务器的全局默认校对规则（仅当数据库未指定时生效）

🔍 您的现状：
所有存储相关变量均为 utf8 和 utf8_general_ci，这意味着：

  • 新建表默认使用 过时的 utf8（实为 utf8mb3，不支持 Emoji 和部分 Unicode 字符）

  • 校对规则为 不精准的 utf8_general_ci

3 utf8mb4 校对规则对比（推荐升级方案）

以下是 utf8mb4 字符集的常用校对规则特点，强烈建议升级以支持完整 Unicode：

校对规则大小写敏感重音敏感Unicode 标准准确性性能存储优化适用场景MySQL 版本
utf8mb4_general_ci ❌ ❌ 自定义简化 ⭐ ⚡️⚡️⚡️ ❌ 旧系统兼容/高性能写入 5.5+
utf8mb4_unicode_ci ❌ ❌ Unicode 4.0 ⭐⭐ ⚡️⚡️ ❌ 基础多语言支持 5.5+
utf8mb4_unicode_520_ci ❌ ❌ Unicode 5.2 ⭐⭐⭐ ⚡️⚡️ ❌ 较新Unicode支持 5.6+
utf8mb4_0900_ai_ci ❌ ❌ Unicode 9.0 ⭐⭐⭐⭐ ⚡️⚡️⚡️ ✅ 现代系统默认推荐 8.0+
utf8mb4_0900_as_cs ✅ ✅ Unicode 9.0 ⭐⭐⭐⭐ ⚡️⚡️ ✅ 精确匹配(用户名等) 8.0+
utf8mb4_bin ✅ ✅ 无(二进制) ⭐⭐⭐⭐⭐ ⚡️ ❌ 密码/严格标识符 所有版本

📊 关键维度说明：

  1. 大小写敏感：✅=区分大小写 (如 A≠a)，❌=不区分

  2. 重音敏感：✅=区分重音 (如 é≠e)，❌=不区分

  3. 准确性：基于语言排序的正确性（⭐越多越符合人类语言习惯）

  4. 性能：⚡️越多表示排序/比较速度越快

  5. 存储优化：✅=使用更紧凑的权重编码（减少存储空间）

📘 关键后缀解释：

    • _ci：不区分大小写（Case Insensitive）

    • _cs：区分大小写（Case Sensitive）

    • _ai：不区分重音（Accent Insensitive）

    • _as：区分重音（Accent Sensitive）

    • _bin：二进制比较

3.1. utf8mb4_bin特点

特性说明
比较机制 直接比较字符的二进制编码值（非语言规则）
大小写处理 严格区分（A=0x41, a=0x61 → 'A' ≠ 'a'）
重音处理 严格区分（é=0xC3A9, e=0x65 → 'café' ≠ 'cafe'）
特殊字符处理 区分全角/半角（Ａ(全角)=0xEFBCA1 ≠ A(半角)=0x41）
Emoji 处理 严格区分（👍=0xF09F918D ≠ 👎=0xF09F918E）

4 升级建议（解决当前问题）

4.1. 立即修复字符集问题

您的当前配置 utf8 (utf8mb3) 已过时（MySQL 8.0 默认改为 utf8mb4）。需执行：

-- 修改数据库默认配置（影响后续新建表）
ALTER DATABASE `您的数据库名` 
  CHARACTER SET = utf8mb4 
  COLLATE = utf8mb4_0900_ai_ci;

-- 修改现有表（示例）
ALTER TABLE `您的表名` 
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_0900_ai_ci;

4.2. 永久修改服务器配置

在 MySQL 配置文件 (my.cnf/my.ini) 中添加：

[mysqld]
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

重启 MySQL 服务使配置生效。

4.3. 校对规则选择建议

场景推荐选择
新项目 (MySQL 8.0+) utf8mb4_0900_ai_ci
多语言精准排序 (MySQL 5.7-) utf8mb4_unicode_520_ci
区分大小写（如用户名） utf8mb4_0900_as_cs
高速写入（兼容旧系统） utf8mb4_general_ci

💡 性能说明：

  • MySQL 8.0 对 utf8mb4_0900_ai_ci 做了深度优化，性能已超越旧版 utf8mb4_general_ci

  • 升级后可完整支持 Emoji 和所有 Unicode 字符（如中文生僻字）

5 修改校验规则

5.1 修改DB校验规则

alter database base_archives COLLATE utf8mb4_general_ci;

5.2 修改表的校验规则

1）该操作仅修改表的默认设置，不会自动修改已有字段的字符集和校对规则

该操作仅修改表的默认设置，不会自动修改已有字段的字符集和校对规则。
ALTER TABLE user
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

说明：

  • 该操作仅修改表的默认设置，不会自动修改已有字段的字符集和校对规则。
  • 如果字段未显式指定字符集，后续新增字段会继承表的默认设置。

2）该操作仅修改表的默认设置，不会自动修改已有字段的字符集和校对规则

该操作会尝试将所有字段的字符集和校对规则转换为目标值
ALTER TABLE user
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

说明：

  • 该操作会尝试将所有字段的字符集和校对规则转换为目标值。
  • 如果字段的原始字符集与目标字符集不兼容（如 latin1 转 utf8mb4），可能会导致数据截断或乱码，操作前务必备份数据。

5.3 修改字段检验规则

ALTER TABLE user
  MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
说明：

如果字段类型不是 CHAR、VARCHAR、TEXT 等字符类型，不需要指定 CHARACTER SET。
修改字段的字符集和校对规则时，MySQL 会尝试转换现有数据，可能导致数据丢失或乱码（尤其是从窄字符集转宽字符集时）。

5.4  修改操作分类及锁表情况对比

在 MySQL 5.7 且支持 Online DDL 的情况下，修改校验规则（字符集和校对规则）时，锁表行为和锁类型取决于具体的操作类型。以下是详细的对比分析：

操作类型是否锁表锁类型说明
修改表的默认字符集和校对规则 否 无锁 使用 ALTER TABLE ... DEFAULT CHARACTER SET ... COLLATE ... 仅修改表的默认设置，不修改已有字段，因此无需锁表。
修改表及字段的字符集和校对规则 可能 MDL 写锁 + 表级锁 使用 ALTER TABLE ... CONVERT TO CHARACTER SET ... COLLATE ...，会修改所有字段的字符集和校对规则，并触发数据转换。此操作需要 MDL 写锁（阻塞所有 DML/DQL）和 表级锁（InnoDB 的行锁升级为表锁）。
修改单个字段的字符集和校对规则 可能 MDL 写锁 + 行锁/表锁 使用 ALTER TABLE ... MODIFY COLUMN ...，若字段类型为字符类型（如 VARCHAR），且操作支持 Online DDL（如 MySQL 5.7 及以上），可能无需锁表；否则会触发 MDL 写锁 和 表级锁。
修改数据库的默认字符集和校对规则 否 无锁 使用 ALTER DATABASE ... COLLATE ...，仅修改数据库的默认设置，不影响已有表和字段。

 

 

 

 posted on 2025-07-17 09:42  xibuhaohao  阅读(13)  评论(0)    收藏  举报