MYSQL中的字符集和比较规则

最新推荐文章于 2024-08-28 18:26:56 发布

yang_56

最新推荐文章于 2024-08-28 18:26:56 发布

阅读量211

点赞数 2

分类专栏： MySQL

本文链接：https://blog.csdn.net/yang_girl/article/details/110312684

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文详细介绍了字符集和比较规则的概念，包括ASCII、ISO8859-1、GB2312、GBK和UTF-8等常见字符集。重点讲述了MySQL中的字符集支持，尤其是utf8mb3和utf8mb4的区别，并提供了查看字符集和比较规则的方法。此外，还阐述了字符集和比较规则在服务器、数据库、表和列四级的应用，以及如何修改和选择合适的比较规则。

摘要由CSDN通过智能技术生成

字符集和比较规则简介

计算机中实际存储的是二进制数组，那它是怎么存储字符串呢？当然是建立字符串和二进制数组的映射关系了。要建立关系，首先要明白：

要把哪些字符映射成二进制数据？也就是界定字符范围。
怎么映射？将字符映射成二进制数据的过程叫做编码，将二进制数据映射到字符的过程叫做解码。

字符集是指用来描述某个字符范围的编码规则。比较规则是指用来比较两个字符大小的规则。

常用字符集

不同的字符集，它们表示的字符范围和用到的编码规则可能都不一样。对于同一个字符，不同的字符集可能采用不同的编码方式。

ASCII字符集：共收录128个字符，所以使用一个字符来编码。该字符集包括空格、标点符号、数字、大小写字母以及一些不可见字符。
ISO 8859-1 字符集(Latin1)：共收录256个字符，也可以使用一个字节来进行编码，它在ASCII的基础上有扩充了128个字符，包括德法两国的字母。
GB2312字符集：收录了包括汉子以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母，共有汉字6763个和682个其他文字符号。该字符集兼容了ASCII字符集，在编码时如果该字符在ASCII字符集中，则使用一字节编码，否则使用二字节编码。这种使用不同字节数来表示一个字符的编码方式称为变长编码方式。
GBK字符集：GBK字符集只在编码范围上对GB2312字符集进行了扩充，编码方式兼容GB2312字符集。
UTF-8字符集：几乎收录了各个国家和地区使用到的字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符时使用1～4个字节。

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

MySQL中并不区分字符集和编码方案的概念。

MySQL中的utf8和utf8mb4

UTF-8字符集在表示一个字符时需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。而在MySQL中，字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能。所在MySQL中定义了以下两个概念。

utf8mb3：处理过的UTF-8字符集，只使用1～3个字节表示字符。
utf8mb4：正宗的UTF-8字符集，使用1～4字节表示字符。

在MySQL中，utf8是utf8mb3的别名，意味着使用1～3个字节来表示一个字符。如果有使用4字节编码一个字符的情况，比如存储一些emoji表情，请使用utf8mb4。在MySQL8.0中，已经很大程度地优化了utf8mb4字符集的性能，而且已经将其设置为默认的字符集。

字符集的查看

--查看当前MySQL中支持的字符集
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
SHOW CHARSET;
--查看MySQL中支持的比较规则
SHOW COLLATION [LIKE 匹配的模式];
SHOW COLLATION LIKE 'utf8\_%';

后缀	描述
_ai	不区分重音（accent insensitive）
_as	区分重音（accent sensitive）
_ci	不区分大小写（case insensitive）
_cs	区分大小写（case sensitive）
_bin	以二进制方式比较（binary）

字符集和比较规则的应用

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

MySQL有四个级别的字符集和比较规则，分别是服务器级别、数据库级别、表级别、列级别。

服务器级别

--MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则。
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

数据库级别
我们在创建和修改数据库时可以指定该数据库的字符集和比较规则。

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

ALTER DATABASE 数据库名
[[DEFAULT] CHARACTER SET 字符集名称]
[[DEFAULT] COLLATE 比较规则名称];
--查看数据库字符集和比较规则
USE 数据库名;
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';

查看当前数据库的字符集和比较规则时必须use database，否则显示变量与服务器级别下相应的值。创建数据库时不指定字符集和比较规则也将默认使用服务器级别的字符集和比较规则。

表级别

CREATE TABLE 表名 （列的信息）
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];

ALTER TABLE 表名
[[DEFAULT] CHARACTER SET 字符集名称]
[COLLATE 比较规则名称];

如果创建表的语句中没指明字符集和比较规则，则使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

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

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

对于某列来说，如果在创建表的时候没有指定字符集和比较规则，则使用表的字符集和比较规则，在修改列的字符集时如果列中的存储的数据不能用修改后的字符集进行表示，则会发生错误。

仅修改字符集或仅修改比较规则

由于字符集和比较规则之间相互关联，因此如果只修改字符集，比较规则也会跟着变化；如果只修改比较规则，字符集也会跟着变化。具体规则如下：

只修改字符集，则比较规则将变为修改后的字符集默认的比较规则；
只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

比较规则的应用

字符串在计算机上的体现就是一个字节序列。如果使用不同的字符集去解码这个字节序列，最后将得到不同的结果。而比较规则通常用来比较字符串的大小以及对某些字符串进行排序，所以有时候也称为排序规则。比如当表中某列的字符集为gbk，比较规则为gbk_chinese_ci则在排序中不区分大小写，如果改成gbk_bin则直接比较字符的二进制编码，所以区分大小写。所以在对字符串进行比较或者对某个字符串列执行排序操作时，如果没有得到想象中的结果，可以考虑一下是不是比较规则的问题。