mysql不同版本的默认字符编码集、存emoji报错问题、utf8与utf8mb4的区别、utf8mb4_unicode_ci和utf8mb4_general_ci_mysql历史版本及编码

前言：
    前段时间项目出现了莫名其妙的报错，在小程序端获取用户信息并存入数据库时发现微信昵称非法了！

    排查之后发现用户的微信昵称中有emojo表情，mysql的utf8编码集无法存入带有emoji表情的微信昵称，所以应该将utf8修改成utf8-mb4。

    但mb4中又有unicode_ci和general_ci等排序规则。在选择时我也产生了困惑，通过彻夜(并不)的查资料之后，总结了这篇文章；

    他们之间又有什么区别和联系呢？mysql 8 中默认的是什么排序方式呢？让我们带着问题进入文章吧！

1. 版本号和编码集的查看方法

     脱离版本号谈字符编码集都是耍流氓，首先我们要学会查看版本号和字符编码集。

1.1 MySQL查看版本号的方法

Linux：

• grep指令查看 - mysql --help | grep Distrib

  [root@heyong tools]# mysql --help | grep Distrib 
  mysql Ver 14.14 Distrib 5.7.30, for xxxxxxxxxxxxxxx...
  1
  2

• status指令查看 - 连接上MySQL加粗样式后执行status

  mysql> status 
  mysql Ver 14.14 Distrib 5.7.30, for xxxxxxxxxxxxxxx...
  ......
  Server version:         5.7.30 MySQL Community Server (GPL)
  ......
  1
  2
  3
  4
  5

• 登录连接时查看

  [root@heyong ~]# mysql -uroot -p 
  Enter password: 
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 874163
  Server version: 5.7.30 MySQL Community Server (GPL)
  ......
  1
  2
  3
  4
  5
  6

Navicat：

• select命令查看 - select version();
  
  
  

1.2 MySQL查看字符集的方法

• 查看默认字符集：show variables like '%charac%';
  (这里以MySQL5.7版本的为例)

mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

• 查看排序规则 - show variables like 'collation_%';

collation_connection	utf8mb4_general_ci
collation_database	utf8_bin
collation_server	utf8_bin
1
2
3

2. 不同版本的默认字符集及解释

2.1 MySQL 5.7 默认字符集

使用命令：show variables like '%charac%';

mysql> show variables like '%charac%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13

2.2 MySQL 8 默认字符集

使用命令：show variables like '%charac%';

mysql> show variables like '%charac%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8                           |
| character_set_connection | utf8                           |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8                           |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.3 MySQL 字符集变量解释

• character_set_client ：默认的内部操作字符集
• character_set_client：客户端来源数据使用的字符集
• character_set_connection：连接层字符集
• character_set_results：查询结果字符集
• character_set_database：当前选中数据库的默认字符集
  决定了新建数据库的默认字符集；数据库的字符集又决定了建表默认字符集；表字符集又决定了字段默认字符集。
  如果没有通过default character set = xxx来改变表字符集，则新表默认使用character_set_database指定的字符集。
• character_set_system：系统元数据(字段名等)字符集
• 还有以collation_开头的同上面对应的变量，用来描述字符序。

     由上总结可以可以发现5.7在建表时默认使用latin1字符集；而8在建表时默认使用utf8mb4字符集。

2.4 utf8 与 utf8mb4(utf8 most bytes 4)

• Mysql5.5.3之后增加了utf8-mb4字符编码集
• 支持BMP(Basic Multilingual Plane，基本多文种平面) 和补充字符
• 最多使用四个字节存储字符

​ utf8mb4 是 utf8 的超集并完全兼容 utf8，能够用四个字节存储更多的字符。标准的utf8字符集编码是可以使用1-4个字节去编码21位字符，这几乎包含了世界上所有能看见的语言。

2.5 MySQL的字符集实现

• utf8：最长使用3个字节，支持到了Unicode中的基本多文本平面（U 0000至U FFFF)，包含控制符、拉丁文、中、日、韩等绝大多数国际字符；不包括emoji和不常用的汉字，如墅等需要四个字节才能编码出来的字符。
• utf8mb4：最长使用4个字节，会多消耗一个字节的空间但是兼容性更好，并且包括emoji表情的uinicode编码范围

2.6 线上报错原因分析

开发时增加新字段的语句：

ADD COLUMN `wechat_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '微信姓名' 
1

在设计微信昵称字段时明明设置了utf8mb4的字符集，但线上却还是报错了，这是为什么呢？
通过查询线上默认字符集、与运维哥哥沟通后找到了答案：

• 第一，线上数据库默认utf8字符集
• 第二，在执行SQL语句时会进行sql审核，在sql审核时不允许由开发人员主动指定字符集
• 第三，当我把SQL语句给同事进行线上执行时，他把后面CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci删掉了！导致数据库使用了默认的utf8字符集

找出问题后，再与运维哥哥交涉一下，修改此字段线上库字符集后问题就完美解决了！

这里肯定会有朋友问了，那你为什么是COLLATE utf8mb4_unicode_ci；我就是COLLATE utf8mb4_general_ci呢？！

3. utf8mb4_unicode_ci 与 utf8mb4_general_ci

utf8mb4_unicode_ci：

• utf8_unicode_ci 校对规则仅部分支持 Unicode 校对规则算法。一些字符还是不能支持。并且，不能完全支持组合的记号。这主要影响越南和俄罗斯的一些少数民族语言（对我们来说可以满足使用），如：Udmurt 、Tatar、Bashkir和Mari。
• 主要的特色是支持扩展，即当把一个字母看作与其它字母组合相等时。例如，在德语和一些其它语言中‘ß’等于‘ss’。
• 准确性 - 基于标准的Unicode来排序和比较，能够在各种语言之间精确排序
• 性能 - 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法()

utf8mb4_general_ci：

• utf8mb4_general_ci 是一个遗留的校对规则，不支持扩展；只能在字符之间进行逐个比较。
• 准确性 - 没有实现Unicode排序规则，在遇到某些特殊语言或字符时，排序结果可能不是所期望的。
• 性能 - 在比较和排序的时候更快。

小总结：

• 准确性 - 大多数情况下，这种特殊字符的排序不一定要那么精确。
  Unicode：会把ß、Œ当做ss和OE来看
  general：会把ß、Œ当做s和e；再入ÀÁÅåāă各自都与A相等
• 性能 - 理论上general比Unicode快些

综上所述，general_ci更快，unicode_ci更准确。

我个人更加推荐utf8mb4_unicode_ci，因为相比CPU来说，它可能不足以成为考虑性能的因素，索引设计、SQL设计才是主战场。