MySQL

发布日期: 2024-07-25

作者: 小乐

文章字数: 2.1k

阅读时长: 8 分

阅读次数:

MySQL字符集与排序规则

MySQL 是业务系统中最常见的数据库，但用好它并不容易。索引设计、SQL 优化、事务处理都是日常开发中需要关注的点。本文从实际场景出发，讲常见问题和解决思路。

一、字符集基础

1.1 什么是字符集

字符集（Character Set）是字符到二进制编码的映射：

字符 'A' → ASCII → 01000001 (0x41)
字符 '中' → UTF-8 → 11100100 10111000 10101101 (0xE4B8AD)

1.2 MySQL支持的字符集

字符集	每个字符最大字节	支持范围	说明
latin1	1	西欧语言	MySQL 5.x默认
gbk	2	中文简体	兼容GB2312
utf8	3	BMP平面	MySQL的utf8实际最多3字节
utf8mb4	4	全Unicode	真正的UTF-8，推荐
utf16	2或4	全Unicode	定长或变长

1.3 UTF-8 vs UTF-8MB4

MySQL的utf8是阉割版的UTF-8，最多使用3字节：

UTF-8编码规则：
- 1字节：0xxxxxxx（ASCII）
- 2字节：110xxxxx 10xxxxxx
- 3字节：1110xxxx 10xxxxxx 10xxxxxx
- 4字节：11110xxx 10xxxxxx 10xxxxxx 10xxxxxx

MySQL utf8：只支持1-3字节（最大0xFFFF）
MySQL utf8mb4：支持1-4字节（完整Unicode）

4字节字符示例：emoji、一些生僻汉字、音乐符号

-- utf8存储emoji会报错
INSERT INTO test_utf8 (content) VALUES ('Hello 😀');
-- ERROR 1366: Incorrect string value

-- utf8mb4正常存储
INSERT INTO test_utf8mb4 (content) VALUES ('Hello 😀');
-- 成功

二、排序规则（Collation）

2.1 什么是排序规则

排序规则定义字符的比较和排序规则：

-- 不同排序规则，'a'和'A'的比较结果不同
SELECT 'a' = 'A' COLLATE utf8mb4_general_ci;  -- 1（相等，不区分大小写）
SELECT 'a' = 'A' COLLATE utf8mb4_bin;         -- 0（不相等，二进制比较）

2.2 排序规则命名规则

utf8mb4_0900_ai_ci
│      │    │ │ │
│      │    │ │ └── ci = case insensitive（不区分大小写）
│      │    │ └──── ai = accent insensitive（不区分重音）
│      │    └────── 0900 = Unicode版本9.0
│      └─────────── utf8mb4 = 字符集

常见后缀：
| 后缀 | 含义 |
|——|——|
| _ci | Case Insensitive，不区分大小写 |
| _cs | Case Sensitive，区分大小写 |
| _bin | Binary，二进制比较 |
| _ai | Accent Insensitive，不区分重音 |
| _as | Accent Sensitive，区分重音 |

2.3 常用排序规则对比

排序规则	‘A’=’a’	‘a’=’á’	性能	说明
utf8mb4_general_ci	是	是	快	简单规则，可能不准确
utf8mb4_unicode_ci	是	是	中	遵循Unicode标准
utf8mb4_0900_ai_ci	是	是	中	MySQL 8.0默认，Unicode 9.0
utf8mb4_bin	否	否	最快	二进制，区分大小写

三、MySQL字符集层级

3.1 六级字符集设置

服务器级（server）
    │
    ├── 数据库级（database）
    │       │
    │       ├── 表级（table）
    │       │       │
    │       │       ├── 列级（column）
    │       │       │       │
    │       │       │       ├── 客户端连接（connection）
    │       │       │       │       │
    │       │       │       │       └── 查询结果（results）

3.2 各级别查看和设置

-- 服务器级
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

-- 数据库级
SHOW CREATE DATABASE mydb;
-- 或
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'mydb';

-- 表级
SHOW CREATE TABLE mytable;

-- 列级
SHOW FULL COLUMNS FROM mytable;

3.3 连接字符集

-- 查看连接相关字符集
SHOW VARIABLES LIKE 'character_set_%';

-- 典型输出：
-- character_set_client     : 客户端发送的SQL编码
-- character_set_connection : 服务器接收后的转换编码
-- character_set_results    : 返回给客户端的编码
-- character_set_database   : 当前数据库编码
-- character_set_server     : 服务器默认编码
-- character_set_system     : 系统元数据编码（固定utf8）

连接字符集工作流程：

客户端（UTF-8）
    │
    │ 发送SQL（UTF-8）
    ▼
character_set_client = utf8mb4
    │
    │ 转换为connection编码
    ▼
character_set_connection = utf8mb4
    │
    │ 执行查询
    ▼
character_set_results = utf8mb4
    │
    │ 转换为results编码
    ▼
客户端接收（UTF-8）

3.4 统一设置字符集

[mysqld]
# 服务器默认字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 客户端连接字符集
init_connect = 'SET NAMES utf8mb4'

# 跳过字符集校验（避免客户端指定字符集）
# skip-character-set-client-handshake

或在连接时指定：

-- JDBC连接串
jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8mb4

-- 连接后执行
SET NAMES utf8mb4;
-- 等效于：
SET character_set_client = utf8mb4;
SET character_set_results = utf8mb4;
SET character_set_connection = utf8mb4;

四、字符集转换问题

4.1 转换原理

当字符集不同时，MySQL会进行转换：

客户端(UTF-8) → 服务端(GBK存储)
    ↓
UTF-8字符 → 解码为Unicode → 编码为GBK → 存储

4.2 常见乱码问题

问题一：乱码存储

现象：存储"中文"，查询显示"??"或乱码

原因：客户端编码和connection编码不一致

解决：
SET NAMES utf8mb4;
-- 或JDBC连接加 ?characterEncoding=utf8mb4

问题二：截断问题

-- VARCHAR(10)在utf8mb4下最多存10个字符
-- 但某些字符（如emoji）是4字节

-- 存储5个emoji：
INSERT INTO test (content) VALUES ('😀😁😂🤣😃');
-- 如果字段是VARCHAR(4)，会报错：Data too long

问题三：字符集转换导致索引失效

-- 表是utf8mb4，传入utf8
SELECT * FROM user WHERE name = '张三';
-- name列可能无法使用索引（发生隐式转换）

-- 解决：确保连接字符集和表字符集一致
SET NAMES utf8mb4;

4.3 字符集转换函数

-- 查看字符串的十六进制
SELECT HEX('中');           -- E4B8AD（UTF-8）
SELECT HEX(CONVERT('中' USING gbk));  -- D6D0（GBK）

-- 转换编码
SELECT CONVERT('中' USING utf8mb4);
SELECT _utf8mb4 '中';       -- 指定字符串字符集

-- 检查字段实际存储的字节
SELECT LENGTH(content), CHAR_LENGTH(content) FROM test;
-- LENGTH：字节数
-- CHAR_LENGTH：字符数

五、生产环境配置

5.1 推荐配置

[mysqld]
# 使用utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_0900_ai_ci

# 连接设置
init_connect = 'SET NAMES utf8mb4'

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

5.2 数据库和表创建

-- 创建数据库
CREATE DATABASE mydb
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

-- 创建表
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
    -- 需要区分大小写的字段
    code VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

5.3 从UTF-8升级到UTF-8MB4

-- 步骤1：修改数据库
ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 步骤2：修改表
ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 步骤3：修改连接（JDBC）
-- jdbc:mysql://host:3306/mydb?useUnicode=true&characterEncoding=utf8mb4

-- 步骤4：修改字段长度（如果有索引长度限制）
-- utf8的VARCHAR(255)是255*3=765字节
-- utf8mb4的VARCHAR(255)是255*4=1020字节
-- InnoDB索引最大767字节（5.6）或3072字节（5.7+）

注意：

CONVERT TO会锁表，大表需要在低峰期执行

也可以只修改特定字段：

ALTER TABLE user MODIFY name VARCHAR(50) CHARACTER SET utf8mb4;

六、排序规则对查询的影响

6.1 大小写敏感查询

-- 不区分大小写的表
SELECT * FROM user WHERE name = 'alice';
-- 能查到 name='Alice' 的记录

-- 需要区分大小写时
SELECT * FROM user WHERE name = 'alice' COLLATE utf8mb4_bin;
-- 只查到 name='alice' 的记录

6.2 排序影响

-- 不区分大小写排序
SELECT name FROM user ORDER BY name;
-- Alice, bob, Charlie

-- 区分大小写排序
SELECT name FROM user ORDER BY name COLLATE utf8mb4_bin;
-- Alice, Charlie, bob（大写在前）

6.3 索引使用

-- 表使用utf8mb4_general_ci
-- 查询使用utf8mb4_bin
SELECT * FROM user WHERE name = 'Alice' COLLATE utf8mb4_bin;
-- 可能无法使用索引！（排序规则不一致）

-- 解决：确保查询和字段排序规则一致
SELECT * FROM user WHERE name = 'Alice';

七、emoji存储实践

7.1 确保完整支持

-- 1. 数据库字符集
SHOW CREATE DATABASE mydb;
-- 确保是utf8mb4

-- 2. 表字符集
SHOW CREATE TABLE user;
-- 确保是utf8mb4

-- 3. 连接字符集
SHOW VARIABLES LIKE 'character_set_%';
-- 确保client、connection、results都是utf8mb4

-- 4. JDBC连接
-- jdbc:mysql://host:3306/mydb?characterEncoding=utf8mb4

7.2 存储emoji示例

CREATE TABLE comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    content VARCHAR(500) CHARACTER SET utf8mb4,
    create_time DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO comments (user_id, content) VALUES 
(1, 'Hello 😀 世界 🌍');

SELECT * FROM comments;
-- 正常显示emoji

八、总结

配置项	推荐值	说明
字符集	utf8mb4	完整支持Unicode，包括emoji
排序规则	utf8mb4_0900_ai_ci	MySQL 8.0默认，准确性好
索引排序	utf8mb4_bin	需要区分大小写时使用

问题	原因	解决
乱码	连接字符集不一致	SET NAMES utf8mb4
emoji报错	使用utf8而非utf8mb4	升级到utf8mb4
索引失效	排序规则不一致	统一排序规则
截断	字符长度计算错误	使用CHAR_LENGTH

字符集管理的核心要点：

统一使用utf8mb4字符集
确保连接字符集和存储字符集一致
选择合适的排序规则
升级旧系统时注意索引长度限制

核心要点

索引设计的原则：最左前缀原则、避免索引列参与计算、选择合适的索引类型
SQL 优化的方法：使用 EXPLAIN 分析执行计划、避免 SELECT *、优化 JOIN
事务隔离级别的选择：根据业务需求选择合适的级别
常见的锁问题：行锁、表锁、死锁的处理

总结

MySQL 优化是一个持续的过程，需要结合业务特点和数据量来调整。在实际项目中，定期分析慢查询日志、优化索引、调整配置参数，都能提升数据库性能。

转载规则

《MySQL字符集与排序规则》由小乐采用知识共享署名 4.0 国际许可协议进行许可。

文件上传服务如何保证稳定

详解文件上传服务如何保证稳定的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-25 系统设计

架构系统设计

订单超时取消如何实现

详解订单超时取消如何实现的核心概念与实践方法，结合真实开发场景说明使用注意事项。

2024-07-24 系统设计