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

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つのフィールドを使用し、サイズの計算に別のフィールドを使用して、 ORANDの他のさまざまな組み合わせを試しました。

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQLで重複行を削除する方法
  • MySQLで重複行を削除する方法
  • MySQLデータベースでサポートされているストレージエンジンの比較
  • MySQLはインデックスプッシュダウンを数秒で理解するのに役立ちます

<<:  Link と @import の違いを詳しく見る

>>:  JavaScript における var と let の違い

推薦する

MySQL エラー コード 1862 の解決方法: パスワードの有効期限が切れています

ブロガーは 1 ~ 2 か月間 MySQL を使用していませんでしたが、今日この問題に遭遇しました。...

フロントエンド開発者のための HTML 入門

1 HTML入門1.1 初めてのコード体験、最初のウェブページの作成XML/HTML コードコンテン...

Linux デバイスに空き容量がありません inode の満杯により 500 エラーが発生します

iノードとは何ですか? inode を理解するには、まずファイル ストレージから始める必要があります...

Linux でのマルチスレッドにおけるフォークの紹介

目次質問:ケース(1)子スレッドを作成する前にフォークするケース(2)子スレッドを作成した後にフォー...

Docker共通コマンドの詳しい解説 Study03

目次1. ヘルプコマンド2. ミラーコマンド3. コンテナコマンド1. ヘルプコマンド1. 現在のD...

デザイン理論:人間中心のグリーンデザイン

「人間中心」と「グリーンデザイン」という2つの視点から考える——デザイン業界の同僚とも議論する2つの...

CSS の画像パスの問題に関する議論 (同じパッケージ/異なるパッケージ)

CSS ファイルでは、背景を使用する、つまり背景画像を追加する必要がある場合があります。これは通常、...

ファイルをアップロードするための HTML フォームの「参照」ボタンを変更する方法

コードをコピーコードは次のとおりです。 <!DOCTYPE HTML PUBLIC "...

Linuxで新しいユーザーを作成し、指定されたディレクトリへの権限を付与する

1 ユーザーを作成し、ユーザーのルートパスとパスワードを指定します useradd -d /home...

JSはモバイル端末の画面を1つずつ上下にスライドさせる機能を実装します

この記事では、モバイル端末を一度に1画面ずつ上下にスライドさせるためのJSの具体的なコードを参考まで...

MySQL のインストール方法と設定に関するいくつかの問題の概要

1. MySQL rpm パッケージのインストール # インストールソースをダウンロードします [r...

小さなページングデザイン

ユーザーが目的のものを探すために前進するか後退するかを選択できるようにします。たとえば、Taobao...

RGBAアルファ透明度変換計算表

IEでのRGBAとフィルター値の変換RGBA 透明度値IE フィルター値0.1 19 0.2 33 ...

MySQL Binlog ログの読み取り時によくある 3 つのエラー

1. mysqlbinlog: [エラー] 不明な変数 'default-character...

Raspberry Pi 4 に Ubuntu 19.10 をインストールするための詳細なチュートリアル

以前、raspbian で実行したときに opencv の一部の依存関係をパッケージ化できず、一部の...