第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 调优

第46期：EXPLAIN：解说一条简单 SQL 语句的执行计划

在MySQL里，当通过各种手段抓取到影响系统性能的慢SQL语句后，此刻 阅读更多…

MySQL 调优

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

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

MySQL 调优

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

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