MySQL 字符集和校验规则工作流程
MySQL 字符集和校验规则工作原理
字符编码相关参数
数据流中的转码过程
校验规则
Tips:字符集和校验规则总是相伴的
一 从简单的建库语句开始
CREATE DATABASE [IF NOT EXISTS] <db_name>
[[DEFAULT] CHARACTER SET <db_charset>]
[[DEFAULT] COLLATE <db_collation>];
db_name : 数据库名 必填
db_charset:数据库的字符集 缺省为服务器字符集
db_collation:数据库的校验规则 缺省为服务器校对规则
二 字符集和字符编码是什么?
字符编码:将特定的字符与二进制码建立一一映射的集合就是字符集。每种字符集对应该种字符集的编码方式。
常见的字符集有 仅支持英文和特殊字符的ASCII、支持中英文的GBK、支持世界所有字符的Unicode等等<UTF-8是Unicode字符集的子集,他们不是两种编码方式>。
以ASCII字符集为例
它基于罗马字母表的编码方式,他不能表示中文仅仅包含了 全部的英文大小写和为数不多的特殊符号,每个字符一个字节低7位为编码位最高位保留,有些地方最高位做了扩充。增添了一些表格符号、运算符等。总而言之1个字节 8bit 表示一个字符,因为一一对应,所有一共有 27个字符。扩展字符集拥有28。
三 查看MySQL编码方式
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 | 服务器的默认编码格式 |
character_set_system | utf8 | 数据库系统使用的编码格式 |
character_sets_dir | /usr/local/mysql-8.0.15-macos10.14-x86_64/share/charsets/. | 数据库字符集存放地址 |
-
MySQL 一旦启动不需要再来关心 character_set_filesystem、character_set_system、character_sets_dir三个变量,因为他们并不会造成乱码的问题。系统文件存储方式不需要关心,字符集存放位置于性能和MySQL业务无关、数据库使用的编码格式是元数据的存储格式。理解MySQL编码转化原理不难理解。
-
建库时,若未明确指定字符集,则采用character_set_server指定的字符集。
建表时,若未明确指定字符集,则采用当前库所采用的字符集。
新增时记录,修改表字段时,若未明确指定字符集,则采用当前表所采用的字符集。
四 编码方式和校验规则在使用中的作用
1 连接的概念
一个连接:指的是连接服务器时所作的事情。 ——《MySQL手册》
例如:客户端发送SQL语句,例如查询,通过连接发送到服务器。服务器通过连接发送响应给客户端,例如结果集。
2 一个提交的分解
-
客户端发起 查询
-
服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。
-
服务器拿到 查询 后用将character_set_client 编码方式转为 character_set_connection对应的校验规则为collation_connection, (如果查询是文字字符串,也就是他们有某种字符格式的引介词 例如_utf8,如果是列值,校验规则将不依靠collation_connection)
-
服务器执行查询的结果 将会 按照 character_set_results 编码方式返回查询结果到客户端。包括结果数据,例如列值和结果元数据(如列名)。
-
关于:字符串对3的注解:**[_charset_name] 'String' [COLLATE collation_name] **
- [_charset_name]字符编码 也就是引介词,表示接下来的字符串的编码方式。
- [COLLATE collation_name]表示该字符串的校验匹配方式
- 来自官方文档对引介词的解释:_charset_name表达式正式称做一个引介词。它告诉解析程序,“后面将要出现的字符串使用字符集X。”因为以前人们对此感到困惑,我们强调引介词不导致任何转换; 它仅是一个符号,不改变字符串的值。引介词在标准十六进制字母和数字十六进制符号(x'literal'和 0xnnnn)中是合法的,以及?(当在一个编程语言接口中使用预处理的语句时进行参数替换)。
3 更新和查询转码过程
数据更新转码过程:character_set_client-->character_set_connection-->表字符集。
数据查询转码过程:表字符集-->character_set_result
4 字符串的引介词和校验规则的确定
引介词:_charset_name表达式正式称做一个引介词。它告诉解析程序,“后面将要出现的字符串使用字符集X。”因为以前人们对此感到困惑,我们强调引介词不导致任何转换; 它仅是一个符号,不改变字符串的值。引介词在标准十六进制字母和数字十六进制符号(x'literal'和 0xnnnn)中是合法的,以及?(当在一个编程语言接口中使用预处理的语句时进行参数替换)。
-
如果指定了CHARACTER SET X和COLLATE Y,那么使用CHARACTER SET X和COLLATE Y。
-
如果指定了CHARACTER SET X而没有指定COLLATE Y,那么使用CHARACTER SET X和CHARACTER SET X的默认校对规则。
-
否则,使用通过character_set_connection 和 collation_connection系统变量给出的字符集和 校对规则。
Tips:COLLATE子句,能够为一个查询覆盖任何默认校对规则。MySQL手册
5 关于校验规则
- 每个字符集都有默认的校验规则
- 两个不同的字符集不能有一致的校验规则
- 存在校对规则命名约定:以其相关的字符集名开始,中间包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二元)结束。
常见操作
查看现在支持的所有编码方式
show character set;
查看支持的所有检验规则
show collation;
查看字符编码设置
show variables like 'character%';
查看当前字符集和校对规则设置
show variables like 'collation_%';
设置编码字符集
set names 'utf8';
修改数据库字符集
alter database database_name character set xxx;
修改表的字符集
- 只修改表的字符集,影响后续该表新增列的默认定义,已有列的字符集不受影响。
alter table table_name character set xxx;
- 同时修改表字符集和已有列字符集,并将已有数据进行字符集编码转换。
alter table table_name convert to character set xxx;
修改列 字符集
alter table table_name modify col_name varchar(col_length) character set xxx;