MySQL で重複行を見つけて削除する方法

1. 重複行を見つける方法

最初のステップは、重複行としてカウントされるものを定義することです。ほとんどの場合、これは単純です。1 つの列に同じ値が存在します。この記事ではこの定義を使用します。おそらく、「重複」の定義はこれよりも複雑で、SQL にいくつかの変更を加える必要があるでしょう。

この記事で使用されているデータサンプルは次のとおりです。

テーブル test(id int not null primary key, day date not null) を作成します。  

テストに挿入(id, day) values(1, '2006-10-08');  
テストに挿入(id, day) values(2, '2006-10-08');  
テストに挿入(id, day) values(3, '2006-10-09');  

テストから*を選択します。  
+----+-------------+  
| id | 日 |  
+----+-------------+  
| 1 | 2006-10-08 |  
| 2 | 2006-10-08 |  
| 3 | 2006-10-09 |  
+----+-------------+

最初の 2 行のdayフィールドの値は同じなので、重複として扱う場合、重複を見つけるためのクエリは次のようになります。クエリは GROUP BY 句を使用して同じフィールド値を持つ行をグループ化し、グループのサイズを計算します。

テストから日、count(*) を GROUP BY day で選択します。  
+------------+-----------+  
| 日 | カウント(*) |  
+------------+-----------+  
| 2006-10-08 | 2 |  
| 2006-10-09 | 1 |  
+------------+-----------+

重複行のグループサイズが 1 より大きいです。重複行のみを表示したい場合は、次のようにHAVING句を使用する必要があります。

テストグループから day、count(*) を選択します。 HAVING count(*) > 1;  
+------------+-----------+  
| 日 | カウント(*) |  
+------------+-----------+  
| 2006-10-08 | 2 |  
+------------+-----------+

これは基本的な手法です。同じ値を持つフィールドでグループ化し、サイズが 1 より大きいグループを表示します。

WHERE 句を使用できないのはなぜですか? WHERE 句はグループ化の前に行をフィルタリングし、HAVING 句はグループ化の後に行をフィルタリングするためです。

2. 重複行を削除する方法

関連する質問は、重複行を削除する方法です。一般的なタスクは、重複する行を 1 行だけ保持し、他の行を削除することです。その後、適切なインデックスを作成して、重複する行が将来データベースに書き込まれるのを防ぐことができます。

繰り返しになりますが、最初にやるべきことは、重複行とは何かを把握することです。どのラインを維持したいですか?最初の行ですか、それとも特定のフィールドの最大値を持つ行ですか?この記事では、最初の行 (ID フィールドの値が最小の行) を保持し、他の行を削除することを前提としています。

おそらく、これを行う最も簡単な方法は、一時テーブルを使用することです。特にMYSQLの場合、クエリステートメントでselect中にテーブルupdateできないという制限がいくつかあります。簡潔にするために、ここでは一時テーブル方式のみを使用します。

私たちのタスクは、グループ内のidフィールドの最小値を持つ行を除くすべての重複行を削除することです。したがって、サイズが 1 より大きいグループと保持する行を見つける必要があります。 MIN()関数を使用できます。ここでのステートメントは一時テーブルを作成し、 DELETEを使用して削除する必要がある行を検索します。

一時テーブル to_delete を作成します (day date が null ではない、min_id int が null ではない);  

to_delete(day, min_id) に挿入します  
  count(*) > 1 を持つ日を基準に、テストグループから day, MIN(id) を選択します。  

to_delete から * を選択します。  
+------------+---------+  
| 日 | min_id |  
+------------+---------+  
| 2006-10-08 | 1 |  
+------------+---------+

このデータを入手したら、「ダーティ」行の削除を開始できます。いくつかのアプローチがあり、それぞれに長所と短所がありますが、ここではクエリ句をサポートするリレーショナルデータベースで使用される標準的なアプローチを説明する以外、詳細な比較は行いません。

テストから削除  
  存在する場所(  
     to_delete から * を選択  
     ここで、to_delete.day = test.day かつ to_delete.min_id <> test.id  
  ）

3. 複数の列で重複を見つける方法

最近、誰かがこの質問をしました:テーブルの 1 つにフィールド b と c の 2 つがあり、それぞれ他の 2 つのテーブルのフィールド b と c に関連付けられています。 b フィールドまたは c フィールドのいずれかに重複した値を持つ行を見つけたいです。

最初は理解しづらかったのですが、会話をしていくうちに、b と c にそれぞれuniqueインデックスを作成したいのだということが分かりました。前述のように、フィールドの重複値を持つ行を見つけるのは、 groupを使用して行をグループ化し、グループのサイズを計算するだけです。また、重複するフィールドを持つ行を見つけるのも非常に簡単で、すべてのフィールドをグループ句に入れるだけです。ただし、フィールド b が繰り返されるか、フィールド c が繰り返されるかを判断することが問題である場合、問題ははるかに難しくなります。ここで質問者が使用したサンプルデータ

テーブルa_b_cを作成します(  
  NULLでないint主キーauto_increment、  
  b 整数、  
  c 整数  
);  

a_b_c(b,c)に値（1, 1）を挿入します。  
a_b_c(b,c)に値（1, 2）を挿入します。  
a_b_c(b,c)に値（1, 3）を挿入します。  
a_b_c(b,c) に値 (2, 1) を挿入します。  
a_b_c(b,c) に値 (2, 2) を挿入します。  
a_b_c(b,c)に値（2, 3）を挿入します。  
a_b_c(b,c)に値（3, 1）を挿入します。  
a_b_c(b,c)に値（3, 2）を挿入します。  
a_b_c(b,c)に値（3, 3）を挿入します。

ここで、テーブル内に重複する行がいくつかあるが、2 つの行に同じタプル {b, c} がないことが簡単にわかります。だからこそ、問題は難しくなるのです。

4. クエリステートメントが正しくない

両方の列をグループ化すると、グループ化の方法とサイズの計算方法に応じて異なる結果が得られます。まさにここが質問者が行き詰まっているところです。場合によっては、クエリによって重複行がいくつか見つかり、他の行が見つからないことがあります。彼が使ったクエリはこれです

a_b_c から b、c、count(*) を選択  
b、c でグループ化  
count(distinct b > 1) を持つ  
  または count(distinct c > 1);

CONT(*)は常に 1 なので、結果はすべての行を返します。なぜでしょうか? COUNT()内に >1 が記述されているからです。このエラーは簡単に見落とされ、実際には

a_b_c から b、c、count(*) を選択  
b、c でグループ化  
数える(1)  
  またはcount(1);

なぜ？ (b > 1) はブール値であるため、これはまったく必要ないからです。あなたが望むのは

a_b_c から b、c、count(*) を選択  
b、c でグループ化  
count(distinct b) > 1 である  
  または count(distinct c) > 1;

空の結果を返します。明らかに、 {b,c}の重複はありません。この人は、グループ化に1つのフィールドを使用し、サイズの計算に別のフィールドを使用して、 ORとANDの他のさまざまな組み合わせを試しました。

a_b_c から b を選択し、count(*) をグループ化します。count(distinct c) > 1 を持ちます。  
+------+----------+  
| b | カウント(*) |  
+------+----------+  
| 1 | 3 |  
| 2 | 3 |  
| 3 | 3 |  
+------+----------+

いずれも重複行をすべて見つけることはできません。そして最もイライラするのは、これが正しい書き方だと誤って考えれば、状況によってはこの記述が有効になるものの、他の状況では間違った結果になる可能性があるということです。

実際、 GROUP BY単純に使用することは現実的ではありません。なぜ？特定のフィールドでgroup by使用すると、別のフィールドの値が異なるグループに分散されるためです。これらのフィールドで並べ替えると、グループ化の場合と同様に、これらの効果を確認できます。まず、b フィールドを並べ替えて、どのようにグループ化されているかを確認します。

b フィールドを並べ替え (グループ化) すると、c の同じ値が異なるグループに分割されるため、用COUNT(DISTINCT c)サイズを計算することはできません。 COUNT () などの内部関数は同じグループに対してのみ機能し、異なるグループの行に対しては何も実行できません。同様に、c フィールドがソートされている場合、同じ値を持つ b は異なるグループに分割され、いずれにしても目的は達成されません。

5. いくつかの正しい方法

おそらく最も簡単な方法は、各フィールドの重複行を個別に見つけ、次のように UNION を使用してそれらを結合することです。

b を値として選択し、count(*) を cnt として選択し、'b' を what_col として選択します。  
a_b_c から count(*) > 1 を持つ b によるグループ化  
連合  
値として c を選択し、cnt として count(*) を選択し、what_col として 'c' を選択します。  
a_b_c から count(*) > 1 を持つ c でグループ化します。  
+-------+-----+-----------+  
| 値 | 個数 | 列数 |  
+-------+-----+-----------+  
| 1 | 3 | バ |  
| 2 | 3 | バ |  
| 3 | 3 | b |  
| 1 | 3 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |  
| 2 | 3 | 3 | 4 |  
| 3 | 3 | c |  
+-------+-----+-----------+

what_colフィールドは、どのフィールドが繰り返されているかを示すために出力されます。別のアプローチは、ネストされたクエリを使用することです。

a_b_c から a、b、c を選択  
b が (a_b_c から b を選択し、count(*) > 1 を持つ b でグループ化) にある場合  
   または c in (count(*) > 1 を持つ c によって a_b_c グループから c を選択)。  
+----+------+------+  
| a | b | c |  
+----+------+------+  
| 7 | 1 | 1 |  
| 8 | 1 | 2 |  
| 9 | 1 | 3 |  
| 10 | 2 | 1 |  
| 11 | 2 | 2 |  
| 12 | 2 | 3 |  
| 13 | 3 | 1 |  
| 14 | 3 | 2 |  
| 15 | 3 | 3 |  
+----+------+------+

この方法はUNION使用するよりもはるかに効率が悪く、繰り返されるフィールド値ではなく、繰り返される各行が表示されます。もう 1 つの方法は、自分自身とグループのネストされたクエリ結果を共同テーブルでクエリすることです。記述方法は比較的複雑ですが、複雑なデータや効率性が求められる状況では必要です。

 a、a_b_c.b、a_b_c.c を選択  
a_b_c から  
  左外部結合（  
     a_b_c から b を選択し、count(*) > 1 を持つ b でグループ化します。  
  ) は a_b_c.b 上の b = bb である  
  左外部結合（  
     a_b_c から c を選択し、count(*) > 1 を持つ c でグループ化します。  
  ) は a_b_c.c = cc の c として  
bb が null ではない、または cc が null ではない

上記の方法は有効ですが、他にも方法はあると思います。 UNIONが使えれば一番シンプルになると思います。

これで、MySQL で重複行を見つけて削除する方法についての記事は終わりです。MySQL で重複行を見つけて削除する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: