MySQL -- 字符集与比较规则

2019-07-06 本文已影响0人 sunyelw

前言

最近在学MySQL，决定记录一下，能写多少写多少，不定时更新，加油。

正文

分几个部分来吧，大致如下：

字符集与比较规则
行格式与数据页
InnoDB索引
访问方法与连接
explain 与子查询优化
redo 与 undo 日志
MVCC 与锁

本文为第一部分字符集与比较规则

首先你自己装个MySQL呗，我说的是安装版、配环境变量的那种，网上都有教程。

声明：本文所有SQL执行结果基于 MySQL5.7.26

sunyelw@windows:$ mysql -V

E:\MySQL\mysql-5.7.26\bin\mysql.exe Ver 14.14 Distrib 5.7.26, for Win64 (x86_64)

Windows 系统下哦~

概述

MySQL是一个数据库软件，做的事大致就是把数据存起来并提供ACID的功能。

数据以什么方式存在于库中呢？

两个概念：

字符集：字符的集合
字符编码：字符到二进制的映射

编程世界有个Unicode字符集，然后对应有很多字符编码方式blabla

MySQL怎么玩的呢？其实大部分差不多，小部分有差异，比如那个utf8mb4。

查看字符集

show [charset | character set] [like 匹配模式];


mysql> show charset like 'utf8%';

+---------+---------------+--------------------+--------+

| Charset | Description  | Default collation  | Maxlen |

+---------+---------------+--------------------+--------+

| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |

| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |

+---------+---------------+--------------------+--------+

2 rows in set (0.00 sec)

每列含义:

Charset 字符集名称
Description 字符集描述信息
Default collation 字符集默认比较规则
Maxlen 字符集最大占用字节数

比如，utf8字符集（注意这是mysql中的），默认比较规则是 utf8_general_ci，占用最大字节数是3。关于utf8与utf8mb4的区别与渊源就不展开了[手动狗头]

上面的Maxlen属性在MySQL中灰常重要。

列几个重要的：

ascii 1个
latin1 1个
gb2312 2个
gbk 2个
utf8 3个
utf8mb4 4个

MySQL是个勤俭执家的好孩子，能省就省，为了节省存储空间简直丧心病狂。。

另外，你可以不带匹配规则查看你的当前MySQL版本支持多少种字符编码。

查看比较规则

都用过MySQL的 order by 功能吧，那它的比较机制是什么呢？瞎比？

肯定有规则。就叫比较规则吧。

这里注意一点，查询比较规则用的是COLLATION, 而在修改或创建时的关键字是COLLATE，注意区分使用

show collation [like 匹配模式];


mysql> show collation like 'utf8\_%';

+--------------------------+---------+-----+---------+----------+---------+

| Collation                | Charset | Id  | Default | Compiled | Sortlen |

+--------------------------+---------+-----+---------+----------+---------+

| utf8_general_ci          | utf8    |  33 | Yes    | Yes      |      1 |

| utf8_bin                | utf8    |  83 |        | Yes      |      1 |

| utf8_unicode_ci          | utf8    | 192 |        | Yes      |      8 |

....

+--------------------------+---------+-----+---------+----------+---------+

27 rows in set (0.08 sec)

我可以很负责任的说，utf8字符集有27个比较规则。

为什么这么确定？

因为比较规则命名是有讲究的，我们按下划线分开是三段的一个一个来看：

以字符集名称开头，比如utf8，你可以试试utf8mb4、ascii。
第二个是使用语言，当然 general 不是语言，是一种通用规则。
最后一个有点烦，挑几个用得多的说。 ci 不区分大小写，ai 区分大小写，bin 以二进制方式比较。

至于不是这种格式的，那就当我没说~~~

上面一个utf8 有这么多比较规则，用哪个？show collation 输出中有个Default看到没？

字符集与比较规则的级别

总共四个级别，由大到小

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

等一哈，说两个事，先把裤子穿上

查看MySQL中的一些全局变量值：

show variables like 匹配模式;

修改全局变量值（可以修改的情况，有些值改不了或者改了也没用）

第一种方式-命令修改

set variable_name variable_value

第二种方式-配置文件修改

[使用区域名]
variable_name=variable_value

说回这个级别，系统与库级别都有一个变量可以查到，命名也挺好记的：

character_set_server
collation_server
character_set_database
collation_database

带server的都是系统级别，带database的都是库级别


mysql> show variables like 'character_set_server';

+----------------------+-------+

| Variable_name        | Value |

+----------------------+-------+

| character_set_server | utf8  |

+----------------------+-------+

1 row in set, 1 warning (0.00 sec)

mysql>

mysql> show variables like 'collation_server';

+------------------+-----------------+

| Variable_name    | Value          |

+------------------+-----------------+

| collation_server | utf8_general_ci |

+------------------+-----------------+

1 row in set, 1 warning (0.00 sec)

mysql>

mysql> show variables like 'character_set_database';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| character_set_database | utf8  |

+------------------------+-------+

1 row in set, 1 warning (0.00 sec)

mysql>

mysql> show variables like 'collation_database';

+--------------------+-----------------+

| Variable_name      | Value          |

+--------------------+-----------------+

| collation_database | utf8_general_ci |

+--------------------+-----------------+

1 row in set, 1 warning (0.00 sec)

表级别与列级别可以在创建时指定，也可以在创建后修改，这里就不列命令了。

算了，还是列一下修改表 t1 中 col1 列的字符集为utf8mb4的命令


ALTER TABLE t1 MODIFY COLUMN col1 varchar(2) CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

使用规则

采用继承制，没指定就往上找，有点像双亲委派哈
对于系统与库级别，修改字符集或比较规则，另一个都会随之更改
对于表级别与列级别，修改字符集还需要考虑库中数据，比如一个列字符集的Maxlen为3，你要改小，那之前的数据如何处理呢？


mysql> alter table hero modify column name varchar(100) charset ascii collate ascii_general_ci;

ERROR 1366 (HY000): Incorrect string value: '\xE5\xA4\xA9\xE5\xB8\x88' for column 'name' at row 3

除非。。

MySQL乱码

到重点了，激动一会会，让我们一层层脱下，不，揭开乱码的神秘面纱。

乱码根由：加密编码与解密编码不一致。

完了。

先放下刀，没问题，可以再详细点，这是你们逼我的。

各位大爷先来玩个例子呗：

首先计算机只认01串，也就是二进制流，为了视觉体验，我们用十六进制表示二进制流。

中文汉字'黄'，utf8编码是0xe9bb84，如果你以utf8字符集去解析 0xe9bb84 ，那肯定得到的是汉字‘黄’，可是万一万一万一用其他的编码方式呢？比如gbk:

0xe9 大于 127 （0x7f），表示是双字节表示一个字符，0xe9bb 对应的gbk编码集合中是‘榛’
继续 0x84 大于 127 （0x7f），表示是双字节表示一个字符，后面呢？后面没了。

程序输出长这个样子


榛�

后面那个看起来就像是‘乱码’吧，当然前面那个我觉得也是，不然你读一下？

这种解密与加密方式不一致导致的后果，就是人类世界的乱码。

那这么多字符集，难道我们使用的时候就只能用一种吗？

当然不是，可以转的撒，先正确解码再转成其他字符集不就行了：

‘黄’ 的utf8字符编码为 0xe9bb84，使用utf8解码得到‘黄’，再用gbk编码映射得到编码 0xbbc6，这种方式称为字符集转换。

废话说了一堆，那么MySQL的字符集转换怎么玩的呢？

大家都知道，MySQL分服务端与客户端（不知道出门右转），客户端以一种字符集字符串编码后发到服务端，服务端解析后返回客户端。

一般情况下，客服端的字符集就是系统级别的字符集（character_set_server），不过有些工具可能使用自己设置的字符集，使用时需确认。
服务端的字符处理将历经三个甚至四个字符集：

以 character_set_client 字符集解析客户端传入的字符串
使用字符集转换转成 character_set_connection 字符集
判断当前请求相关列的字符集与 character_set_connection 是否一致，若不相同需要再做一次字符集转换
将得到的查询结果以 character_set_results 字符集转换后返回到客户端。

上面提了三个系统变量

character_set_client
character_set_connection
character_set_results

下面命令可以设置三个变量为同一字符集：


set NAMES 字符集;

通常情况下，都是将服务端的三个系统变量设为一致。

完了，才写一篇最简单的部分就这么累，awsl。。

各位，下一篇见。

MySQL -- 字符集与比较规则

前言

正文

概述

查看字符集

查看比较规则

字符集与比较规则的级别

MySQL乱码

猜你喜欢

热点阅读