大多數重複記錄屬於這兩類:重複意思和非唯一鍵。我們在「如何在 MySQL 中發現和刪除具有重複意思的值」文章中處理了重複意思的記錄。在今天的後續文章中,我們將討論如何識別非唯一鍵。這是在同一個資料表中的兩筆記錄具有相同的鍵,但是可能或未必有不同的值和意思。
![](https://www.navicat.com/link/Blog/Image/20171227/duplicates2.png)
這是怎樣發生的?
即使是精心設計的資料庫也可能會有非唯一鍵的重複項。它通常是由從外部來源(例如 text、csv 或 excel 檔案)以及資料來源匯入的資料導致的。如果要組合資料產生新鍵,即使資料是來自兩個不同資料庫,也可能會導致重複鍵問題。當然這假設了新鍵欄支援非唯一值。例如,連接兩個數字來產生新鍵就可能會出現問題:
Key 1 | Key 2 | New Key | ||
-------------------------- | ||||
10 | 25 | 1025 | ||
102 | 5 | 1025 !!! |
資料表範例
在支援複雜系統的資料庫中,防止重複鍵發生並不總是可行的。重要的是能夠在資料受到污染之前快速有效地處理它們。
讓我們由從重疊鍵中分開真重複值開始。
這資料表合併了兩個演員的資料來源。你會注意到有幾個重複的名字,特別是「JENNIFER DAVIS」和「NICK WAHLBERG」:
id | first_name | last_name | ||
-------------------------------------- | ||||
10 | PENELOPE | GUINESS | ||
12 | NICK | WAHLBERG | ||
14 | ED | CHASE | ||
22 | JENNIFER | DAVIS | ||
23 | JOHNNY | LOLLOBRIGIDA | ||
27 | BETTE | NICHOLSON | ||
34 | GRACE | MOSTEL | ||
41 | NICK | WAHLBERG | ||
39 | JOE | SWANK | ||
23 | CHRISTIAN | GABLE | ||
22 | JENNIFER | DAVIS |
NICK WAHLBERG 是重複意思的案例,我們在上一篇文章中已探討過。另一方面,JENNIFER DAVIS 出現在兩筆記錄中,並有相同的鍵 22。還有另一個重複的鍵 #23,是兩筆不相關的演員的記錄:「JOHNNY LOLLOBRIGIDA」和「CHRISTIAN GABLE」。關於 22 和 23 的重複鍵,第一個是真正的重複,而第二個只需要為其中一筆記錄產生新鍵就可解決。
識別和計算重複項
以下查詢可識別上述資料表中有相同 ID 的所有記錄。我建議使用 MySQL 的 group_concat() 函式將重複的列組合在一行:
SELECT |
COUNT(*) as repetitions, |
group_concat(id, ' (', last_name, ', ', first_name, ') ' SEPARATOR ' | ') |
as row_data |
FROM amalgamated_actors |
GROUP BY id |
HAVING repetitions > 1; |
Repetitions | row_data | |
------------------------------------------------------------- | ||
2 | 22 (DAVIS, JENNIFER) | 22 (DAVIS, JENNIFER) | |
2 | 23 (LOLLOBRIGIDA, JOHNNY) | 23 (GABLE, CHRISTIAN) |
如果你想同時找出所有重複項(即重複意思和非唯一鍵重複項),你可以將上述查詢與使用 UNION 運算子檢查重複名稱的查詢組合:
SELECT |
COUNT(*) as repetitions, |
group_concat(id, ' (', last_name, ', ', first_name, ') ' SEPARATOR ' | ') |
as row_data |
FROM amalgamated_actors |
GROUP BY id |
HAVING repetitions > 1 |
UNION |
SELECT |
COUNT(*) as repetitions, |
group_concat(id, ' (', last_name, ', ', first_name, ') ' SEPARATOR ' | ') |
as row_data |
FROM amalgamated_actors |
GROUP BY last_name, first_name |
HAVING repetitions > 1; |
這突顯了一個結果集中的所有重複項:
Repetitions | row_data | |
------------------------------------------------------------- | ||
2 | 22 (DAVIS, JENNIFER) | 22 (DAVIS, JENNIFER) | |
2 | 23 (LOLLOBRIGIDA, JOHNNY) | 23 (GABLE, CHRISTIAN) | |
2 | 41 (WAHLBERG, NICK) | 12 (WAHLBERG, NICK) |
總結
如要識別 MySQL 中的重複鍵,編寫查詢是相對簡單的方法,因為你只需要以鍵欄位分組並包含「Having COUNT(*) > 1」子句。在住後的文章中,我們將評估一些刪除重複列和更新鍵的方法。