Mysql Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT)

记录一次Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation ‘=’
错误

发现问题

在编写sql语句，进行测试是发现了一个错误

SELECT
            T1.region_id  AS regionId
        FROM
            sys_region T1
                INNER JOIN (
                SELECT
                    @region_id AS _region_id,
				( SELECT @region_id := region_parent_id  FROM sys_region WHERE region_id = _region_id ) AS region_parent_id,
				@sort := @sort + 1 AS sort
                FROM
                    ( SELECT @region_id := '110109007', @sort := 0 ) T2_1,
                    sys_region T2_2
                WHERE
                    @region_id IS NOT NULL
                  AND @region_id != ''
            ) T2 ON T1.region_id = T2._region_id
        ORDER BY
        T2.sort DESC;

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

经过测试，sql语句没有问题，根据错误信息，百度一下，了解到是数据表的编码格式不一致导致

解决方式：重新设置该表的编码方式

  alter table sys_region_1 default character set utf8mb4 collate=utf8mb4_general_ci;

 ALTER TABLE sys_region_1 convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

正确效果

进一步挖掘问题

当前的编码格式 Collation = utf8mb4_bin, 查询是OK的。

当前的编码格式 Collation = utf8mb4_general_ci, 查询是OK的。

当前的编码格式 Collation = utf8mb4_unicode_ci, 查询是NO的。

现在引入了两个问题，utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci三者有什么区别？
第二个问题：utf8mb4_unicode_ci格式，为什么无法正确查询到期望结果，报错的原因是什么？

类型	utf8mb4_bin	utf8mb4_general_ci	utf8mb4_unicode_ci
是否区分大小写	区分	不区分	不区分
排序准确性		基于字符二进制形式进行比较和排序，在某些特殊情况下，会导致不准确的排序结果	使用Unicode字符集进行比较和排序，能够正确处理不同语音之前的排序差异和特殊字符的排序
多语音支持		不支持	支持
性能		快	由于使用Unicode字符集，排序会慢

由于其排序准确性和对多语言支持的优势，一般推荐在需要确切和精确的排序结果，以及多语言支持的情况下使用utf8mb4_unicode_ci排序规则。不过在某些特定场景下，utf8mb4_general_ci可能仍然是合适的选择，例如在不涉及多语言排序要求的简单应用中。

utf8mb4_general_ci和utf8mb4_unicode_ci是两种不同的排序规则（collation）。虽然它们在处理大多数情况下都没有问题，但是在某些特殊情况下，它们之间可能会出现冲突或不兼容的情况。这就是为什么在某些情况下utf8mb4_general_ci可以正常工作，而utf8mb4_unicode_ci会导致错误的原因。

utf8mb4_general_ci是一种更简单的排序规则，它主要基于字符的二进制形式进行比较和排序。它对于大多数情况都能正常工作，但在某些特殊情况下，它可能会导致一些不准确的排序结果或不正常的比较行为
utf8mb4_unicode_ci是一种更复杂的排序规则，它可以处理更广泛的字符集和语言特定的排序要求。它使用Unicode字符集进行比较和排序，能够正确处理不同语言之间的排序差异和特殊字符的排序
因此，当涉及到复杂的字符集、语言特定的排序要求，或者需要准确的排序结果时，utf8mb4_unicode_ci是更可靠和适合的选择。

另一种解决方法

SELECT
    T1.region_id AS regionId
FROM
    sys_region T1
INNER JOIN (
    SELECT
        @region_id AS _region_id,
        (SELECT @region_id := region_parent_id COLLATE utf8mb4_unicode_ci FROM sys_region WHERE region_id = _region_id COLLATE utf8mb4_unicode_ci) AS region_parent_id,
        @sort := @sort + 1 AS sort
    FROM
        (SELECT @region_id := '110109007', @sort := 0) T2_1,
        sys_region T2_2
    WHERE
        @region_id IS NOT NULL
        AND @region_id != ''
    ORDER BY region_id COLLATE utf8mb4_unicode_ci
) T2 ON T1.region_id = T2._region_id COLLATE utf8mb4_unicode_ci
ORDER BY T2.sort DESC;

调整连接条件的字符集和排序规则，使其保持一致。