【MySQL】字符集与校对集详解

---

字符集与校对集

1字符集与校对集的概述

1.1字符集

1字符集的含义

字符指计算机中保存的各种文字和符号包括各种国家的文字、标点符号、图形、符号、数字等。

由于计算机采用的是二进制保存数据用户输入的字符将会按照一定的规则转化为二进制后保存这个过程就是字符编码。

将一系列的字符的编码规则组合起来就形成了字符集用于表示特定的文本信息。

2常见的字符集

在计算机的发展历史中出现了许多的字符集MySQL也提供了各种各样的字符集的支持。

常用的字符集有以下几种

字符集	单字符最大长度	支持的语言
latinl	1字节	西欧字符希腊字符等
gbk	2字节	简体和繁体中文日文韩文等
utf8	3字节	世界上大部分国家的文字
utf8mb4	4字节	支持几乎所有的语言

注意事项

1. 单字符占用的存储空间越多所支持的语言种类就越多。
2. 表中的字符集均向下兼容ASCII（美国信息交换标准代码如果输入的字符在ASCII的范围内则编码结果是相同的。
3. MySQL中的utf8编码和标准的UTF-8之间存在差异标准的UTF-8中的一个字符最多占用4个字节而utf8的最多占用3个字节。
4. utf8的单字符占用最大字节数少于UTF-8的进而导致出现UTF-8中的一些特殊字符（表情在MySQL的utf8编码中无法使用的情况
5. utf8mb4编码的引进解决了上述问题utf8mb4表示 Unicode 的所有字符包括表情符号和一些较少使用的字符。

---

3查看MySQL中的字符集

MySQL中提供了挺多的字符集的支持查看MySQL中可用字符集基本语法如下

SHOW CHARACTER SET;

操作结果如下

操作结果分析

Charset字符集名称

Description描述信息

Default collation默认校对集

Maxlen单字符的最大长度

---

1.2校对集

1校对集的含义

校对集是数据比较的规则用于为不同的字符集指定比较和排序规则。

校对集依赖字符集每个字符集有多种校对规则。

---

2校对集名称的构成

latinl字符集对应的校对集为latinl_swedish_ci

• 校对集的名称由_分隔的三部分组成的。
• 开头的latinl表示对应的字符集。
• 中间的swedish表示国家名或者使用general。
• 结尾的是cics或binci表示不区分大小写cs表示区分大小写bin表示以二进制的方式比较。

---

3校对集的查看

MySQL也提供了许多的字符集查看MySQL中可用校对集基本语法如下

SHOW COLLATION;

操作结果如下

操作结果分析

Collatioin校对集名称

Charset对应的字符集

Id校对集ID

default是否为对应字符集的默认校对集

Compiled是否已编译

Sortlen排序的内存需求量

---

2字符集和校对集的设置

MySQL 的字符集和校对规则有 4 个级别的默认设置即服务器级、数据库级、表级和字段级。

它们分别在不同的地方设置作用也不相同。

2.1MySQL环境

1当前会话使用的字符集查看

SHOW VARIABLES LIKE 'CHARACTER%';

操作结果如下

操作结果说明

1. 上述结果是显示当前会话使用的字符集在不同的客户端环境中的输出结果可能不相同。
2. 不同的客户端可以指定不同的字符集环境配置服务器会按照不同的字符集环境配置进行处理。
3. 会话是指从客户端登录服务器直到退出服务器的整个过程可以依次打开多个客户端进行操作进而产生不同的会话。
4. 如果没有指定服务器字符集MySQL 会默认使用 latin1 作为服务器字符集。如果只指定了字符集没有指定校对规则MySQL 会使用该字符集对应的默认校对规则。如果要使用字符集的非默认校对规则需要在指定字符集的同时指定校对规则。

---

2MySQL环境与字符集相关的变量

变量名	含义
character_set_client	客户端字符集
character_set_connection	客户端与服务器连接使用的字符集
character_set_database	默认数据库使用的字符集
character_set_filesystem	文件系统字符集
character_set_results	将查询结果（返回给客户端的字符集
character_set_server	服务器默认字符集
character_set_system	服务器用来存储标识符的字符集
character_sets_dir	安装字符集的目录

注意事项说明

1. character_set_clientcharacter_set_connectioncharacter_set_resultscharacter_set_server这4个变量是重点需特别注意。
2. character_set_server决定了新创建的数据表默认使用的字符集character_set_client对应的是客户端的字符集character_set_connection对应的是连接层的字符集character_set_results对应的是查询结果的字符集。
3. 数据库的字符集决定了数据表的默认字符集数据表的字符集决定了字段的默认字符集。
4. character_set_clientcharacter_set_connectioncharacter_set_results这三个变量的值在一般情况下是相同的具体值是由客户端的编码决定的进而使客户端的输入字符和输出的查询结果不会出现乱码情况。

---

3字符集变量的设置更改

set 变量名 = 值;

操作实例

set character_set_client = gbk;
set character_set_connecction = gbk;
set character_set_results = gbk;

---

4注意事项说明

1. 上述3个变量的更改在MySQL中还可以使用set names 字符集; 直接进行修改即set names gbk；。
2. 使用set或set names修改字符集只对当前会话有效不会对其它的会话产生影响且会话结束后下次的会话仍然使用默认值。
3. character_set_connectioncharacter_set_databasecharacter_set_server对应的校对集分别通过变量collation_connectioncollation_databasecollation_server来指定。
4. 若字段使用utf8字符集而客户端使用gbk字符集。MySQL会自动进行编码转换。
5. 由于utf8和gbk这两种字符集本质上是不同的虽然常见字符可以转换成功但遇到其中一个字符集中没有对应的特殊字符则就会出现转换失败进而导致乱码的情况。

---

2.2数据库

数据库的字符集和校对集既可以在创建数据库时指定也可以在创建完数据库后通过 ALTER DATABASE 命令进行修改。

1创建数据库时设定字符集和校对集

#既未指定字符集又未指定校对集
CREATE DATABASE 库名称;

#仅指定字符集未指定校对集
CREATE DATABASE 库名称 CHARACTER SET [=] charset_name;

#仅指定校对集未指定字符集
CREATE DATABASE 库名称 COLLATE [=] collation_name;

#既指定字符集又指定校对集
CREATE DATABASE 库名称 CHARACTER SET [=] charset_name COLLATE [=] collation_name;

知识点

1. CHARACTER SET用于指定字符集COLLATE用于指定校对集。

2. 字符集和校对集的设定规则

   • 若指定字符集又指定校对集表示数据库使用指定的字符集和校对集。

   • 若仅指定字符集未指定校对集表示数据库使用该字符集的默认校对集。

   • 若仅指定校对集未指定字符集表示数据库使用该校对集对应的字符集。

   • 若未指定字符集又未指定校对集表示数据库使用服务器字符集和校对集作为数据库的字符集和校对规则。

3. 为了避免受到默认值的影响推荐在创建数据库时指定字符集和校对集。

---

操作实例

创建数据库mydb_1指定字符集为utf8使用默认校对集utf8_genral_ci的示例语句

create database mydb_1 character set utf8;

---

2修改数据库的字符集和校对集

如果您在创建完数据库后想进行修改它的字符集或校对集可以使用 ALTER DATABASE 语句。

#对数据库的字符集单独进行修改
ALTER DATABASE 库名称 CHARACTER SET [=] charset_name;

#对数据库的校对集单独进行修改
ALTER DATABASE 库名称 COLLATE [=] collation_name;

#对数据库的字符集和校对集进行修改
ALTER DATABASE 库名称 CHARACTER SET [=] charset_name COLLATE [=] collation_name;

操作实例

将 mydb_1数据库的字符集修改为 latin1校对规则修改为 latin1_general_ci 的示例语句

ALTER DATABASE mydb_1 CHARACTER SET latin1 COLLATE latin1_general_ci;

需要注意的是如果数据库里已经存在数据表修改字符集后已有的数据表不会按照新的字符集重新存放所以不能通过修改数据库的字符集来修改数据表的内容。

---

3查看数据库的字符集和校对集

#第一步进入操作库
use 库名称;

#第二步查看操作库的创建信息
SHOW CREATE DATABASE 库名称 \G;

---

2.3数据表

数据表的字符集和校对集在创建表的时候指定也可以在创建完表后通过 ALTER TABLE 命令进行修改。

1创建数据表时设定字符集和校对集

#既未指定字符集又未指定校对集
CREATE TABLE 表名称 (字段名 字段类型...);

#仅指定字符集未指定校对集
CREATE TABLE 表名称 (字段名 字段类型...) CHARACTER SET [=] charset_name;

#仅指定校对集未指定字符集
CREATE TABLE 表名称 (字段名 字段类型...) COLLATE [=] collation_name;

#既指定字符集又指定校对集
CREATE TABLE 表名称 (字段名 字段类型...) CHARACTER SET [=] charset_name COLLATE [=] collation_name;

知识点

1. CHARACTER SET可简写为CHARSET。

2. 设置表的字符集及校对集和设置数据库字符集的规则基本类似

   • 若指定字符集又指定校对集表示数据表使用指定的字符集和校对集。

   • 若仅指定字符集未指定校对集表示数据表使用该字符集的默认校对集。

   • 若仅指定校对集未指定字符集表示数据表使用该校对集对应的字符集。

   • 若未指定字符集又未指定校对集表示数据表使用数据库字符集和校对集作为数据表的字符集和校对规则。

3. 为了避免受到默认值的影响推荐在创建数据表时指定字符集和校对集。

操作实例

在数据库mydb_1中创建数据表demo1指定字符集为utf8使用默认校对集utf8_genral_ci的示例语句

create table demo1(
id int,
name varchar(15)
)character set utf8;

---

2修改数据表的字符集和校对集

如果您在创建完数据库后想进行修改它的字符集或校对集可以使用 ALTER tables 语句。

#对数据库的字符集单独进行修改
ALTER TABLE 表名称 CHARACTER SET [=] charset_name;

#对数据库的校对集单独进行修改
ALTER TABLE 表名称 COLLATE [=] collation_name;

#对数据库的字符集和校对集进行修改
ALTER TABLE 表名称 CHARACTER SET [=] charset_name COLLATE [=] collation_name;

操作实例

将数据表demo1的字符集修改为 latin1校对集修改为 latin1_general_ci 的示例语句

alter table demo1 character set latin1 collate latin1_general_ci;

---

3查看数据表的字符集和校对集

SHOW CREATE TABLE 表名称 \G;

---

2.4字段

字段的字符集和校对集在创建表的时候指定也可以在创建完表后通过 ALTER TABLE 命令进行修改

1创建字段时设定字符集和校对集

#既未指定字符集又未指定校对集
CREATE TABLE 表名称 (字段名1 字段类型 ...);

#仅指定字符集未指定校对集
CREATE TABLE 表名称 (字段名1 字段类型 CHARACTER SET [=] charset_name, ...);

#仅指定校对集未指定字符集
CREATE TABLE 表名称 (字段名1 字段类型 COLLATE [=] collation_name, ...);

#既指定字符集又指定校对集
CREATE TABLE 表名称 (字段名1 字段类型 CHARACTER SET [=] charset_name COLLATE [=] collation_name, ...);

操作实例

在数据库mydb_1中创建数据表demo2指定字段name的字符集为utf8使用默认校对集utf8_genral_ci的示例语句

create table demo2(
name varchar(15) character set utf8);

---

2修改数据库的字符集和校对集

如果您在创建完数据库后想进行修改它的字符集或校对集可以使用 ALTER DATABASE 语句语法格式如下

ALTER TABLE 表名称 MODIFY 字段名 数据类型 [CHARACTER SET charset_name][COLLATE collation_name];

操作实例

将数据表demo2里的name字段的字符集修改为utf8,使用默认校对集utf8_genral_ci 的示例语句

alter table demo2 modify name varchar(15) character set utf8;

注意如果在创建字段的时候没有特别指定字符集和校对规则默认使用数据表的字符集和校对规则。

---

3查看数据表的字符集和校对集

SHOW CREATE TABLE 表名称 \G;

操作实例

---

结语

这就是本期博客的全部内容啦想必大家已经对MySQL中的字符集和校对集的相关知识有了全新地认识和理解吧如果有什么其他的问题无法自己解决可以在评论区留言哦

最后如果你觉得这篇文章写的还不错的话或者有所收获的话麻烦小伙伴们动动你们的小手给个三连呗（点赞👍评论✍收藏📖多多支持一下各位的支持是我最大的动力后期不断更新优质的内容来帮助大家一起进步。那我们下期见

---