3、字符集和比较规则-CSDN博客

本文链接：https://blog.csdn.net/2301_79890189/article/details/139586960

3、字符集和比较规则

声明：此博客是本人学习《MySQL是怎样运行的：从根儿上理解MySQL》的学习笔记，侵权必删。

注：由于内容是从typora导入的，难免会有错误。如果你发现博客中有错误，还请告诉我，我会第一时间修改并给予您诚挚的感谢ღ( ´･ᴗ･` )。

基本概念

什么是字符集？什么是比较规则？

字符集是描述某个字符范围的编码规则；

比较规则是针对字符集中的某个字符如何比较大小的原则。

什么是编码和解码？

编码是将字符映射到二进制的过程，解码是将二进制映射到字符的过程。

产生乱码的原因？

编码和解码所使用的字符集不一致

常见重要的字符集

————`ASCII`字符集

包含字符：空格、标点符号、阿拉伯数字、字母大小写、部分不可见字符。

最大编码字节：1个字节。

不可见字符包括：

空格字符；
控制字符，如换行、回车、制表、退格符等；
其它非打印字符，如空字符、文件分隔符、组分隔符等。

————`ISO 8859-1` 字符集

包含字符：在ASCII字符集的基础上扩充了128个西欧常用字符。

最大编码字节：1个字节

别名：Latin1

————`GB2312`字符集

包含字符：包括汉字等其他字符，兼容ASCII字符集，是一种变长编码方式的字符集。

变长编码方式：表示不同字符可能需要不同的编码长度(字节)的编码方式。

最大编码字节：2个字节。

怎么区分是一个字节编码还是两个字节编码？

如果当前字节十进制大于127，则说明是两个字节编码，因为ASCII字符集已经收录了可以用一个字节编码的字符，而GB2312兼容它，一个字节表示的字符大小范围是0-127；

如果当前字节十进制小于127，则说明是一个字节编码。

————`GBK`字符集

包含字符：对GB2312字符集进行扩充，编码方式上兼容GB2312字符集。

————`utf8`字符集

包含字符：地球人能想到的所有字符，还在不断增加；兼容ASCII字符集，采用变长编码方式。

最大编码字节：4个字节。

`MySQL`中支持的字符集和比较规则

MySQL中支持的字符集超级多，每个字符集包含很多比较规则，那么比较规则更多。

查看字符集

可以使用如下语句查看MySQL中支持的字符集：

# 有两种查询方法，两种方法等效
SHOW CHARSET [LIKE 匹配模式];
SHOW CHARACTER SET [LIKE 匹配模式];

查询结果各字段解释：

在这里插入图片描述

# Charset是字符集
# Description是简要描述
# Default collation是默认比较规则
# Maxlen是最大编码长度

`MySQL`中的`utf8`和`utf8mb4`字符集

我们上面说过utf8最大编码长度是4个字节，但是对于常用字符来说，三个字符足够，所以MySQL对utf8字符集做了裁剪，使用最大长度为3的编码规则，这种阉割版的编码规则又被称为utf8mb3字符集，俗称utf8。而utf8mb4字符集是正规版的utf8，也就是最大编码长度为4。

记住，MySQL中说的utf8其实是utf8mb3，是最大编码长度为3个字节的字符集。

查看比较规则

可以使用如下语句来查看比较规则：

SHOW COLLATION [LIKE 匹配模式];

# 如查看所有utf8的比较规则
SHOW COLLATION LIKE 'utf8\_%';

查询结果各字段解析：

在这里插入图片描述

字符集和比较规则的应用

各级别的字符集和比较规则

MySQL中字符集和比较规则有四个级别：

服务器级别
数据库级别
表级别
列级别

下面分别对四个级别的字符集和比较规则进行具体分析：

服务器级别

服务器级别的字符集和比较规则由两个系统变量控制着，分别是：

# 字符集
character_set_server

# 比较规则
collation_server

查看两变量的值：

SHOW VARIABLES LIKE 'character_set_server'; -- 查看字符集
SHOW VARIABLES LIKE 'collation_server';     -- 查看比较规则

修改两变量的值：

可以通过服务器启动时设置的启动选项来修改：

# 命令行
mysqld --character_set_server=gbk --collation_server=gbk_chinese_ci

# 配置文件
[server]
character_set_server = gbk
collation_server = gbk_chinese_ci

也可以通过服务器运行时的SET语句来修改：

SET GLOBAL character_set_server = gbk;
SET GLOBAL collation_server = gbk_chinese_ci;

数据库级别

数据库级别的字符集和比较规则也与两个变量有关，不过着两个变量都是只读的，也就是说不能通过变量来修改字符集和比较规则。

# 字符集
character_set_database

# 比较规则
collation_database

查看两变量的值：

SHOW VARIABLES LIKE 'character_set_database'; -- 查看字符集
SHOW VARIABLES LIKE 'collation_database';     -- 查看比较规则

修改字符集和比较规则：

在创建或修改数据库时可以进行字符集和比较规则的修改。

# 创建库时
CREATE DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

# 修改库时
ALTER DATABASE 数据库名
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

表级别

表级别和列级别都没有系统变量可供查看(表列哪有这本事a_a)，所以要查看就要费些功夫。

查看字符集和比较规则：

1、通过SHOW CREATE TABLE语句查看：

SHOW CREATE TABLE '表名';
# 可以看到 CHARSET、COLLATE 字样

2、通过查询information_schema.TABLES表查看：

SELECT 
    TABLE_NAME,
    TABLE_COLLATION AS 'Collation'
FROM information_schema.TABLES
WHERE
    TABLE_SCHEMA = '数据库名' AND
    TABLE_NAME = '表名';
    
# 可以查到collation比较规则，需要根据比较规则来推一下字符集。还是推荐用上面那种方式

修改字符集和比较规则：

可以在创建表或修改表进行字符集和比较规则的修改。

# 创建表时
CREATE TABLE 表名
	...(列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

# 修改表时
ALTER TABLE 表名
	...(列的信息)
	[[DEFAULT] CHARACTER SET 字符集名称]
	[[DEFAULT] COLLATE 比较规则名称];

列级别

查看字符集和比较规则：

直接查询information_schema.COLUMNS表来查看。

SELECT   
    COLUMN_NAME,   
    COLLATION_NAME AS 'Collation',   
    CHARACTER_SET_NAME AS 'Charset'   -- 对表没有该选项，但是对列有
FROM   
    information_schema.COLUMNS   
WHERE   
    TABLE_SCHEMA = 'your_database_name' AND   
    TABLE_NAME = 'your_table_name' AND   
    COLUMN_NAME = 'your_column_name';

修改字符集和比较规则：

可以在创建和修改列定义时进行字符集和比较规则的修改。

# 创建列
CREATE TABLE 表名(
 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
 其他列...
);

# 修改列
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

修改列字符集的注意事项：

假设要将trans列的字符集由utf8修改为ASCII，一定要保证trans中的字符不包含ASCII中无法解析的字符，如汉字等，否则会出错。

字符集和比较规则的关联性

字符集和比较规则是具有关联性的，修改A，B就会改变，具体来说有如下原则：

只修改字符集，其比较规则就变为该字符集的默认规则；

只修改比较规则，字符集就变成对应的字符集。

不论哪个级别的字符集和比较规则都适用与上述原则。

各级别字符集和比较规则的缺省情况

如果当前级别的字符集和比较规则缺省，即没有明确设置，其字符集和比较规则就会使用其上一级的字符集和比较规则(由于字符集和比较规则的关联性，不存在两者只设置了一个的情况)。

拿表级别来说，若创建表时没有指定字符集和比较规则，那么该表就会使用其所在数据库的字符集和比较规则。

客户端和服务器通信中的字符集

字符集转换的概念

字符通过一种字符集解码，再通过另一种字符集编码的过程叫做字符集转换。

客户端与服务器通信时所使用的字符集

客户端发送请求所用字符集：

跟随操作系统，类Unix为utf8，Windows为gbk。若使用比如navicat之类的可视化工具，就会按自定义的字符集发送连接请求。

服务器处理请求所用字符集：

与三个系统变量有关，三个系统变量分别控制着服务器处理请求的不同阶段的字符集：

character_set_client     -- 服务器解码请求时使用的字符集
character_set_connection -- 服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
character_set_results    -- 服务器向客户端返回数据时使用的字符集

客户端和服务器通信过程中字符集的变化如下图：

在这里插入图片描述

对于操作①和操作⑤，服务器和客户端双方的字符集必须一致，否则就会出现其中一方无法解析的情况。

转换太麻烦了，能不能只用一种编码？

可以！不仅可以，MySQL还为我们提供了一起设置三个系统变量的语句，简直不要太贴心~语句如下：

SET NAMES 字符集名; -- 为当前'客户端-服务器'连接统一设置三个系统变量

# 该语句与下面三条语句效果相同
SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

还是太麻烦，能不能让以后连接的每个’客户端-服务器’连接都只用同一种编码？

如果有这样的需求，那就只能修改配置文件了。可以将client分组的default-character-set选项设置为指定字符集。如：

...

[client]
default-character-set=utf8 # 后续连接的客户端全都设置为utf8字符集

...

这个知识点还牵出了另外的一个知识点：

对于不同的客户端连接，这三个系统变量的值是可以不同的。

比较规则的应用

主要有两个应用：

比较字符串大小；
对字符串进行排序，所以比较规则也叫做"排序规则"。

3、字符集和比较规则