MySQL字符集Character和排序规则Collations的查看和修改

原创已于 2022-04-21 18:11:48 修改 · 4.1k 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#java #后端 #运维 #mysql

于 2022-04-21 18:03:14 首次发布

MySQL 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了MySQL中字符集和排序规则的概念及其重要性，包括查看和修改不同级别的默认字符集与排序规则的方法。从服务器、数据库、表到列的设置步骤逐一解析，并提醒了字符集不一致可能导致的问题以及如何通过JDBC连接设置字符集。

文章目录

1.概念

字符集（Character）是一组符合和编码。

排序规则（Collations）是一组用于比较字符集中字符的规则。

MySQL使用字符集存储字符串，使用排序规则比较字符串。字符集和排序规则可以指定到列上，同一个表的各个列可以是不同的字符集和排序规则。

字符集和排序规则不一致会导致join查询的时候on失败。同样的字符集和排序规则也会提升join的效率。

2.操作起来了

字符集和排序规则默认有四个级别：服务器、数据库、表、列。

字符集会影响存储，还会影响客户端程序和MySQL服务器（实例）之间的通信。

2.1.MySQL支持哪些字符集和排序规则

查看支持的字符集

show character set ;

查看支持的排序规则

show collation ;

2.2.默认字符集和排序规则

2.2.1.查看

查看各项字符集，表和列的字符集需要在表设置里面查看

show variables like '%character%';

Variable_name	Value	备注
character_set_client	utf8mb4	默认客户端的字符集
character_set_connection	utf8mb4	默认连接的字符集
character_set_database	utf8mb4	默认数据库的字符集
character_set_filesystem	binary	默认文件系统的字符集
character_set_results	utf8mb4	默认返回结果的字符集
character_set_server	utf8mb4	默认服务器（MySQL实例）的字符集
character_set_system	utf8mb3	默认系统（Linux、WinServer）的字符集
character_sets_dir	/usr/share/mysql-8.0/charsets/

2.2.2.修改

2.2.2.1.设置客户端、连接、返回结果的字符集

MySQL的utf8mb4才是真正的utf8。这是一个历史原因，需要支持中文的话，选utf8mb4。

SET NAMES 'utf8mb4';

这个命令影响的是character_set_client、character_set_connection、character_set_results

2.2.2.2.设置各项字符集

set @@[Variable_name]=‘字符集’；即可

举个栗子：

set @@character_set_server='utf8mb4';

表和列的字符集需要在表配置里面修改。

2.3.库database的字符集和排序规则

2.3.1.查看

show create database 库名;

show create database school;

结果

Database	Create Database
school	CREATE DATABASE `school` /!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION=‘N’ /

CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci，字符集为utf8mb4，排序规则为utf8mb4_0900_ai_ci

2.3.2.修改

alter database 库名 character set 字符集 collate 排序规则;

alter database school character set `utf8mb4` collate `utf8mb4_general_ci`;

2.4.具体表table的字符集和排序规则

2.4.1.查看

show create table 库名.表名;

show create table school.student;
# 或者
show create table student;

结果

Table	Create Table
student	CREATE TABLE `student` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL COMMENT ‘名字’, `age` int unsigned DEFAULT NULL COMMENT ‘年龄’, `address` varchar(100) DEFAULT NULL COMMENT ‘地址’, `class_id` int DEFAULT NULL COMMENT ‘班级id’, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci，字符集为utf8mb4，排序规则为utf8mb4_0900_ai_ci

2.4.2.修改

alter table 库名.表名 character set = 字符集 collate = 排序规则;

alter table school.student character set = `utf8mb4` collate = `utf8mb4_general_ci`;

2.5.具体字段的字符集和排序规则

2.5.1.查看

show full columns from 库名.表名;

show full columns from school.student;
# 或者
show full columns from student;

结果

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
id	int	NULL	NO	PRI	NULL	auto_increment	select,insert,update,references
name	varchar(50)	utf8mb4_0900_ai_ci	YES		NULL		select,insert,update,references	名字
age	int unsigned	NULL	YES		NULL		select,insert,update,references	年龄
address	varchar(100)	utf8mb4_0900_ai_ci	YES		NULL		select,insert,update,references	地址
class_id	int	NULL	YES		NULL		select,insert,update,references	班级id

2.5.2.修改

修改字段的SQL没固定模式

ALTER TABLE `school`.`student` MODIFY COLUMN `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '名字' AFTER `id`;

注意：如果使用ALTER TABLE将列从一个字符集转换为另一个字符集，MySQL会尝试映射数据值，但如果字符集不兼容，则可能会丢失数据。

2.6.JDBC连接的字符集

jdbc:mysql://localhost:3306/school?characterEncoding=utf8&useLegacyDatetimeCode=false&serverTimezone=UTC

JDBC连接