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 wcqysfrom channel_partner_opportunity oppLEFT JOIN channel_partner_opportunity_follow follon opp.id = foll.cpo_idGROUP BY gbk_office, leader_name union allselect '合計' 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 wcqysfrom (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 wcqysfrom channel_partner_opportunity oppLEFT JOIN channel_partner_opportunity_follow follon opp.id = foll.cpo_idGROUP 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 wcqysfrom (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 wcqysfrom channel_partner_opportunity oppLEFT JOIN channel_partner_opportunity_follow follon opp.id = foll.cpo_idGROUP BY gbk_office, leader_name) tableAunion allSELECT 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 wcqysfrom channel_partner_opportunity oppLEFT JOIN channel_partner_opportunity_follow follon opp.id = foll.cpo_idGROUP 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，所以可能有一些信息截圖中與保存信息不一致的存在。

報錯信息是不知道修改調試到哪一個版本進行的。所以可能和解決方案中描述的不太一樣，但是不影響解決思路，

解決方案中，我先用了修改，然后報錯：‘Changing the STORED status’ is not supported for generated columns. 所以我直接使用了新增，然后刪除了這個舊列，然后重命名新列。這樣一個方法，因為時間有限

之所以換位置就會報錯。是因為老版本中，將字符集不同的gbk_office當做輸出結果，新版本中：將null 作為 offIce的結果，去關聯字符集不同的列。故而報錯

總結

以上是生活随笔為你收集整理的mysql使用union all时字符集不一致的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。