MySQL データベースから重複データを削除する方法

Introduction

Lorsque vous travaillez avec des bases de données, en particulier dans des environnements où de grands ensembles de données sont constamment modifiés ou ajoutés, il est courant de rencontrer des données en double. Cela peut entraîner des inefficacités dans les performances des requêtes, des incohérences des données et des rapports inexacts. Heureusement, MySQL fournit des outils robustes pour identifier et supprimer ces doublons.

Dans ce blog, je vais vous présenter une approche efficace et complète pour détecter et supprimer les enregistrements en double dans une base de données MySQL. Les méthodes présentées ici sont applicables à la plupart des systèmes de bases de données relationnelles, mais nous nous concentrerons sur MySQL pour ce didacticiel.

Qu'est-ce qui constitue un doublon ?

Avant de plonger dans le code, il est crucial de définir ce qui est considéré comme un doublon. Dans de nombreux cas, les doublons ne sont pas simplement des enregistrements dans lesquels toutes les colonnes ont des valeurs identiques. Souvent, les doublons peuvent avoir les mêmes valeurs dans un sous-ensemble de colonnes clés. Par exemple, dans une table utilisateurs, deux enregistrements peuvent avoir le même e-mail mais différer dans d'autres champs comme le nom d'utilisateur ou la date d'inscription.

Par souci de simplicité, dans ce didacticiel, nous supposerons que les doublons sont des lignes dans lesquelles toutes les colonnes (ou un sous-ensemble de colonnes) correspondent.

Prenons l'exemple d'employés de table suivant :

id	first_name	last_name	email	salary
1	John	Doe	john@example.com	60000
2	Jane	Smith	jane@example.com	65000
3	John	Doe	john@example.com	60000
4	Alex	Johnson	alex@example.com	72000
5	John	Doe	john@example.com	60000

ここでは、id = 1、id = 3、および id = 5 の行が重複しています。私たちの目標は、コピーを 1 つだけ残して削除することです。

重複を削除するためのステップバイステップ ガイド

1. 重複レコードを特定する

最初のステップは、どのレコードが重複しているかを特定することです。これを行うには、一意である必要がある列ごとにレコードをグループ化する必要があります。この場合、first_name、last_name、および email の組み合わせが一意であると仮定しましょう。

次のクエリを使用して重複を検索できます:

SELECT first_name, last_name, email, COUNT(*)
FROM employees
GROUP BY first_name, last_name, email
HAVING COUNT(*) > 1;

このクエリは、first_name、last_name、および email 列に基づいてレコードをグループ化し、複数出現するグループ (つまり、重複) のみを表示します。

2. 保持または削除する重複行を選択します

重複を特定したら、それらを削除する方法が必要です。一般的なアプローチは、最小または最大の ID を持つレコードを保持し、その他のレコードを削除することです。 自己結合を使用してこれを実行し、各重複レコードを保持したいレコードと照合します。

例：

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id;

このクエリは次のように機能します:

• レコードの first_name、last_name、および email が同じである従業員テーブルに対して 自己結合 を実行します。
• これにより、最小の ID を持つレコードを保持しながら、より高い ID (後で挿入されたことを意味する) を持つ行のみが削除されることが保証されます。

3. より複雑なシナリオには一時テーブルを使用します

状況によっては、最新のsignup_date や最高給与のレコードを保持するなど、どの重複を保持するかを決定するためのより複雑な条件が必要になる場合があります。

そのような場合は、一時テーブルを使用して保持したい行を保存し、他のすべてを削除できます。

例：

CREATE TEMPORARY TABLE temp_employees AS
SELECT * FROM employees e1
WHERE e1.id IN (
    SELECT MIN(id)
    FROM employees
    GROUP BY first_name, last_name, email
);

DELETE FROM employees
WHERE id NOT IN (SELECT id FROM temp_employees);

このクエリの動作は次のとおりです:

1. first_name、last_name、および email の組み合わせごとに 1 つの一意のレコード (この場合は、最小の ID を持つレコード) のみを含む一時テーブル temp_employees を作成します。
2. temp_employees テーブルに存在しないすべてのレコードを元のemployees テーブルから削除します。

このメソッドは、どの重複を保持するかを決定するための基準が、単に ID を使用するよりも複雑な場合に役立ちます。

4. 将来の重複を防ぐために固有の制約を追加します

重複をクリーンアップしたら、重複の発生を防ぐことをお勧めします。これを実現するには、一意制約を関連する列に追加します。

たとえば、同じ first_name、last_name、および email を持つ今後の行を防ぐには:

ALTER TABLE employees
ADD CONSTRAINT unique_employee
UNIQUE (first_name, last_name, email);

これにより、重複レコードを挿入しようとした場合にデータベースがエラーをスローすることが保証され、データの整合性が維持されます。

パフォーマンスに関する考慮事項

大規模なデータセットを操作する場合、重複の削除は時間がかかり、リソースを大量に消費する可能性があります。パフォーマンスを最適化するためのヒントをいくつか紹介します:

• インデックス作成: フィルタリングに関係する列 (first_name、last_name、email など) がインデックス付けされていることを確認してください。これにより、プロセスが大幅に高速化されます。
• バッチ削除: 多数の行を削除する場合は、長時間のロックを回避し、サーバーの負荷を軽減するために、バッチで実行することを検討してください。

一括削除の例:

DELETE e1
FROM employees e1
JOIN employees e2
ON e1.first_name = e2.first_name
AND e1.last_name = e2.last_name
AND e1.email = e2.email
AND e1.id > e2.id
LIMIT 1000;

すべての重複が削除されるまで、このクエリを複数回実行できます。

結論

MySQL データベース内の重複レコードの処理は一般的なタスクであり、処理を誤るとデータの損失や結果の一貫性の欠如につながる可能性があります。このブログで説明する手順を使用すると、データの整合性を維持しながら、自信を持って効率的に重複を削除できます。さらに、一意の制約を追加することで、今後の重複を確実に防止し、クリーンで信頼性の高いデータセットを維持することができます。

以上がMySQL データベースから重複データを削除する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。