MySQL で重複レコードを見つけて削除する方法

みなさんこんにちは。私は技術の話ばかりして髪を切らない先生のトニーです。何らかの歴史的な理由や誤操作により、データテーブルに重複レコードが存在する場合があります。今日は、MySQL テーブルで重複データを検索する方法と、これらの重複レコードを削除する方法について説明します。

サンプルテーブルを作成する

まず、サンプルテーブル people を作成し、データを生成します。

存在する場合はテーブルを削除します。
テーブルを作成する人（
 id int auto_increment 主キー、
 名前varchar(50)がnullではない、
 電子メールvarchar(100)がnullでない
);

人々に挿入(名前、メールアドレス)
値（'张三'、'[email protected]'）、
  （「Li Si」、「[email protected]」）、
  （「王武」、「[email protected]」）、
  ('李斯', '[email protected]'),
  （「王武」、「[email protected]」）、
  （'Wang Wu'、'[email protected]'）;

人から*を選択します。
ID|名前|メール|
--|------|-----------------|
 1|張三|[email protected]|
 2|李思|[email protected] |
 3|王武|[email protected] |
 4|リシ|[email protected] |
 5|王武|[email protected] |
 6|王武|[email protected] |

このうち、2 と 4 のメールフィールドに重複データがあり、3、5、6 の名前とメールフィールドに重複データがあります。

この時点で、電子メールの一意の制約を作成しようとすると、エラーが返されます。

テーブル people を変更し、制約 uk_people_email に一意のキー (email) を追加します。
エラー 1062 (23000): キー「people.uk_people_email」のエントリ「[email protected]」が重複しています

当然のことながら、一意の制約を作成するには、電子メールフィールド内の重複レコードを見つけて削除する必要があります。

単一のフィールドで重複データを検索する

重複するメールデータを検索する場合は、このフィールドに基づいてグループ化してカウントし、1 行を超えるグループを返すことができます。

メールを選択し、count(email)
人々から
メールでグループ化
count(email) > 1 であること
メール |count(メール)|
---------------|-------------|
[email protected] | 2|
[email protected]| 3|

クエリ結果には、重複する電子メールアドレスが 2 つあることが示されています。完全な重複データを表示する場合は、サブクエリまたは結合クエリを使用できます。

*を選択
人々から
メールアドレス（
  メールを選択
  人々から
  メールでグループ化
  count(email) > 1)を持つ
電子メールで注文する;
ID|名前|メール|
--|------|--------------|
 2|李思|[email protected] |
 4|リシ|[email protected] |
 3|王武|[email protected]|
 5|王武|[email protected]|
 6|王武|[email protected]|

p.*を選択
人々から
参加する （
 メールを選択
 人々から
 メールでグループ化
 count(email) > 1 を持つ
) d は p.email 上にあります = d.email
電子メールで注文する;
ID|名前|メール|
--|------|--------------|
 2|李思|[email protected] |
 4|リシ|[email protected] |
 3|王武|[email protected]|
 5|王武|[email protected]|
 6|王武|[email protected]|

重複レコードを見つける別の方法は、自己結合クエリと distinct 演算子を直接使用することです。次に例を示します。

異なる p を選択*
人々から
p.email = d.email で人々 d に参加
ここで、p.id <> d.id
メールで注文してください。
ID|名前|メール|
--|------|--------------|
 4|リシ|[email protected] |
 2|李思|[email protected] |
 6|王武|[email protected]|
 5|王武|[email protected]|
 3|王武|[email protected]|

注意: distinct は省略できません。省略すると、一部のデータ (3、5、6) が複数回返されます。

複数のフィールドで重複データを検索する

名前とメールのフィールドが重複しているデータを検索する場合、実装は同様です。

*を選択
人々から
(名前、メールアドレス) が (
  名前、メールアドレスを選択
  人々から
  名前、メールアドレスでグループ化
  count(1) > 1)を持つ
電子メールで注文する;
ID|名前|メール|
--|------|--------------|
 3|王武|[email protected]|
 5|王武|[email protected]|
 6|王武|[email protected]|

異なる p を選択*
人々から
p.name = d.name および p.email = d.email で人 d に参加
ここで、p.id <> d.id
電子メールで注文する;
ID|名前|メール|
--|------|--------------|
 6|王武|[email protected]|
 5|王武|[email protected]|
 3|王武|[email protected]|

重複データは名前と電子メールの両方が同じ場合にのみ発生するため、2 と 4 は重複レコードではありません。

重複排除

重複データを見つけたら、それをどうやって削除するかという問題を解決する必要があります。通常は、レコードの 1 つを保持する必要があります。

DELETE FROM を使用して重複データを削除する

重複する電子メールレコードを削除して 1 つだけ残したい場合は、DELETE FROM ステートメントを使用してこれを実現できます。

削除p
人々から
p.email = d.email かつ p.id < d.id で人々 d を結合します。

delete ステートメントは、接続を使用して削除するレコードを検索します。上記の例では、重複データ内の最大 ID に対応するデータ行が保持されます。 people テーブルを再度クエリします。

人から*を選択します。
ID|名前|メール|
--|------|-----------------|
 1|張三|[email protected]|
 4|リシ|[email protected] |
 6|王武|[email protected] |

考えてみてください。重複データの中で ID が最小のデータを保持したい場合、どうすればよいでしょうか?

サブクエリを使用して重複データを削除する

サブクエリを使用して、保持する必要があるデータを検索し、残りを削除できます。

消去
人々から
IDが（
  最大(ID)を選択
  人々から
  メールでグループ化
  );

上記のステートメントを実行する前に、people テーブルを再作成し、テストデータを生成することを忘れないでください。

中間テーブルを通じて重複データを削除する

重複レコードの削除は、中間テーブルを使用して実行することもできます。次に例を示します。

-- 中間テーブルを作成します。create table people_temp like people;

-- 保持する必要があるデータ行をコピーし、people_temp (id、name、email) に挿入します。
ID、名前、メールアドレスを選択
人々から
idが（
  最大(ID)を選択
  人々から
  メールでグループ化
  );

--元のテーブルを削除します。drop table people;

-- 中間テーブルの名前を元のテーブルに変更します。alter table people_temp rename to people;

上記のステートメントを実行する前に、people テーブルを再作成し、テストデータを生成することを忘れないでください。

この方法で注意する必要があることの 1 つは、create table ... like ステートメントでは元のテーブルの外部キー制約がコピーされないため、手動で追加する必要があることです。

ウィンドウ関数を使用して重複データを削除する

ROW_NUMBER() は、データをグループ化し、各データに一意の番号を割り当てるために使用できる MySQL 8.0 の新しいウィンドウ関数です。例えば：

ID、名前、メールアドレスを選択 
  row_number() を (電子メールによるパーティション、ID による順序) で row_num として計算します。 
人々から;
ID|名前|メール|行番号|
--|------|-----------------|-------|
 2|李思|[email protected] | 1|
 4|リシ|[email protected] | 2|
 3|王武|[email protected] | 1|
 5|王武|[email protected] | 2|
 6|王武|[email protected] | 3|
 1|張さん|[email protected]| 1|

上記のステートメントは、データを電子メールでグループ化し (電子メールでパーティション)、ID で並べ替え (ID で順序付け)、各グループのデータに番号を割り当てます。番号が 1 より大きい場合は、重複データがあることを意味します。

📝ROW_NUMBER() に加えて、RANK() または DENSE_RANK() 関数でも上記の機能を実現できます。ウィンドウ関数の紹介と使用例については、こちらの記事を参照してください。

クエリ結果に基づいて、重複レコードを削除できます。

消去
人々から
idが（
 IDを選択
 から （
  IDを選択、
    row_number() を (電子メールによるパーティション、ID による降順) で row_num として計算します。 
  人から
 ここで、行番号 > 1 です);

上記のステートメントを実行する前に、people テーブルを再作成し、テストデータを生成することを忘れないでください。

複数のフィールドに基づいてデータの重複を排除する方法は、単一のフィールドの場合と非常に似ています。ぜひご自身で試していただき、ディスカッションのためにメッセージを残してください。

要約する

この記事では、GROUP BY、サブクエリ、または結合クエリを使用して単一のフィールドまたは複数のフィールドで重複データを検索する方法、および DELETE FROM ステートメント、サブクエリ、中間テーブル、およびウィンドウ関数を使用して重複データを削除する方法など、MySQL で重複レコードを検索して削除する方法について説明します。 MySQL 重複レコードの検索と削除の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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