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

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation ‘=’

| username: 大飞飞呀

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Reproduction Path] What operations were performed to cause the issue

select base.I_DATE,       -- uniq
        base.CH_DEPT_NAME, -- uniq
       ifnull(acci.I_FMS_COUNT, 0)     as I_FMS_COUNT,
        ifnull(comm.I_COMMENT_COUNT, 0) as I_COMMENT_COUNT,
        ifnull(comm.I_USER_COUNT, 0)    as I_USER_COUNT,
        ifnull(comm.I_ENTER_COUNT, 0)   as I_ENTER_COUNT、
         from (select I_MONTH as I_DATE, CH_DEPT_NAME
       from (select distinct I_MONTH from stat_date_info where I_DATE >= 20210301) a
                join (select 'a组' as CH_DEPT_NAME
                      union
                      select 'b组' as CH_DEPT_NAME
                      union
                     select 'c组' as CH_DEPT_NAME
                      union
                      select 'd组' as CH_DEPT_NAME
                      union
                      select 'e组' as CH_DEPT_NAME
                      union
                      select '总计' as CH_DEPT_NAME) b) base
          left join (select I_DATE, CH_DEPT_NAME, I_COMMENT_COUNT, I_USER_COUNT, I_ENTER_COUNT
                     from dws_biz_service_dept_comment_cnt_mdi
                     union                     select I_DATE,
                            '总计',
                            sum(I_COMMENT_COUNT) as I_COMMENT_COUNT,
                            sum(I_USER_COUNT)    as I_USER_COUNT,
                            sum(I_ENTER_COUNT)   as I_ENTER_COUNT
                     from dws_biz_service_dept_comment_cnt_mdi
                     group by I_DATE) comm on base.I_DATE = comm.I_DATE and base.CH_DEPT_NAME = comm.CH_DEPT_NAME
          left join (select I_DATE, CH_DEPT_NAME, I_FMS_COUNT
                     from dws_biz_service_dept_accident_cnt_mdi
                     union
                     select I_DATE,
                            '总计',
                            sum(I_FMS_COUNT) as I_FMS_COUNT
                     from dws_biz_service_dept_accident_cnt_mdi
                     group by I_DATE) acci on base.I_DATE = acci.I_DATE and base.CH_DEPT_NAME = acci.CH_DEPT_NAME

Tables dws_biz_service_dept_accident_cnt_mdi COLLATE=utf8mb4_unicode_ci
Tables dws_biz_service_dept_comment_cnt_mdi COLLATE=utf8mb4_unicode_ci
[Encountered Issue: Issue Phenomenon and Impact]
The above SQL, when executed using Go code, reports an error

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

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

2
| username: Billmay表妹 | Original post link

Character set issue

3
| username: 大飞飞呀 | Original post link

How to solve it?

4
| username: 大飞飞呀 | Original post link

I did a version upgrade from 5 to 7, and then this problem occurred.

5
| username: tidb菜鸟一只 | Original post link

Check what the collation of the field is.

6
| username: ajin0514 | Original post link

Take a look at the character set and collation.