ILLEGAL MIX OF COLLATIONS (UTF8MB4_0900_AI_CI,IMPLICIT) AND (UTF8MB4_GENERAL_CI,IMPLICIT) 错误解决
写了一个查询所有上级的sql
-
SELECT
-
T0.*,T1.lower_count,T1.aicp,T1.user_level
-
FROM
-
(
-
SELECT
-
@r AS _id,
-
(
-
SELECT
-
@r := invite_user
-
FROM
-
fa_user
-
WHERE
-
id = _id
-
) AS invite_user1,
-
@l := @l + 1 AS lvl
-
FROM
-
(SELECT @r := '用户id', @l := 0) vars,
-
fa_user h
-
WHERE
-
@r <> ''
-
) T0
-
JOIN fa_user T1 ON T0._id = T1.id
报错SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=',。MySQL 8.0.20
我看了下数据库的字符集排序规则
表的字符集排序规则
表字段的字符集排序规则
解决方法:在my.cnf中也做相同编码的配置
-
[client]
-
default-character-set = utf8mb4
-
-
[mysqld]
-
character-set-client-handshake = FALSE
-
character_set_server = utf8mb4
-
collation-server = utf8mb4_0900_ai_ci
-
如下图
进入mysql执行 show variables where Variable_name like 'collation%';查看