MySql Illegal mix of collations问题解决

错误

sql:

SELECT COUNT( *) FROM .`sys_user` u 
left join `stu_student` ss on ss.id = u.`student_id` and ss.`del_flag` = '0'
WHERE u.`tenant_id`  = 55 and u.`username` != ss.`login_num`

Illegal mix of collations (utf8mb4_0900_as_cs,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation ‘<>’

原因

因为两张表的采取的编码不一

解决方案

1.修改表的排序规则
2.在 ‘=’ 或 ‘like’ 前后添加 COLLATE utf8mb4_unicode_ci

SELECT COUNT( *) FROM `ykxtx`.`sys_user` u 
left join `stu_student` ss on ss.id = u.`student_id` and ss.`del_flag` = '0'
WHERE u.`tenant_id`  = 55 and u.`username` != ss.`login_num`  COLLATE utf8mb4_unicode_ci