MySQL多列欄位去重的案例實踐

愛可生雲資料庫發表於2023-05-16
MySql

同事提了個需求，如下測試表，有code、cdate和ctotal三列，
select * from tt;

現在要得到code的唯一值，但同時帶著cdate和ctotal兩個欄位。

提起"唯一值"，想到的就是distinct。distinct關鍵字可以過濾多餘的重複記錄只保留一條。

distinct支援單列去重和多列去重，如果是單列去重，簡明易懂，即相同值只保留1個，如下所示，
select distinct code from tt;
圖片
多列去重則是根據指定的去重列資訊進行，即只有所有指定的列資訊都相同，才會被認為是重複的資訊，如下所示，code、cdate和ctotal都相同，才會返回記錄，因此不是字面上的理解，即只要code是distinct的，cdate和ctotal無需關注。實際上當distinct應用到多個欄位的時候，其應用的範圍是其後面的所有欄位，而不只是緊貼著它的一個欄位，即distinct同時作用了三個欄位，code、cdate和ctotal，並不只是code欄位，select distinct code, cdate, ctotal from tt;
圖片
而且distinct只能放到所有欄位的前面，如果像這種，distinct之前有其它欄位，則會提示錯誤，

select cdate, ctotal, distinct code from tt;SQL 錯誤 [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct code from tt' at line 1

但是如上SQL使用distinct關鍵字，並沒有滿足需求，即得到code的唯一值，但同時帶著cdate和ctotal兩個欄位，可以看到有很多相同的code。

除了distinct，group by子句也可以去重，從需求的理解上，如果按照code做group by，應該就可以得到唯一的code了，但是實際執行，提示這個錯誤，

select code, cdate, ctotal from tt group by code;SQL 錯誤 [1055] [42000]: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tt.code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

很常見的錯誤，因為sql_mode中含only_full_group_by規則，

show variables like '%sql_mode%';ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

only_full_group_by規則是指對group by進行查詢的SQL，不允許select部分出現group by中未出現的欄位，也就是select查詢的欄位必須是group by中出現的或者使用聚合函式的，即校驗更加嚴格。

P.S. MySQL不同版本sql_mode預設值可能是不同的，因此在資料庫升級配合的應用遷移過程中，尤其要注意像only_full_group_by這種校驗規則的改變，很可能是個坑。

僅針對當前這個問題，可以在會話級，修改sql_mode，調整校驗的強度，刪除only_full_group_by，

set session 
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

此時，使用group by，

select code, cdate, ctotal from tt group by code;

就可以得到想要的效果了，
圖片
除了group by，還可以使用group_concat函式，配合distinct，達到相同效果。

我們分解來做，可以看到group_concat(code)，得到的是所有記錄的code值拼接成新欄位，

select group_concat(code), cdate, ctotal from tt group by code;

圖片
group_concat中加上distinct，就可以過濾所有的重複值，滿足了需求，

select group_concat(distinct code), cdate, ctotal from tt group by code;

圖片
當然，這種在會話級透過改動sql_mode實現的路徑，還需要考慮場景，因為缺少only_full_group_by的校驗，按照code聚類了，但cdate和ctotal的值很可能是不唯一的，返回的結果，只能準確描述code的資料情況，不能代表cdate和ctotal的真實資料情況。因此，任何方案的選擇，都需要結合實際的場景需求，我們找的方案，不一定是最好的，但需要最合適的。本文關鍵字：#SQL# #去重#

相關文章