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

sql查询时报错：

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'

或

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

 

初步判断，数据库链接的编码 和 数据库sever的编码不同导致

查看当前数据库默认编码：
show variables where Variable_name like ‘collation%’;

Variable_name	Value
collation_connection	utf8mb4_unicode_ci
collation_database	utf8mb4_general_ci
collation_server	utf8mb4_general_ci

 

如果不想改数据库连接的编码，可以使用如下方法：

在 ‘=’ 或 ‘like’ 前后添加    COLLATE utf8mb4_unicode_ci

例如：

SELECT
    d.a , d.a , d.*
FROM
    (
        SELECT
            @num :=
        IF (
            @type = p.a COLLATE utf8mb4_unicode_ci and @type2 = p.b COLLATE utf8mb4_unicode_ci,
            @num + 1,
            1
        ) AS row_number,
        @type := p.a AS large,
        @type2 := p.b  as middle,
        p.*
    FROM
        entProduct p,
        (SELECT @num := 0, @type := '', @type2 := '') notuse
    WHERE
        p.entId = 12  COLLATE utf8mb4_unicode_ci
    AND p.deleted = 0  COLLATE utf8mb4_unicode_ci
    ORDER BY
        p.a ASC,
        p.b ASC
    ) d
WHERE
    d.row_number <= 4;

或  COLLATE  utf8mb4_general_ci

 

只要确保 查询参数的编码统一即可