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

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

以下もご興味があるかもしれません:
  • MySQL にテーブルが存在するかどうかを確認し、それを一括で削除する方法
  • MySQL でデータを削除してもテーブル ファイルのサイズが変更されないのはなぜですか?
  • MySQL でテーブルを削除する 3 つの方法 (要約)
  • MySQL で大量のデータ (数千万) を素早く削除するためのいくつかの実用的なソリューションの詳細な説明
  • MySQLサービスを削除する具体的な方法
  • MySQL の重複データの処理方法 (防止と削除)
  • MySQL データベース操作 (作成、選択、削除)
  • Windows で削除された MySQL 8.0.17 のルート アカウントとパスワードを回復する方法
  • Linux で MySQL 8.0 サービスを完全に削除する方法
  • MySQL テーブル削除操作の実装 (delete、truncate、drop の違い)
  • MySQL で削除されたレコードが有効にならない理由のトラブルシューティング

<<:  jsはウォーターフォールフローのボトムアウトによるデータの動的ロードを実現します

>>:  Alibaba Cloud に Docker をインストールする際の問題と解決策

推薦する

Excel をインポートするときに js で時間を変換する正しい方法について

目次1. 基本2. 問題の説明3. 解決策付録: js を使用して Excel の日付形式を変換する...

MySQL での select、distinct、limit の使用

目次1. はじめに2. 選択2.1 単一列のクエリ2.2 複数の列のクエリ2.3 すべての列をクエリ...

HTML でナンバープレート番号と州の略語を入力するためのサンプルコード

原理としては、まずボタン付きの div を記述し、次に画面のサイズに応じて自動的に適応してキーボード...

MySQL 重複インデックスと冗長インデックスの例の分析

この記事では、例を使用して MySQL の重複インデックスと冗長インデックスについて説明します。ご参...

MySQLのパスワードを忘れた場合の対処方法

MySQL パスワードを 2 回忘れてしまいましたか?最初、私はアンインストールして再インストールす...

JDKネイティブスレッドプールのバグを修正するTomcatの実装原理

処理能力と同時実行性を向上させるために、Web コンテナは通常、リクエストを処理するタスクをスレッド...

Linux システムで Code Cloud にプロジェクトをアップロードする方法

Code Cloudで新しいプロジェクトtest1を作成します。 公開鍵を取得するには次のコマンドを...

NavicatでMySqlスケジュールタスクを作成する方法の詳細な説明

Navicat で MySql スケジュールタスクを作成する詳細な説明イベントは、MySQL が特定...

Ubuntu 18.04 MySQL 8.0 のインストールと設定方法のグラフィックチュートリアル

この記事では、MySQL 8.0のインストールと設定方法を参考までに紹介します。具体的な内容は以下の...

CSS3 で作成された背景グラデーションアニメーション効果

成果を達成する 実装コードhtml <h1 class="text-light&qu...

JS のオブジェクトリテラルの詳細な説明

目次序文1. オブジェクト構築にプロトタイプを設定する1.1 __proto__ の使用における特殊...

Dockerイメージをインポートおよびエクスポートする方法

この記事では、移行、バックアップ、アップグレードなどのシナリオで使用される Docker イメージの...

Linux での Docker と portainer の設定方法

1.Docer CEをインストールして使用するこの記事では、CentOS 7 を例に Docker ...

HTML 中国語文字エンコード標準の概要

HTML では、Web ページで使用されるエンコーディングを指定する必要があります。一般的な指定方法...