潍坊网站建设方案推广,网站建设的职业叫什么,怎么登录别人的wordpress,wordpress 媒体库 最大MySQL字符集和比较规则
字符集和比较规则简介 字符集#xff1a; 描述字符与二进制数据的映射关系 比较规则#xff1a;比较指定字符集中的字符的规则 字符集
我们知道#xff0c;计算机无法直接存储字符串#xff0c;实际存储的都是二进制数据。字符集是有限的#xff…MySQL字符集和比较规则
字符集和比较规则简介 字符集 描述字符与二进制数据的映射关系 比较规则比较指定字符集中的字符的规则 字符集
我们知道计算机无法直接存储字符串实际存储的都是二进制数据。字符集是有限的也就是字符范围比如ASCII字符集包含了128个字符与进制数据的映射关系。将字符根据编码规则映射成二进制数据的过程叫做编码将二进制数据根据编码规则映射到字符的过程叫做解码。
举个例子在ASCII字符集中存在如下映射关系
A - 01000001(十六进制为0x41)
B - 01000010(十六进制为0x42)
a - 01100001(十六进制为0x61)
b - 01100010(十六进制为0x62)
我 - 不存在因为ASCII字符集的字符范围里并不存在我与8位组的对应关系查看字符集
命令SHOW (CHARSET | CHARACTER SET) [LIKE 匹配的模式]
mysql SHOW CHARSET;
------------------------------------------------------------------------
| Charset | Description | Default collation | Maxlen |
------------------------------------------------------------------------
| ascii | US ASCII | ascii_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
...
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| utf8mb3 | UTF-8 Unicode | utf8mb3_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
------------------------------------------------------------------------
41 rows in set (0.01 sec)本文使用的MySQL8.0版本中共支持41中字符集(仅列出了常用字符集)。其中Default collation为该字符集默认的比较规则Maxlen是最多需要使用几个字节来映射一个字符。
比较规则
在确定字符集之后还需要比较字符集中两个字符的大小或是否相等此时就需要用到字符集的比较规则。最简单的比较规则就是直接比较两个字符对应的二进制编码的大小。一个字符集可以有多种比较规则。
查看比较规则
命令SHOW COLLATION [LIKE 匹配的模式]
mysql SHOW COLLATION LIKE %utf8mb4%;
-------------------------------------------------------------------------------------
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
-------------------------------------------------------------------------------------
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
| utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD |
| utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
...
| utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE |
...
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE |
| utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD |
-------------------------------------------------------------------------------------
89 rows in set (0.00 sec)上述代码块中展示了MySQL8.0中utf8mb4这个字符集的常用比较规则。这些比较规则是有规律的。
比较规则的名称以其关联的字符集名称开头。后面紧跟着该比较规则所应用的语言比如utf8mb4_spanish_ci表示西班牙语的比较规则。utf8mb4_general_ci是一种通用的比较规则。名称后缀表示该规则是否区分大小写、语言中的重音等。具体后缀如下表所示
后缀英文释义描述_aiaccent insensitive不区分重音_asaccent sensitive区分重音_cicase insensitive不区分大小写_cscase sensitive区分大小写_binbinary以二进制方式比较
常用的字符集
计算机是美国人发明的因此ACISS字符集中包含了26个英文字母和一些字符就足够美国人日常使用了。但是如果一些其他国家和地区想要使用计算机就必须有一套包含自身语言的字符集比如GBK、IOS-8859-1等字符集。以下是一些常用的字符集
ASCII包含128个字符包括空格、标点符号、数字、大小写字母和一些不可见字符回车、制表符等。由于ASCII字符集总共才128个字符所以使用**一个字节8位**就足够保存了。GB2312收录6763个汉字以及拉丁字母、希腊字母等兼容ASCII字符集。使用GB2312编码时如果该字符存在于ASCII字符集中则使用一字节编码否则采用两字节编码。这种使用不同不同字节数来表示一个字符的编码方式称为变长编码。GBK(国标扩展的首字母)GBK对GB2312进行了扩充编码方式兼容GB2312。Unicode几乎收录了各个国家使用的字符并且还在不断扩充。Unicode仅定义了字符和数值的映射关系。具体存储需要看是UTF-8、UTF-16、UTF-32等。
Unicode字符集
Unicode定义了映射关系但是却没有规定如何存储。一个码值为 0x41 的字符只需要 1字节 就可以存下但是码值 0x9ED1 的字符需要 2字节 的空间才可以装下而更靠后的字符可能会需要 3字节 甚至 4字节 的空间。
UTF-16 和 UTF-32
我们可以规定一个字符使用四个字节存储也就是 32 位这样就能涵盖现有 Unicode 包含的所有字符这种编码方式叫做 UTF-32UCS Transformation Format 的缩写。UTF-32 的规则虽然简单但是缺陷也很明显假设使用 UTF-32 和 ASCII 分别对一个只有西文字母的文档编码前者需要花费的空间是后者的四倍ASCII 每个字符只需要一个字节存储。
UTF-16是一种变长字符编码, 它将字符编码成 2 字节 或者 4 字节。与UTF-32类似同样存在着空间浪费的问题。
UTF-8
在存储和网络传输中通常使用更为节省空间的变长编码方式 UTF-8UTF-8 代表 8 位一组表示 Unicode 字符的格式采用变长编码使用 1 - 4 个字节来表示字符比如下面这样
C - 01000011(十六进制为0x43) // 1字节
斌 - 11100110 10010110 10001100(十六进制为0xE6968C) // 3字节 变长编码通常需要存储表示字节数的信息让计算机知道字符是以几个字节来存储的。 UTF-8用首字节的前几位表示编码的字节数
首字节以0开头表示单字节编码首字节以110开头表示双字节编码后续字节以10开头首字节以1110开头表示三字节编码后续字节以10开头首字节以11110开头表示四字节编码后续字节以10开头 举个例子就拿上方代码块的 ‘斌’ 这个字符 通过中文转Unicode编码工具可知码值为 \u658C。 转换为二进制数01100101 10001100我们得到码点后采用UTF-8将其存储到计算机中我们需要16位有效比特位从上图可以看出只要 3字节 即可表示。 将二进制数代入规则1110 0110 1001 0110 1000 1100红色字体为有效比特位
字符集和比较规则的应用
各级别的字符集和比较规则
在MySQL中可以从服务器、数据库、表、列这几个维度分别设置和查看字符集及其比较规则。
服务器级别
系统变量描述character_set_server服务器级别的字符集collation_server服务器级别的比较规则
MySQL8.0中默认的服务器字符集为utf8mb4比较规则为utf8mb4_0900_ai_ci。
mysql SHOW variables like character_set_server;
-------------------------------
| Variable_name | Value |
-------------------------------
| character_set_server | utf8mb4 |
-------------------------------mysql SHOW variables like collation_server;
--------------------------------------
| Variable_name | Value |
--------------------------------------
| collation_server | utf8mb4_0900_ai_ci |
-------------------------------------- 修改MySQL系统变量的方式可以通过启动选项或者在服务器程序运行过程中使用SET语句来修改。也可以修改配置文件的*[server]*组信息。
数据库级别
具体数据库的字符集和比较规则可以在创建是指定创建后也可以通过ALTER语句来修改。
# 创建或修改数据库时指定字符集
(CREATE | ALTER) DATABASE 数据库名称[CHARACTER SET 字符集名称][COLLATE 比较规则名称];系统变量描述character_set_database当前数据库的字符集collation_database当前数据库的比较规则
数据库级别的两个系统变量与服务器级别不同的是这我们不能通过修改这两个变量的值来改变当前数据库的字符集和比较规则修改可以通过ALTER语句。
创建数据库时也可以不指定字符集和比较规则这将默认使用服务器级别的值。
表级别
与数据库级别类似表的字符集和比较规则也可以在创建表时指定或创建后修改。
# 创建表时指定
CREATE TABLE 表名 (列信息)[CHARACTER SET 字符集名称][COLLATE 比较规则名称];# 修改表的字符集
ALTER TABLE 表名[CHARACTER SET 字符集名称][COLLATE 比较规则名称]; 创建数据库时同样可以不指定字符集和比较规则这将继承数据库级别。
列级别
对于存储字符串的列同一个表中不同的列可以有不同的字符集和比较规则。在创建和修改列的时候可以指定该列的字符集和比较规则。
# 创建时指定
CREATE TABLE 表名(列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],...
);# 修改列的字符集
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集] [COLLATE 比较规则名称];同样如果没有指定就会使用表级别的。需要注意的是修改指定列的字符集时如果列中存储的数据不能用修改后的字符集进行表示则会发生错误中断修改操作。
小结各级别的字符集和比较规则
如果创建或修改列时没有显式指定字符集和比较规则则默认使用表的字符集和比较规则。如果创建表时没有显式指定则默认使用数据库的。如果创建数据库时没有显式指定则该数据库默认使用服务器的。 知道列的字符集后就能知道某个列存储的实际数据所占用的存储空间大小。 客户端和服务器通信过程中的字符集 什么是乱码计算机对某个字节序列进行解码时采用的字符集和编码时使用的字符集不一致就会产生乱码。例如’我’在utf-8下字节序列为0xE68891若采用GBK解码会先读前两个字节。 客户端发送阶段
使用MySQL自带的mysql客户端程序发送请求时(如下图)客户端编码请求字符串时使用的字符集与操作系统当前使用的字符集一致。 在Windows系统中可以在启动客户端时使用命令default-character-set指定字符集Unix系统慎用指定字符集与操作系统不一致时会乱码。
mysql --default-character-setutf8在连接服务端时客户端将默认的字符集信息或者通过上述方式指定字符集与用户名、密码等信息一起发送给服务器。服务端收到后将character_set_client、character_set_connetion、character_set_results这3个系统变量的值初始化为客户端传递的字符集作用请参考 2.2.2 节。
服务端处理阶段
系统变量描述character_set_client服务器认为请求是按照该系统变量指定的字符集进行编码character_set_connection服务器处理请求时把请求字节序列从character_set_client转换为character_set_connection对应的字符集character_set_results服务器采用该系统变量指定的字符集对返回给客户端的字符串进行编码
接收请求
服务端接收到客户端的请求的字节序列时根据系统变量character_set_client对应的字符集解码该字节序列该变量是SESSION级别的可以对不同客户端设置不同的解码规则。
处理请求
在真正处理请求时又会将其转换为系统变量character_set_connection对应的字符集进行编码的字节序列该变量也是SESSION级别的与之配套的另一个变量是collation_connection。
将字节序列根据 character_set_client 转码为 character_set_connection 是为了确保数据在不同环节中的一致性、避免字符集不匹配问题、提高系统的灵活性和兼容性。这样可以保证 MySQL 在处理请求时能够正确解析和处理不同字符集编码的数据确保数据的完整性和准确性。
CREATE TABLE table1(col VARCHAR(100)
) ENGINEInnoDB CHARSETutf8;假设当前的character_set_connection为GBK。
当执行如下查询语句
SELECT * FROM table1 WHERE col 斌;表中的col字段是UTF-8编码但连接为GBK在这种情况下列的字符集和排序规则的优先级会更高因此会将 ‘斌’ 从 GBK 转为 UTF-8 后使用 c 列的比较规则进行比较。
返回结果
服务端将查询结果从对应列的字符集编码转换为character_set_results系统变量对应的字符集编码后的字节序列之后再发送给客户端。该变量亦是SESSION级别的。
客户端接收结果阶段
在此阶段客户端收到的响应也是一个字节序列。对于Windows系统客户端会使用MySQL客户端的默认字符集来解码这个字节序列启动项 –default-character-set。但是 Unix 系统会使用系统默认编码直接输出到控制台因此可能会产生乱码。如下场景
查看当前操作系统的默认编码 在 Mac OS 启动MySQL客户端时使用 –default_character_setgbk。 客户端与服务端建立连接时指定了编码方式为 GBK 因此服务端将会返回 GBK 编码后的字节序列。 Mac OS 会直接将序列输出到终端使用的是系统的编码 UTF-8 因此出现乱码windows会使用客户端的编码。
