mysql使用union all时字符集不一致

mysql 使用union all时碰到的一个奇怪现象：

问题描述

写了一个稍微有一点复杂的sql：先根据where条件查询出结果集，然后union all结果集中的各个列的sum。简单来说，就是在结果的最后一行加上一个总和。只不过这一步是放在sql中执行，而不是程序
然后返回让两个union all 前后的内容交换就报错：

Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (gbk_chinese_ci,IMPLICIT) for operation

原来能正确执行的sql：

                  SELECT gbk_office                                                               as office,
                         leader_name   ,
                         leader_id                                                                as leaderId,
                         count(DISTINCT (opp.id))                                                 as sjzs,
                         count(DISTINCT (case when foll.id is null then null else opp.id end))    as ygjsj,
                         count(DISTINCT (foll.creator_id))                                        as gjrs,
                         COUNT(foll.id)                                                            as gjjls,
                         max(opp.last_follow_date)                                                as zhgjsj,
                         COUNT(DISTINCT (case when foll.type = 1 then opp.id else null end))      as smbfs,
                         COUNT(DISTINCT (case when opp.stage_id = 2 then opp.id else null end))    as xsjd,
                         COUNT(DISTINCT (case when opp.stage_id = 3 then opp.id else null end))    as yxjd,
                         COUNT(DISTINCT (case when opp.stage_id = 4 then opp.id else null end))     as bfjd,
                         COUNT(DISTINCT (case when opp.stage_id = 5 then opp.id else null end))    as swjd,
                         COUNT(DISTINCT (case when opp.stage_id = 6 then opp.id else null end))    as wcqys
                  from channel_partner_opportunity opp
                           LEFT JOIN channel_partner_opportunity_follow foll
                                     on opp.id = foll.cpo_id
                  GROUP BY gbk_office, leader_name
union all
                  select '合计'    as office,
                         null        as leader_name,
                         null        as leaderId,
                         sum(sjzs)   as sjzs,
                         sum(ygjsj)  as ygjsj,
                         sum(gjrs)   as gjrs,
                         sum(gjjls)  as gjjls,
                         max(zhgjsj) as zhgjsj,
                         sum(smbfs)  as smbfs,
                         sum(xsjd)   as xsjd,
                         sum(yxjd)   as yxjd,
                         sum(bfjd)   as bfjd,
                         sum(swjd)   as swjd,
                         sum(wcqys)  as wcqys
                  from (
                           SELECT gbk_office                                                             as office,
                                  leader_name,
                                  leader_id                                                              as leaderId,
                                  count(DISTINCT (opp.id))                                               as sjzs,
                                  count(DISTINCT (case when foll.id is null then null else opp.id end))  as ygjsj,
                                  count(DISTINCT (foll.creator_id))                                      as gjrs,
                                  COUNT(foll.id)                                                         as gjjls,
                                  max(opp.last_follow_date)                                              as zhgjsj,
                                  COUNT(DISTINCT (case when foll.type = 1 then opp.id else null end))    as smbfs,
                                  COUNT(DISTINCT (case when opp.stage_id = 2 then opp.id else null end)) as xsjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 3 then opp.id else null end)) as yxjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 4 then opp.id else null end)) as bfjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 5 then opp.id else null end)) as swjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 6 then opp.id else null end)) as wcqys
                           from channel_partner_opportunity opp
                                    LEFT JOIN channel_partner_opportunity_follow foll
                                              on opp.id = foll.cpo_id
                           GROUP BY gbk_office, leader_name
                       ) tableA

修改后的sql（仅交换了union all 前后的内容位置）：

                  select '合计'    as office,
                         null        as leader_name,
                         null        as leaderId,
                         sum(sjzs)   as sjzs,
                         sum(ygjsj)  as ygjsj,
                         sum(gjrs)   as gjrs,
                         sum(gjjls)  as gjjls,
                         max(zhgjsj) as zhgjsj,
                         sum(smbfs)  as smbfs,
                         sum(xsjd)   as xsjd,
                         sum(yxjd)   as yxjd,
                         sum(bfjd)   as bfjd,
                         sum(swjd)   as swjd,
                         sum(wcqys)  as wcqys
                  from (
                           SELECT gbk_office                                                             as office,
                                  leader_name,
                                  leader_id                                                              as leaderId,
                                  count(DISTINCT (opp.id))                                               as sjzs,
                                  count(DISTINCT (case when foll.id is null then null else opp.id end))  as ygjsj,
                                  count(DISTINCT (foll.creator_id))                                      as gjrs,
                                  COUNT(foll.id)                                                         as gjjls,
                                  max(opp.last_follow_date)                                              as zhgjsj,
                                  COUNT(DISTINCT (case when foll.type = 1 then opp.id else null end))    as smbfs,
                                  COUNT(DISTINCT (case when opp.stage_id = 2 then opp.id else null end)) as xsjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 3 then opp.id else null end)) as yxjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 4 then opp.id else null end)) as bfjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 5 then opp.id else null end)) as swjd,
                                  COUNT(DISTINCT (case when opp.stage_id = 6 then opp.id else null end)) as wcqys
                           from channel_partner_opportunity opp
                                    LEFT JOIN channel_partner_opportunity_follow foll
                                              on opp.id = foll.cpo_id
                           GROUP BY gbk_office, leader_name
                       ) tableA
                  union all
                  SELECT gbk_office                                                               as office,
                         leader_name   ,
                         leader_id                                                                as leaderId,
                         count(DISTINCT (opp.id))                                                 as sjzs,
                         count(DISTINCT (case when foll.id is null then null else opp.id end))    as ygjsj,
                         count(DISTINCT (foll.creator_id))                                        as gjrs,
                         COUNT(foll.id)                                                            as gjjls,
                         max(opp.last_follow_date)                                                as zhgjsj,
                         COUNT(DISTINCT (case when foll.type = 1 then opp.id else null end))      as smbfs,
                         COUNT(DISTINCT (case when opp.stage_id = 2 then opp.id else null end))    as xsjd,
                         COUNT(DISTINCT (case when opp.stage_id = 3 then opp.id else null end))    as yxjd,
                         COUNT(DISTINCT (case when opp.stage_id = 4 then opp.id else null end))     as bfjd,
                         COUNT(DISTINCT (case when opp.stage_id = 5 then opp.id else null end))    as swjd,
                         COUNT(DISTINCT (case when opp.stage_id = 6 then opp.id else null end))    as wcqys
                  from channel_partner_opportunity opp
                           LEFT JOIN channel_partner_opportunity_follow foll
                                     on opp.id = foll.cpo_id
                  GROUP BY gbk_office, leader_name;

报错信息：

Illegal mix of collations (utf8mb4_0900_ai_ci,COERCIBLE) and (gbk_chinese_ci,IMPLICIT) for operation

原因分析：

有某列的字符集不一致导致

执行sql1：show full columns from 表名;
查出当前表的全部列信息

执行结果：
其中collation是字符集列。在出问题前，有一列的coolation不是utf8_general_ci，而是gbk_chinese_ci

执行sql2：SHOW COLLATION;

执行结果：
可以在其中搜到，发生问题的：gbk_chinese_ci 字符集是gbk。而其他列的：utf8_general_ci字符集utf8。两个是不一样的

因此，得出结论：是 gbk_offic 列的字符集与其他列的不同，导致的错误。

解决方案：

修改与其他列字符集不同的列，与其他列保持一致

新增：alter table 表名 add column 列名 varchar (50) character set utf8;
修改：alter table 表名 modify column 列名 varchar(50) character set utf8;

小tip：

因为是解决了问题后，才来记录的这个bug，所以可能有一些信息截图中与保存信息不一致的存在。

1. 报错信息是不知道修改调试到哪一个版本进行的。所以可能和解决方案中描述的不太一样，但是不影响解决思路，
2. 解决方案中，我先用了修改，然后报错：‘Changing the STORED status’ is not supported for generated columns. 所以我直接使用了新增，然后删除了这个旧列，然后重命名新列。这样一个方法，因为时间有限
3. 之所以换位置就会报错。是因为老版本中，将字符集不同的gbk_office当做输出结果，新版本中：将null 作为 offIce的结果，去关联字符集不同的列。故而报错