https://juejin.im/book/5bffcbc9f265da614b11b731
字符集和比较规则简介
一些重要的字符集
-
ASCII
字符集共收录128个字符,包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符,所以可以使用1个字节来进行编码,我们看一些字符的编码方式:
'L' -> 01001100(十六进制:0x4C,十进制:76) 'M' -> 01001101(十六进制:0x4D,十进制:77)
-
ISO 8859-1
字符集共收录256个字符,是在
ASCII
字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母),也可以使用1个字节来进行编码。这个字符集也有一个别名latin1
。
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
。
字符集的查看
MySQL
中支持的字符集:1 SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
其中的Default collation
列表示这种字符集中一种默认的比较规则
。大家注意返回结果中的最后一列Maxlen
,它代表该种字符集表示一个字符最多需要几个字节。
字符集名称 | Maxlen |
ascii | 1 |
latin1 | 1 |
gb2312 | 1 |
gbk | 1 |
utf8 | 1 |
utf8mb4 | 1 |
utf8
字符集下的比较规则:SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
后缀 | 英文释义 | 描述 |
_ai | accent insensitive | 不区分重音 |
_ai | accent sensitive | 区分重音 |
_ci | case insensitive | 不区分大小写 |
_cs | case sensitive | 区分大小写 |
_bin | binary | 二进制比较 |
SHOW COLLATION
的返回结果中的Default
列的值为YES
的就是该字符集的默认比较规则,比方说utf8
字符集默认的比较规则就是utf8_general_ci
。
字符集和比较规则的应用
各级别的字符集和比较规则
MySQL
有4个级别的字符集和比较规则,分别是:
- 服务器级别
- 数据库级别
- 表级别
- 列级别
服务器级别
1 SHOW VARIABLES LIKE 'character_set_server';
我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET
语句修改这两个变量的值。比如我们可以在配置文件中这样写:
1 [server] character_set_server=gbk collation_server=gbk_chinese_ci
数据库级别
1 CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; 2 ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称]; 3 4 mysql> CREATE DATABASE charset_demo_db -> CHARACTER SET gb2312 -> COLLATE gb2312_chinese_ci; 5 Query OK, 1 row affected (0.01 sec)
需要注意的一点是: character_set_database 和 collation_database 这两个系统变量是只读的,我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。
数据库的创建语句中也可以不指定字符集和比较规则,比如这样:
CREATE DATABASE 数据库名;
这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。
表级别
我们也可以在创建和修改表的时候指定表的字符集和比较规则,语法如下:
CREATE TABLE 表名 (列的信息) [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]]
ALTER TABLE 表名 [[DEFAULT] CHARACTER SET 字符集名称] [COLLATE 比较规则名称]
列级别
需要注意的是,对于存储字符串的列,同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则,语法如下:
CREATE TABLE 表名( 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称], 其他列... );
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];
仅修改字符集或仅修改比较规则
由于字符集和比较规则是互相有联系的,如果我们只修改了字符集,比较规则也会跟着变化,如果只修改了比较规则,字符集也会跟着变化,具体规则如下:
- 只修改字符集,则比较规则将变为修改后的字符集默认的比较规则。
- 只修改比较规则,则字符集将变为修改后的比较规则对应的字符集。
不论哪个级别的字符集和比较规则,这两条规则都适用
各级别字符集和比较规则小结
我们介绍的这4个级别字符集和比较规则的联系如下:
- 如果创建或修改列时没有显式的指定字符集和比较规则,则该列默认用表的字符集和比较规则
- 如果创建或修改表时没有显式的指定字符集和比较规则,则该表默认用数据库的字符集和比较规则
- 如果创建或修改数据库时没有显式的指定字符集和比较规则,则该数据库默认用服务器的字符集和比较规则说
客户端和服务器通信中的字符集
编码和解码使用的字符集不一致的后果
系统变量 | 描述 |
character_set_client
|
服务器解码时请求使用的字符集 |
character_set_connection
|
服务器处理请求时转换出的字符集 |
character_set_results
|
服务器向客户短返回数据时使用的字符集 |
从这个分析中我们可以得出这么几点需要注意的地方:
-
服务器认为客户端发送过来的请求是用
character_set_client
编码的。假设你的客户端采用的字符集和 character_set_client 不一样的话,这就会出现意想不到的情况。比如我的客户端使用的是
utf8
字符集,如果把系统变量character_set_client
的值设置为ascii
的话,服务器可能无法理解我们发送的请求,更别谈处理这个请求了。 -
服务器将把得到的结果集使用
character_set_results
编码后发送给客户端。假设你的客户端采用的字符集和 character_set_results 不一样的话,这就可能会出现客户端无法解码结果集的情况,结果就是在你的屏幕上出现乱码。比如我的客户端使用的是
utf8
字符集,如果把系统变量character_set_results
的值设置为ascii
的话,可能会产生乱码。 character_set_connection
只是服务器在将请求的字节串从character_set_client
转换为character_set_connection
时使用,它是什么其实没多重要,但是一定要注意,该字符集包含的字符范围一定涵盖请求中的字符,要不然会导致有的字符无法使用character_set_connection
代表的字符集进行编码。比如你把character_set_client
设置为utf8
,把character_set_connection
设置成ascii
,那么此时你如果从客户端发送一个汉字到服务器,那么服务器无法使用ascii
字符集来编码这个汉字,就会向用户发出一个警告。
我们通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况,这样减少了很多无谓的字符集转换。为了方便我们设置,MySQL
提供了一条非常简便的语句:
SET NAMES 字符集名;
SET character_set_client = 字符集名; SET character_set_connection = 字符集名; SET character_set_results = 字符集名;
比较规则
的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中,所以有时候也称为排序规则
。
比较规则
的问题。总结
字符集
指的是某个字符范围的编码规则。比较规则
是针对某个字符集中的字符比较大小的一种规则。- 在
MySQL
中,一个字符集可以有若干种比较规则,其中有一个默认的比较规则,一个比较规则必须对应一个字符集。 -
查看
MySQL
中查看支持的字符集和比较规则的语句如下:SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式]; SHOW COLLATION [LIKE 匹配的模式];
- MySQL有四个级别的字符集和比较规则
-
-
服务器级别
character_set_server
表示服务器级别的字符集,collation_server
表示服务器级别的比较规则。 -
数据库级别
-
服务器级别
创建和修改数据库时可以指定字符集和比较规则
-
CREATE DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称];
ALTER DATABASE 数据库名 [[DEFAULT] CHARACTER SET 字符集名称] [[DEFAULT] COLLATE 比较规则名称];
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 比较规则名称];
-
列级别
-
从发送请求到接收结果过程中发生的字符集转换:
- 客户端使用操作系统的字符集编码请求字符串,向服务器发送的是经过编码的一个字节串。
- 服务器将客户端发送来的字节串采用
character_set_client
代表的字符集进行解码,将解码后的字符串再按照character_set_connection
代表的字符集进行编码。 - 如果
character_set_connection
代表的字符集和具体操作的列使用的字符集一致,则直接进行相应操作,否则的话需要将请求中的字符串从character_set_connection
代表的字符集转换为具体操作的列使用的字符集之后再进行操作。 - 将从某个列获取到的字节串从该列使用的字符集转换为
character_set_results
代表的字符集后发送到客户端。 - 客户端使用操作系统的字符集解析收到的结果集字节串。
在这个过程中各个系统变量的含义如下:
系统变量 描述 character_set_client
服务器解码请求时使用的字符集 character_set_connection
服务器处理请求时会把请求字符串从 character_set_client
转为character_set_connection
character_set_results
服务器向客户端返回数据时使用的字符集 一般情况下要使用保持这三个变量的值和客户端使用的字符集相同。
- 比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中。
本站文章如无特殊说明,均为本站原创,如若转载,请注明出处:MySQL 是怎样运行的:从根儿上理解 MySQL:字符集和比较规则 - Python技术站