![9f4537baa5dd2d7a86a9c1a5470d0b6b.png](https://i-blog.csdnimg.cn/blog_migrate/f16fc7758d3f89b4011b1a6c35a73b89.jpeg)
概念
- 字符集的内容包含:字符集(character set)和排序规则(collation rule)
- 每种字符集可对应一到多个排序规则,每种排序规则对应一种字符集
- 字符集是一套字符与一套编码的映射集合,像这样:
![f5b98db4e616d700fd1707b535f4e71c.png](https://i-blog.csdnimg.cn/blog_migrate/e0a896b46e6a4b7ca1450f6c3bb7622a.png)
- 排序规则是字符集内用来比较每个字符的一套规则,也就是字符的排序方式
比如要比较字符 A 和 B 的大小,最简单直观的方法就是对比他们对应的编码。显然编码 0 < 1,这种规则下 A < B。那么类似这样的规则集合就是排序规则。单字节字符编码如此,多字节的编码排序也以此类推。
那么接下来我来详细介绍下字符集相关的介绍以及使用场景。
一、字符编码的分类
1. ASCII
用途:用来映射简单的单字节字符,比如大小写英文字母、阿拉伯数字、常用的标点符、运算符、控制字符等。
编码范围:U+0000 - U+007F
注意:对于用这类字符的场景够用了,但是却无法表达比如汉字,日文等编码。
2. UNICODE
用途:用来映射包含 ASCII 以内的其他的所有字符。
编码范围:U+0000 - U+10FFFF
注意:ASCII 是 UNICODE 的子集,ASCII 编码的字符可以无损转换为 UNICODE 编码的字符。
二、MySQL 常用字符集
1. Latin1
Latin1 是 cp1252 或者 ISO-8859-1 的别名。ISO-8859-1 编码是单字节编码,向下兼容 ASCII。
编码范围:U+0000 - U+00FF
![0b9f2420659939befc1831a7d1747133.png](https://i-blog.csdnimg.cn/blog_migrate/46ba91f51e5fefdf7873b50cc6303a7a.png)
ISO-8859-1 收录的字符除 ASCII 收录的字符外,还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。
单字节内的空间都被 ISO-8859-1 编码占用,所以能够用 ISO-8859-1 编码存储、传输其他任何编码的字节流。
比如把一个 Utf8mb4 的编码或者 GBK 的编码存入 Latin1,不会有任何问题。因为 Latin1 保留了原始的字节流,这也就是 MySQL 长期以来把 Latin1 做默认字符集的原因。
但是由于 Latin1 对任何字符都存放字节流,造成了字符个数的浪费。
比如:
CHAR(10) CHARACTER SET LATIN1;
CHAR(10) CHARACTER SET UTF8;
该字段中存储字符个数 UTF8 是 Latin1 的三倍!!!
2. GB18030
GB18030 是中国官方标准字符集,向前兼容 GBK、GB2312,是这两个的超集。用 1、2、4 个字节分别表示一个符号。比如对一般中文字符,默认是用两个字节编码存储。Windows 系统,默认用的就是 GB18030。
若只是存储中文字符,那 GB18030 最佳。
原因有两点:
1)占用空间小,比如比 UTF8 小。
2)存储的汉字根据拼音来排序,检索快。
3. UTF8
UTF8 是 Unicode 的编码实现,可以存储 UNICODE 编码对应的任何字符, 这也是使用最多的一种编码。最大的特点就是变长的编码方式,用 1 到 4 个字节表示一个符号,可以根据不同的符号编码字节长度。
字母或数字用 1 字节,汉字用 3 字节,emoji 表情符号用 4 字节。UTF8 字符集目前是使用最广泛的。
注意!MySQL 里常说的 UTF8 是 UTF8MB3 的别名,UTF8MB3 是 UTF8MB4 的子集,UTF8MB4 才是真正的 4 字节 UTF8 字符集!
UTF8MB3 表示最大支持 3 个字节存储字符,UTF8MB4 表示最大 4 个字节存储字符。根据实际需要和未来展望,MySQL 8.0 已经默认用 UTF8MB4 基础字符集。
三、查看字符集
基本上现在的字符集 MySQL 都支持,查看 MySQL 支持的字符集列表, 有两种方法:
1. SQL 语句
-- 过滤指定字符集
mysql> show character set where description like '%unicode%' and charset like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+---------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci | 4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.01 sec)
2. 查看元数据字典表
-- 过滤指定字符集
mysql> select * from information_schema.character_sets where description like '%Unicode%' and character_set_name like 'utf8%';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |
| utf8mb4 | utf8mb4_0900_ai_ci | UTF-8 Unicode | 4 |
+--------------------+----------------------+---------------+--------+
2 rows in set (0.00 sec)
查询结果:
1)第一列代表字符集名字;
2)第二列表示字符集排序规则;
3)第三列表示字符集描述;
4)第四列表示字符集编码的最大字节数。
四、查看排序规则
1. SQL 语句
-- 检索出字符集为 utf8mb4 的默认排序规则
mysql> show collation where charset = 'utf8mb4' and `default` = 'yes';
+--------------------+---------+-----+---------+----------+---------+---------------+
| Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
+--------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD |
+--------------------+---------+-----+---------+----------+---------+---------------+
1 row in set (0.00 sec)
2. 查看元数据字典表
-- 检索出排序规则包含 utf8mb4%_bin 的
mysql> select * from information_schema.collations where collation_name like 'utf8mb4%_bin';
+------------------+--------------------+-----+------------+-------------+---------+---------------+
| COLLATION_NAME | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
+------------------+--------------------+-----+------------+-------------+---------+---------------+
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE |
| utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD |
+------------------+--------------------+-----+------------+-------------+---------+---------------+
2 rows in set (0.01 sec)
查询结果:
1)第一列代表排序规则名称;
2)第二列表示对应字符集名称;
3)第四列表示是否为默认排序规则;
4)最后一列表示排序时是否需要比较字符后面的空格。
3. NO PAD vs PAD SPACE
NO PAD(处理)
如果字符后面有空格,那就把空格当作一个字符处理。也就是在对比的时候不会忽视空格的存在。
PAD SPACE(忽略)
表示如果字符后面有空格,可以忽略空格来比较。也就是空格可有可无。
示例:
-- 排序规则 utf8mb4_bin 属性为 PAD SPACE。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected (0.00 sec)
mysql> set @a='mysql ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @b='mysql';
Query OK, 0 rows affected (0.00 sec)
-- 验证变量 @a 和变量 @b 是否相同,结果为相同,也就是尾部的空格不参与比较。
mysql> select if(@a=@b,' @a 和 @b 相同','@a 和 @b 不同') as '比较结果';
+-------------------+
| 比较结果 |
+-------------------+
| @a 和 @b 相同 |
+-------------------+
1 row in set (0.00 sec)
-- 把排序规则变为 utf8mb4_0900_bin,这个属性为 NO PAD。
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
Query OK, 0 rows affected (0.00 sec)
-- 同样验证上面的结果,结果跟描述相悖。原因在于,@a 和 @b 还保持着之前的排序规则,
mysql> select if(@a=@b,' @a 和 @b 相同','@a 和 @b 不同') as '比较结果';
+-------------------+
| 比较结果 |
+-------------------+
| @a 和 @b 相同 |
+-------------------+
1 row in set (0.00 sec)
-- 重新给@a和@b赋值
mysql> set @a='mysql ';
Query OK, 0 rows affected (0.00 sec)
mysql> set @b='mysql';
Query OK, 0 rows affected (0.00 sec)
-- 再次验证结果,和之前描述一致。
mysql> select if(@a=@b,' @a 和 @b 相同','@a 和 @b 不同') as '比较结果';
+------------------+
| 比较结果 |
+------------------+
| @a 和 @b 不同 |
+------------------+
1 row in set (0.00 sec)
五、UTF8MB3 与 UTF8MB4 的互相迁移
1. UTF8 到 UTF8MB4 升级
这种顺序可以做到无损迁移,前者就是后者的子集。比如从 MySQL 5.7 到 MySQL 8.0 的字符集升级,这样的场景不会有任何问题。
示例
-- 表 t1 字段 a 字符集 utf8
mysql> create table t1 (a varchar(10) charset utf8);
Query OK, 0 rows affected, 1 warning (0.10 sec)
-- 表 t2 字段 a 字符集 utf8mb4.
mysql> create table t2 (a varchar(10) charset utf8mb4);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into t1 values ('消灭病毒,中国无敌!');
Query OK, 1 row affected (0.01 sec)
-- 表 t1 的数据可以直接插入到 t2。
mysql> insert into t2 select * from t1;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t2;
+--------------------------------+
| a |
+--------------------------------+
| 消灭病毒,中国无敌!|
+--------------------------------+
1 row in set (0.00 sec)
2. UTF8MB4 到 UTF8 兼容
这种相当于降级模式,如果 utf8mb4 包含的字符没有超出了 utf8 的范围,则可以顺序进行;否则失败。
-- t2 的字段 a 虽然是 utf8mb4,但是包含的字符没有超出 utf8 的范围,所以可以顺利的进行
mysql> truncate t1;
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t1 select * from t2;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+--------------------------------+
| a |
+--------------------------------+
| 消灭病毒,中国无敌!|
+--------------------------------+
1 row in set (0.01 sec)
-- 清空表 t1,t2,
mysql> truncate table t2;
Query OK, 0 rows affected (0.07 sec)
mysql> truncate t1;
Query OK, 0 rows affected (0.10 sec)
-- 插入 EMOJI 表情字符'哈哈 '
mysql> insert into t2 values ('哈哈 ');
Query OK, 1 row affected (0.01 sec)
-- 这些字符不包含在 utf8mb3 中,所以插入报错。
mysql> insert into t1 select * from t2;
ERROR 1366 (HY000): Incorrect string value: 'xF0x9Fx8Dx80xF0x9F...' for column 'a' at row 1
六、字符集系统参数
MySQL 字符集涉及到的参数有以下几个:1. MySQL 服务层以下两个设置 MySQL 服务层字符集和排序规则,代表 MySQL 服务启动后,默认的字符集和排序规则。character_set_server:服务层默认字符集编码collation_server:服务层默认排序规则
2. 客户端层
character_set_client:设置客户端的字符集。对任何可以连接到 MySQL 服务端的客户端生效。
3. 数据库层
character_set_database:设置创建新数据库时默认的字符集collation_database:设置创建新数据库时默认排序规则名称
4. 元数据层
数据库名,表名,列名,用户名等。character_set_system: MySQL 元数据默认的字符集,目前不可设置,固定为 UTF8。
5. 结果集层
character_set_results:设置从服务端发送数据到客户端的字符集。包括查询结果,错误信息输出等。
6. 连接层
character_set_connection:设置客户端发送请求到服务端,但是服务端还没有接受之前的数据编码。比如普通字符串,或者已经写好的 SQL 语句但还没有执行。collation_connection: 连接层的排序规则。
7. 文件系统层
character_set_filesystem:设置语句中涉及到的文件名字字符集。比如 load dataintotable t1'/tmp/t1.txt';
这里代表文件名字 /tmp/t1.txt 是以何种编码被 MySQL 解析。客户端层、连接层、结果集层,这三层一般都是一起设置。比如 setnames utf8;
同时设置这三个层次的参数;服务层一定得选择好对应的编码,否则可能会造成接下来的表、字段、存储过程等默认字符集不正确,产生字符集升级。如果兼容还好,不兼容就可能出现乱码或者其他的错误。
总结
那关于 MySQL 字符集的概念大致就介绍到此。简单总结下本篇,本篇主要介绍字符集相关基本概念以及 MySQL 字符集相关参数大致情况,并且举例说明 UTF8MB3 和 UTF8MB4 的相互转换注意事项,希望对大家有帮助。
关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!
![fae602beb0214462dfacb447f6bbff31.png](https://i-blog.csdnimg.cn/blog_migrate/66dfe1855c603e9dab740004fff64afb.jpeg)