字符集和比较规则

MySQL中的utf8和utf8mb4

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

• utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。
• utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符

有一点需要大家十分的注意，在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情什么的，那请使用utf8mb4

字符集查看

SHOW CHARSET

返回结果中的最后一列Maxlen，它代表该种字符集表示一个字符最多需要几个字节

字符集名称	Maxlen
ascii	1
latin1	1
gb2312	2
gbk	2
utf8	3
utf8mb4	4

比较规则查看

utf8下的比较规则

• 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。
• 后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。
• 名称后缀意味着该比较规则是否区分语言中的重音、大小写什么的，具体可以用的值如下

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

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，SHOW COLLATION的返回结果中的Default列的值为YES的就是该字符集的默认比较规则，比方说utf8字符集默认的比较规则就是utf8_general_ci

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

MySQL有4个级别的字符集和比较规则，分别是：

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

服务器级别

SHOW VARIABLES LIKE 'character_set_server';		# 字符集

SHOW VARIABLES LIKE 'collation_server';			# 比骄规则

可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值

[server]
character_set_server=gbk
collation_server=gbk_chinese_ci

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了

数据库级别

在创建和修改数据库的时候可以指定该数据库的字符集和比较规则

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

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

查看数据库的字符集和比较规则

USE demo_test2;
SHOW VARIABLES LIKE 'character_set_database';	# 数据库字符集

USE demo_test2;
SHOW VARIABLES LIKE 'collation_database';

需要注意的一点是： *character_set_database* 和 *collation_database* 这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则

创建数据库不指定得话，将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则

表级别

可以在创建和修改表的时候指定表的字符集和比较规则

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

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

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

列级别

同一个表中的不同的列也可以有不同的字符集和比较规则

在创建和修改列定义的时候可以指定该列的字符集和比较规则

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

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

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

如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则

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

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

MySQL中字符集的转换

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

• 服务器认为客户端发送过来的请求是用character_set_client编码的
• 服务器将把得到的结果集使用character_set_results编码后发送给客户端
• character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码

我们通常都把 character_set_client* 、character_set_connection**、*character_set_results* 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句

SET NAMES 字符集名;

这一条语句产生的效果和我们执行这3条的效果是一样

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

或者在配置文件里可以这么写

[client]
default-character-set=utf8