文章来源：
https://chenchenchen.blog.csdn.net/article/details/109291324?spm=1001.2014.3001.5506
https://zhuanlan.zhihu.com/p/534492938

一、存储字符集 utf8 和 utf8mb4

utf8 是 Mysql 中的一种字符集，只支持最长三个字节的 UTF-8 字符，也就是 Unicode 中的基本多文本平面。

mb4的意思是：Most Bytes 4，可以兼容unicode。

而utf8最多支持3个字节，比如3个字节的uft8无法支持Emoji表情和不常用的汉字，以及任何新增的Unicode字符等，因此才引入了uft8mb4。

要在 Mysql 中保存 4 字节长度的 UTF-8 字符，就要使用 utf8mb4 字符集，但只有 5.5.3 版本以后的才支持。我觉得，为了获取更好的兼容性，应该总是使用 utf8mb4 而非 utf8. 对于 CHAR 类型数据，utf8mb4 会多消耗一些空间，根据 Mysql 官方建议，使用 VARCHAR 替代 CHAR。

如果数据库默认字符集不是 utf8mb4，那么可以在创建数据库时指定字符集：

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看编码格式:

mysql> show variables like "%char%";
+--------------------------------------+--------------------------------+
| Variable_name            | Value             |
+--------------------------------------+--------------------------------+
| character_set_client         | utf8mb4            |
| character_set_connection       | utf8mb4            |
| character_set_database        | utf8mb4            |
| character_set_filesystem       | binary             |
| character_set_results        | utf8mb4            |
| character_set_server         | utf8mb4            |
| character_set_system         | utf8              |
| character_sets_dir          | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1               |
+--------------------------------------+--------------------------------+
9 rows in set (0.00 sec)

MySQL 配置文件中字符集相关变量:

  • character_set_client：客户端请求数据的字符集
  • character_set_connection：从客户端接收到数据，然后传输的字符集
  • character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，那就使用 character_set_server 指定的字符集，这个变量建议由系统自己管理，不要人为定义。
  • character_set_filesystem：把操作系统上的文件名转化成此字符集，即把 character_set_client 转换 character_set_filesystem， 默认 binary 是不做任何转换的
  • character_set_results：结果集的字符集
  • character_set_server：数据库服务器的默认字符集
  • character_set_system：存储系统元数据的字符集，总是 utf8，不需要设置

二、排序字符集

utf8mb4_unicode_ci 和 utf8mb4_general_ci

1、准确性

utf8mb4_unicode_ci 是基于标准的 Unicode 来排序和比较，支持所有Unicode字符的精确排序，能够在各种语言之间精确排序

utf8mb4_general_ci 没有实现 Unicode 排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。

但是绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

2、性能

utf8mb4_general_ci 在比较和排序的时候更快

utf8mb4_unicode_ci 在特殊情况下，Unicode 排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

但是在绝大多数情况下，不会发生此类复杂比较。相比选择哪一种 collation，使用者更应该关心字符集与排序规则在 db 里需要统一。

utf8mb4_0900_ai_ci

推荐用 utf8mb4_unicode_ci，但是用 utf8mb4_general_ci 也没啥问题。

MySQL 8.0 默认的是 utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci 中的一种，具体含义如下：

  • uft8mb4 表示用 UTF-8 编码方案，每个字符最多占 4 个字节。
  • 0900 指的是 Unicode 校对算法版本。（Unicode 归类算法是用于比较符合 Unicode 标准要求的两个 Unicode 字符串的方法）。
  • ai 指的是口音不敏感。也就是说，排序时 e，è，é，ê 和 ë 之间没有区别。
  • ci 表示不区分大小写。也就是说，排序时 p 和 P 之间没有区别。

utf8mb4 已成为默认字符集，在 MySQL 8.0.1 及更高版本中将 utf8mb4_0900_ai_ci 作为默认排序规则。以前，utf8mb4_general_ci 是默认排序规则。由于 utf8mb4_0900_ai_ci 排序规则现在是默认排序规则，因此默认情况下新表格可以存储基本多语言平面之外的字符。现在可以默认存储表情符号。如果需要重音灵敏度和区分大小写，则可以使用 utf8mb4_0900_as_cs 代替。


三、排序规则冲突

执行查询语句：

SELECT * FROM table1 t1 
left join table2 on t1.username = t2.username

报错如下：

Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

问题是排序规则冲突引起的：

关联字段的编码字符集均为utf8mb4，但是t1.username的排序规则是utf8mb4_general_ci，而t2.username的排序规则是utf8mb4_unicode_ci

四、排序规则不兼容的解决方案

第一种，修改表字段的格式一致

即修改表的排序规则统一为utf8mb4_unicode_ci，或者另一种。官方更推荐使用utf8mb4_unicode_ci的排序规则，借用StackOverflow上的一段话：

There is almost certainly no reason to use utf8mb4_general_ci anymore, as we have left behind the point where CPU speed is low enough that the performance difference would be important. Your database will almost certainly be limited by other bottlenecks than this.

大概意思是说，当前CPU的运行速度已经快到可以让我们不再将此排序作为一个考虑参数，而更应该开了其他开销。

第二种，借助关键字COLLATE

通过COLLATE属性，可以指定列的排序和比较方式。

在使用时，将它放在关联查询需要修改排序规则的地方：

SELECT
	u.guid
FROM
	`test`  t
LEFT JOIN user u ON u.guid = t.guid COLLATE utf8mb4_unicode_ci
WHERE t.state = 1

如此，使用COLLATE属性，可以让使用不同排序规则的字段进行关联查询

但是，经过测试，这样会减慢SQL查询的速度

具体采用哪种方式，需要细细考量