6.8.7 字符集与排序规则配置规范

字符集与排序规则配置规范#

字符集与排序规则直接影响数据兼容性、查询性能与排序一致性。本节给出统一规范、库表字段配置、连接层设置、迁移注意事项，并提供可执行示例、排错与练习。

一、原理与作用范围（含原理草图）#

字符集配置从“服务器默认 → 数据库 → 表 → 字段 → 连接层 → SQL字面量”逐级生效，任一层混用会触发隐式转换与索引失效。

二、统一规范与选型原则#

统一使用 UTF8MB4：支持完整 Unicode，避免后期兼容问题。
排序规则优先使用通用、稳定规则：
MySQL 8.0：utf8mb4_0900_ai_ci
MySQL 5.7：utf8mb4_unicode_ci（兼容性优先）
避免混用字符集/排序规则：库、表、字段、连接层不一致会导致隐式转换与性能下降。

三、数据库与表级配置示例（含完整建库建表示例）#

1）配置服务端默认字符集（建议在新库前完成）
文件路径：/etc/my.cnf 或 /etc/mysql/my.cnf

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci
# 建议确保 InnoDB 行格式支持大索引
innodb_file_per_table = 1
innodb_default_row_format = DYNAMIC

2）建库与建表显式声明

-- 连接后验证服务端默认值
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

-- 建库
CREATE DATABASE ops
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE ops;

-- 建表：显式声明表级字符集与排序规则
CREATE TABLE user_profile (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL,
  nickname VARCHAR(64) NOT NULL,
  remark VARCHAR(255) DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_0900_ai_ci;

-- 字段级覆盖（仅在明确需求，如大小写敏感）
ALTER TABLE user_profile
  MODIFY username VARCHAR(64)
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_bin;

预期效果：
- SHOW CREATE TABLE user_profile; 中表级字符集为 utf8mb4，排序规则为 utf8mb4_0900_ai_ci。
- username 字段为大小写敏感比较。

四、连接层与会话层配置示例#

1）客户端连接字符集统一设置

-- 查看当前会话字符集
SHOW VARIABLES LIKE 'character_set%';

-- 设置本会话字符集
SET NAMES utf8mb4;

2）应用连接参数（示例：JDBC）

jdbc:mysql://db01:3306/ops?useUnicode=true&characterEncoding=utf8mb4&useSSL=false

命令解释：
- SET NAMES utf8mb4 会同步 character_set_client/connection/results。
- 连接串参数确保应用层与服务器一致，避免隐式转换。

五、排序规则选择与查询行为示例#

1）对比排序规则行为

-- 大小写不敏感
SELECT 'a' = 'A' COLLATE utf8mb4_0900_ai_ci AS ci_equal;

-- 大小写敏感
SELECT 'a' = 'A' COLLATE utf8mb4_bin AS bin_equal;

2）索引与排序规则影响（演示索引使用）

-- 只演示思路：大小写敏感字段创建索引
CREATE INDEX idx_username ON user_profile(username);

EXPLAIN SELECT * FROM user_profile WHERE username='Admin';

预期效果：
- 若字段为 utf8mb4_bin，查询大小写敏感，索引命中明确。

六、迁移与变更注意事项（含执行示例）#

1）变更评估：可能触发表重建与锁表，建议低峰执行
2）迁移前检查非法字节

-- 检查可能的非法字符（简化示例）
SELECT id, nickname
FROM user_profile
WHERE nickname REGEXP '[\\x80-\\xFF]';

3）变更字符集/排序规则（示例）

-- 数据库级变更（仅影响新建对象）
ALTER DATABASE ops
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

-- 表级变更（会重建表）
ALTER TABLE user_profile
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

预期效果：
- 表内字符串列被转换到新字符集/排序规则。
- 执行期间可能持有锁，需评估业务影响。

七、常见问题与排错清单（含命令）#

1）现象：插入表情符号报错
- 可能原因：字符集不是 UTF8MB4
- 排查：

SHOW VARIABLES LIKE 'character_set_server';
SHOW CREATE TABLE user_profile;

2）现象：查询走不了索引，性能下降
- 可能原因：查询字面量字符集与列不一致，发生隐式转换
- 排查：

EXPLAIN SELECT * FROM user_profile WHERE nickname='张三';
SHOW VARIABLES LIKE 'collation_connection';

3）现象：排序结果异常
- 可能原因：排序规则与预期不一致
- 排查：

SHOW FULL COLUMNS FROM user_profile;

八、运维检查清单#

库、表、字段字符集与排序规则一致且符合统一规范
连接层与应用连接参数已配置为 UTF8MB4
查询中无频繁隐式转换与字符集混用
索引长度与行格式满足 UTF8MB4 约束
迁移前后对比校验数据一致性与排序结果一致性

九、练习题（含明确命令）#

1）创建库 ops_lab，表 t_user，要求库/表为 UTF8MB4，username 大小写敏感。
2）插入 Admin 与 admin 两条数据，验证是否能区分。
3）将表排序规则改为 utf8mb4_0900_ai_ci，观察查询变化。

CREATE DATABASE ops_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE ops_lab;

CREATE TABLE t_user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(32)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO t_user(username) VALUES ('Admin'), ('admin');

SELECT * FROM t_user WHERE username='Admin';
ALTER TABLE t_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
SELECT * FROM t_user WHERE username='Admin';

预期结果：
- 在 utf8mb4_bin 下能区分大小写；
- 转换为 utf8mb4_0900_ai_ci 后大小写不敏感。