一、字符集和排序规则概述
MySQL8支持多种字符集，每个字符集都有一套相应的排序规则，字符集决定如何存储数据，而排序规则决定了如何比较和排序规则。
1.1、主要字符集
1.2、排序规则示例
-- utf8mb4 字符集的常见排序规则
utf8mb4_general_ci -- 不区分大小写的通用排序
utf8mb4_unicode_ci -- 基于Unicode的排序规则
utf8mb4_bin -- 二进制排序，区分大小写
utf8mb0_0900_ai_ci -- MySQL 8.0+ 默认排序规则
二、字符集设置
2.1、服务器级别设置
-- 查看当前服务器字符集 SHOW VARIABLES LIKE 'character_set_server'; -- 设置服务器默认字符集 SET GLOBAL character_set_server = utf8mb4; -- 修改配置文件 /etc/mysql/my.cnf [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_0900_ai_ci
2.2、数据库级别设置
-- 创建数据库时指定字符集 CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 修改现有数据库字符集 ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 查看数据库字符集 SHOW VARIABLES LIKE 'character_set_database';
2.3、表级别设置
-- 创建表时指定字符集 CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 修改表字符集 ALTER TABLE users DEFAULT CHARACTER SET utf8mb4; -- 查看表字符集 SHOW TABLE STATUS FROM mydb LIKE 'users';
2.4、列级别设置
-- 创建表时为列指定字符集 CREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, description TEXT CHARACTER SET utf8mb4, price DECIMAL(10,2) ); -- 修改列字符集 ALTER TABLE products MODIFY COLUMN name VARCHAR(100) CHARACTER SET utf8mb4;
三、实际应用
1、完整的数据库创建
-- 创建支持中文和emoji的数据库
CREATE DATABASE company_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_0900_ai_ci;
-- 使用数据库
USE company_db;
-- 创建用户表
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
email VARCHAR(100) NOT NULL,
real_name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
phone VARCHAR(20) NOT NULL,
bio TEXT CHARACTER SET utf8mb4,
avatar VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- 创建产品表
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
description TEXT CHARACTER SET utf8mb4,
price DECIMAL(10,2) NOT NULL,
status ENUM('在售', '缺货', '下架') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '在售',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;2、字符集转换
-- 检查表的字符集 SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database'; -- 将表从latin1转换为utf8mb4 ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4; -- 将整个数据库转换为utf8mb4 ALTER DATABASE your_database CHARACTER SET utf8mb4;
3、字符集兼容性检查
-- 检查表字符集设置 SELECT TABLE_NAME as '表名', CHARACTER_SET_NAME as '字符集', TABLE_COLLATION as '排序规则' FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' ORDER BY TABLE_NAME; -- 检查列字符集设置 SELECT TABLE_NAME as '表名', COLUMN_NAME as '列名', CHARACTER_SET_NAME as '字符集', COLLATION_NAME as '排序规则' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database' AND (CHARACTER_SET_NAME IS NOT NULL OR COLLATION_NAME IS NOT NULL) ORDER BY TABLE_NAME, COLUMN_NAME;
三、字符集问题和解决方案
3.1、常见问题
乱码问题
原因：客户端字符集与服务器字符集不匹配
解决：统一使用utf8mb4
emoji显示异常
原因：使用utf8而非utf8mb4
解决：改为utf8mb4字符集
排序错误
原因：使用了不合适的排序规则
解决：使用unicode_ci或自定义排序规则
3.2、环境配置
-- 查看当前连接字符集 SHOW VARIABLES LIKE 'character_set_connection'; -- 设置连接字符集 SET NAMES utf8mb4; -- 检查所有字符集相关变量 SHOW VARIABLES LIKE 'character_set_%';
3.3、配置文件示例
# my.cnf 或 my.ini 配置 [mysqld] # 服务器字符集 character-set-server = utf8mb4 collation-server = utf8mb4_0900_ai_ci [client] # 客户端字符集 default-character-set = utf8mb4 [mysql] # mysql客户端字符集 default-character-set = utf8mb4
四、性能考虑
存储空间
utf8mb4比utf8占用更多空间
对于纯英文内容，utf8mb4和utf8存储相同
索引大小
字符集影响索引大小
utf8mb4索引比utf8索引大33%
查询性能
合适的排序规则影响查询性能
unicode_ci比general_ci更准确但稍慢
五、最佳实践
统一使用utf8mb4
支持所有Unicode字符
避免乱码和emoji问题
合理的排序规则
使用utf8mb4_0900_ai_ci（MySQL 8.0+默认）
特殊需求时选择其他排序规则
层级设置
服务器：utf8mb4
数据库：utf8mb4
表：utf8mb4
列：根据需求设置
环境一致性
确保客户端和服务器字符集一致
应用连接字符串中明确指定字符集
数据迁移
从latin1或gbk迁移时注意数据兼容性
建议使用专业的迁移工具