第06期：梳理 MySQL 字符集的相关概念

于由杨涛涛发布

概念

  • 字符集的内容包含：字符集（character set）和排序规则（collation rule）

  • 每种字符集可对应一到多个排序规则，每种排序规则对应一种字符集

  • 字符集是一套字符与一套编码的映射集合，像这样：


  • 排序规则是字符集内用来比较每个字符的一套规则，也就是字符的排序方式

    比如要比较字符 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

ISO-8859-1 收录的字符除 ASCII 收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。

单字节内的空间都被 ISO-8859-1 编码占用，所以能够用 ISO-8859-1 编码存储、传输其他任何编码的字节流。

比如把一个 Utf8mb4 的编码或者 GBK 的编码存入 Latin1，不会有任何问题。因为 Latin1 保留了原始的字节流，这也就是 MySQL 长期以来把 Latin1 做默认字符集的原因。

但是由于 Latin1 对任何字符都存放字节流，造成了字符个数的浪费。

比如：

  1. CHAR(10) CHARACTER  SET LATIN1;
  2. 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 语句
  1. -- 过滤指定字符集

  3. mysql> show character set where description like '%unicode%' and charset like 'utf8%';
  4. +---------+---------------+--------------------+--------+
  5. | Charset | Description   | Default collation  | Maxlen |
  6. +---------+---------------+--------------------+--------+
  7. | utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
  8. | utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |
  9. +---------+---------------+--------------------+--------+
  10. 2 rows in set (0.01 sec)
2. 查看元数据字典表
  1. -- 过滤指定字符集

  3. mysql> select * from information_schema.character_sets where description like '%Unicode%' and character_set_name like 'utf8%';
  4. +--------------------+----------------------+---------------+--------+
  5. | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
  6. +--------------------+----------------------+---------------+--------+
  7. | utf8               | utf8_general_ci      | UTF-8 Unicode |      3 |
  8. | utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode |      4 |
  9. +--------------------+----------------------+---------------+--------+
  10. 2 rows in set (0.00 sec)

查询结果：

1）第一列代表字符集名字；

2）第二列表示字符集排序规则；

3）第三列表示字符集描述；

4）第四列表示字符集编码的最大字节数。

四、查看排序规则

1. SQL 语句

  1. -- 检索出字符集为 utf8mb4 的默认排序规则
  2. mysql> show collation where charset = 'utf8mb4' and `default` = 'yes';
  3. +--------------------+---------+-----+---------+----------+---------+---------------+
  4. | Collation          | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
  5. +--------------------+---------+-----+---------+----------+---------+---------------+
  6. | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
  7. +--------------------+---------+-----+---------+----------+---------+---------------+
  8. 1 row in set (0.00 sec)
2. 查看元数据字典表
  1. -- 检索出排序规则包含 utf8mb4%_bin 的
  2. mysql> select * from information_schema.collations where collation_name like 'utf8mb4%_bin';
  3. +------------------+--------------------+-----+------------+-------------+---------+---------------+
  4. | COLLATION_NAME   | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
  5. +------------------+--------------------+-----+------------+-------------+---------+---------------+
  6. | utf8mb4_bin      | utf8mb4            |  46 |            | Yes         |       1 | PAD SPACE     |
  7. | utf8mb4_0900_bin | utf8mb4            | 309 |            | Yes         |       1 | NO PAD        |
  8. +------------------+--------------------+-----+------------+-------------+---------+---------------+
  9. 2 rows in set (0.01 sec)

查询结果：

1）第一列代表排序规则名称；

2）第二列表示对应字符集名称；

3）第四列表示是否为默认排序规则；

4）最后一列表示排序时是否需要比较字符后面的空格。

3. NO PAD vs PAD SPACE

NO PAD（处理）

如果字符后面有空格，那就把空格当作一个字符处理。也就是在对比的时候不会忽视空格的存在。

PAD SPACE（忽略）

表示如果字符后面有空格，可以忽略空格来比较。也就是空格可有可无。

示例：

  1. -- 排序规则 utf8mb4_bin 属性为 PAD SPACE。
  2. mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin;
  3. Query OK, 0 rows affected (0.00 sec)

  5. mysql> set @a='mysql      ';
  6. Query OK, 0 rows affected (0.00 sec)

  8. mysql> set @b='mysql';
  9. Query OK, 0 rows affected (0.00 sec)

  11. -- 验证变量 @a 和变量 @b 是否相同，结果为相同，也就是尾部的空格不参与比较。
  12. mysql> select if(@a=@b,' @a 和 @b 相同','@a 和 @b 不同') as '比较结果';
  13. +-------------------+
  14. | 比较结果          |
  15. +-------------------+
  16. |  @a 和 @b 相同    |
  17. +-------------------+
  18. 1 row in set (0.00 sec)

  20. -- 把排序规则变为 utf8mb4_0900_bin，这个属性为 NO PAD。
  21. mysql> SET NAMES utf8mb4 COLLATE utf8mb4_0900_bin;
  22. Query OK, 0 rows affected (0.00 sec)

  24. -- 同样验证上面的结果，结果跟描述相悖。原因在于，@a 和 @b 还保持着之前的排序规则，
  25. mysql> select if(@a=@b,' @a 和 @b 相同','@a 和 @b 不同') as '比较结果';
  26. +-------------------+
  27. | 比较结果          |
  28. +-------------------+
  29. |  @a 和 @b 相同    |
  30. +-------------------+
  31. 1 row in set (0.00 sec)

  33. -- 重新给@a和@b赋值
  34. mysql> set @a='mysql      ';
  35. Query OK, 0 rows affected (0.00 sec)

  37. mysql> set @b='mysql';
  38. Query OK, 0 rows affected (0.00 sec)

  40. -- 再次验证结果，和之前描述一致。
  41. mysql> select if(@a=@b,' @a 和 @b 相同','@a 和 @b 不同') as '比较结果';
  42. +------------------+
  43. | 比较结果         |
  44. +------------------+
  45. | @a 和 @b 不同    |
  46. +------------------+
  47. 1 row in set (0.00 sec)

五、UTF8MB3 与 UTF8MB4 的互相迁移

1. UTF8 到 UTF8MB4 升级

这种顺序可以做到无损迁移，前者就是后者的子集。比如从 MySQL 5.7 到 MySQL 8.0 的字符集升级，这样的场景不会有任何问题。

示例

  1. -- 表 t1 字段 a 字符集 utf8
  2. mysql> create table t1 (a varchar(10) charset utf8);
  3. Query OK, 0 rows affected, 1 warning (0.10 sec)

  5. -- 表 t2 字段 a 字符集 utf8mb4.
  6. mysql> create table t2 (a varchar(10) charset utf8mb4);
  7. Query OK, 0 rows affected (0.05 sec)

  9. mysql> insert into t1 values ('消灭病毒，中国无敌！');
  10. Query OK, 1 row affected (0.01 sec)

  12. -- 表 t1 的数据可以直接插入到 t2。
  13. mysql> insert into t2 select * from t1;
  14. Query OK, 1 row affected (0.02 sec)
  15. Records: 1  Duplicates: 0  Warnings: 0

  17. mysql> select * from  t2;
  18. +--------------------------------+
  19. | a                              |
  20. +--------------------------------+
  21. | 消灭病毒，中国无敌！|
  22. +--------------------------------+
  23. 1 row in set (0.00 sec)

2. UTF8MB4 到 UTF8 兼容

这种相当于降级模式，如果 utf8mb4 包含的字符没有超出了 utf8 的范围，则可以顺序进行；否则失败。

  1. -- t2 的字段 a 虽然是 utf8mb4，但是包含的字符没有超出 utf8 的范围，所以可以顺利的进行
  2. mysql> truncate t1;
  3. Query OK, 0 rows affected (0.09 sec)

  5. mysql> insert into t1 select * from t2;
  6. Query OK, 1 row affected (0.02 sec)
  7. Records: 1  Duplicates: 0  Warnings: 0

  9. mysql> select * from t1;
  10. +--------------------------------+
  11. | a                              |
  12. +--------------------------------+
  13. | 消灭病毒，中国无敌！|
  14. +--------------------------------+
  15. 1 row in set (0.01 sec)

  17. -- 清空表 t1,t2,
  18. mysql> truncate table t2;
  19. Query OK, 0 rows affected (0.07 sec)

  21. mysql> truncate t1;
  22. Query OK, 0 rows affected (0.10 sec)

  24. -- 插入 EMOJI 表情字符'哈哈 🍀🍎💰📱'
  25. mysql> insert into t2 values ('哈哈🍀🍎💰📱');
  26. Query OK, 1 row affected (0.01 sec)

  28. -- 这些字符不包含在 utf8mb3 中，所以插入报错。
  29. mysql> insert into t1 select * from t2;
  30. ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\x80\xF0\x9F...' 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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

分类： MySQL 调优
标签：

相关文章

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句 阅读更多…

MySQL 调优

第44期：无主键分区表该不该使用

本来想着分区表在上一篇后就不续写了，最近又有同学咨询我分区表的新问题： 阅读更多…

MySQL 调优

第43期：多表关联场景下如何用好分区表

如何在多表关联场景下合理利用分区表来提升查询性能？基于前几篇关于分区表 阅读更多…