MySQL 查询遇到Illegal mix of collations的错误

业务同学线上业务执行 SQL 时报错，

### Error querying database.  Cause: java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,COERCIBLE) for operation 'like'

含义是对'like'操作非法混合了排序规则(utf8mb4_general_ci,IMPLICIT)和(utf8mb4_0900_ai_ci,COERCIBLE) ，所以大致猜想应该是 collation(排序规则）设置的问题。

业务执行出问题的查询语句为：

select * from fruits
where name like concat('%','爽爆🍋','%')

可以看到，这个语句中，用到了一个表情🍋，再看这张表的排序规则是 utf8mb4_general_ci 。

那就解释通了，原因是utf8mb4_general_ci对于表情的支持不是很好，当 where 条件中使用表情时，可能会出现非预期的结果，上述的例子就是其中一种。还有比较著名的 “sushi = beer”的问题：https://bugs.mysql.com/bug.php?id=76553,也是一个场景。

那么这个问题的解决方案就是：

使用 utf8mb4_0900_ai_ci 替换utf8mb4_general_ci

在 MySQL 8.0 中，默认字符集已从 latin1 更改为 ut8mb4。默认排序规则是 utf8mb4_0900_ai_ci，这也意味着 utf8mb4_0900*是更被推荐的。

所以 utf8mb4_0900_ai_ci 是什么呢？

首先我们要明白数据库中字符集和排序规则的含义。

字符集和排序规则

字符集(Character Set)是一组编码规则，用于将字符映射到数字编码。它定义了一个字符集合，并为每个字符分配要给唯一的编码值。常见的字符集包括 ASCII、UTF-8、UTF-16、GBK 等。

排序规则(collation): 定义了字符在排序和比较操作中的规则。它决定了字符之间的顺序、大小写敏感、重音符号等。排序规则是基于字符集的，同一个字符集可以有多个排序规则可供选择。例如，对于 utf-8 字符集来说，常见的排序规则有：utf8_general_ci、utf8_bin、utf8_0900_ai_ci。对于 utf8mb4 字符集来说，对应的排序规则则是：utf8mb4_general_ci、utf8mb4_bin、utf8mb4_0900_ai_ci。

扩展一下

Unicode和UTF-8有何区别？

Unicode和UTF-8是处理字符编码的两个相关概念，它们有以下区别：

定义和范围：Unicode是一个字符集，它定义了每个字符的唯一编码点。它为世界上几乎所有的字符分配了一个唯一的编码值，包括字母、数字、标点符号、符号、表情符号等。UTF-8是一种用于表示Unicode字符的编码方案，它定义了如何将Unicode字符编码为字节序列。

编码方式：Unicode使用固定的编码点来表示字符，每个字符都有一个唯一的编码值。UTF-8是一种可变长度的编码方案，它使用1到4个字节来表示不同的Unicode字符。根据字符的范围，UTF-8使用不同长度的字节序列来编码字符，从而实现更高的存储效率。

存储效率：由于UTF-8采用可变长度编码，它可以根据字符的范围选择适当长度的字节序列进行编码。对于只包含ASCII字符（0-127）的文本，UTF-8使用单个字节表示每个字符，与ASCII编码完全兼容，因此具有较高的存储效率。对于非ASCII字符，UTF-8使用多个字节表示，根据字符的范围选择适当长度的字节序列。

兼容性：Unicode是一个字符集，不依赖于特定的存储方式。UTF-8是Unicode的一种编码方案，它可以表示Unicode字符。UTF-8是一种广泛使用的编码方案，可以在各种计算机系统和应用程序之间进行交换和共享文本数据。

简单点来说就是Unicode只定义了字符集，但是UTF-8定义了编码格式。UTF的全称是Unicode Transformation Format，UTF-8则是它其中一个变换格式，还同时定义了编码格式（可变长度编码），此外还有UTF-16、UTF-32等等。

utf8和utf8mb4有何区别？

在MySQL数据库中，UTF-8被实现为一种最多使用3个字节的编码方式，被称为"utf8"。而utf8mb4使用最多4个字节来表示不同范围的Unicode字符。

在MySQL数据库中，"utf8"字符集实际上只支持部分的UTF-8编码范围，而"utf8mb4"字符集支持完整的UTF-8编码范围。因此，如果需要存储包括表情符号在内的广泛字符集，应该选择使用"utf8mb4"字符集。

回到正题，相比于旧的编码方式，utf8mb4_0900_ai_ci具有更好的排序规则和比较性能。它是基于Unicode Collation Algorithm (UCA) 的一种改进版本，能够更准确地排序和比较Unicode字符。

其中，utf8mb4代表字符集，0900代表是Unicode 9000规范，ai代表即不区分重音符号，ci代表不区分大小写。

新版本（8.0以上版本）中推荐使用utf8mb4_0900_ai_ci。

参考：https://www.lifesailor.me/archives/2676.html
https://www.modb.pro/db/100485

点个“赞 or 在看” 你最好看！

喜欢，就关注我吧！