MySQL 字符集和校验规则工作流程

Wesley13
• 阅读 534

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 表示一个字符,因为一一对应,所有一共有 2^7个字符。扩展字符集拥有2^8。

三 查看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/.

数据库字符集存放地址

  1. MySQL 一旦启动不需要再来关心 character_set_filesystem、character_set_system、character_sets_dir三个变量,因为他们并不会造成乱码的问题。系统文件存储方式不需要关心,字符集存放位置于性能和MySQL业务无关、数据库使用的编码格式是元数据的存储格式。理解MySQL编码转化原理不难理解。

  2. 建库时,若未明确指定字符集,则采用character_set_server指定的字符集。

    建表时,若未明确指定字符集,则采用当前库所采用的字符集。

    新增时记录,修改表字段时,若未明确指定字符集,则采用当前表所采用的字符集。

四 编码方式和校验规则在使用中的作用

1 连接的概念

一个连接:指的是连接服务器时所作的事情。 ——《MySQL手册》

例如:客户端发送SQL语句,例如查询,通过连接发送到服务器。服务器通过连接发送响应给客户端,例如结果集。

2 一个提交的分解

  1. 客户端发起 查询

  2. 服务器使用character_set_client变量作为客户端发送的查询中使用的字符集。

  3. 服务器拿到 查询 后用将character_set_client 编码方式转为 character_set_connection对应的校验规则为collation_connection, (如果查询是文字字符串,也就是他们有某种字符格式的引介词 例如_utf8,如果是列值,校验规则将不依靠collation_connection)

  4. 服务器执行查询的结果 将会 按照 character_set_results 编码方式返回查询结果到客户端。包括结果数据,例如列值和结果元数据(如列名)。

  5. 关于:字符串对3的注解:**[_charset_name] 'String' [COLLATE collation_name] **

    1. **[_charset_name]**字符编码 也就是引介词,表示接下来的字符串的编码方式。
    2. **[COLLATE collation_name]**表示该字符串的校验匹配方式
    3. 来自官方文档对引介词的解释:__charset_name_表达式正式称做一个_引介词_。它告诉解析程序,“后面将要出现的字符串使用字符集X。”因为以前人们对此感到困惑,我们强调引介词不导致任何转换; 它仅是一个符号,不改变字符串的值。引介词在标准十六进制字母和数字十六进制符号(x'literal'和 0x_nnnn_)中是合法的,以及?(当在一个编程语言接口中使用预处理的语句时进行参数替换)。

3 更新和查询转码过程

数据更新转码过程:character_set_client-->character_set_connection-->表字符集。

数据查询转码过程:表字符集-->character_set_result

4 字符串的引介词和校验规则的确定

引介词:__charset_name_表达式正式称做一个_引介词_。它告诉解析程序,“后面将要出现的字符串使用字符集X。”因为以前人们对此感到困惑,我们强调引介词不导致任何转换; 它仅是一个符号,不改变字符串的值。引介词在标准十六进制字母和数字十六进制符号(x'literal'和 0x_nnnn_)中是合法的,以及?(当在一个编程语言接口中使用预处理的语句时进行参数替换)。

  • 如果指定了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 关于校验规则

  1. 每个字符集都有默认的校验规则
  2. 两个不同的字符集不能有一致的校验规则
  3. 存在校对规则命名约定:以其相关的字符集名开始,中间包括一个语言名,并且以_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;

修改表的字符集

  1. 只修改表的字符集,影响后续该表新增列的默认定义,已有列的字符集不受影响。

    alter table table_name character set xxx;

  2. 同时修改表字符集和已有列字符集,并将已有数据进行字符集编码转换。

    alter table table_name convert to character set xxx;

修改列 字符集

alter table table_name modify col_name varchar(col_length) character set xxx;
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
2年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
MySQL字符集
一、字符集和校验规则字符集是一套符号和编码,校验规则(collation)是在字符集内用于比较字符的一套规则,即字符集的排序规则。MySQL可以使用多种字符集和检验规则来组织字符。MySQL服务器可以支持多种字符集,在同一台服务器,同一个数据库,甚至同一个表的不同字段都可以指定使用不同的字符集,相比oracle等其他数据库管理系
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这