MySQL で重複する行を削除する
-
DELETE JOINステートメントを使用して重複行を削除する - ネストされたクエリを使用して重複行を削除する
- 一時テーブルを使用して重複行を削除する
-
ROW_NUMBER()関数を使用して重複する行を削除する
この記事では、MySQL のテーブルに存在する重複行を削除するさまざまな方法を紹介します。このタスクを実行するには、4つの異なるアプローチがあります。
DELETE JOINステートメントを使用して重複行を削除します- ネストされたクエリを使用して重複行を削除します
- 一時テーブルを使用して重複行を削除します
ROW_NUMBER()関数を使用して重複行を削除します
次のスクリプトは、4つの列（custid、first_name、last_name、および email）を持つテーブル customers を作成します。
CREATE TABLE customers (custid INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(255) NOT NULL );
CREATE TABLE customers (custid INT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(255) NOT NULL );
デモンストレーション用の customers テーブルにサンプルデータ値を挿入します。
INSERT INTO customers
VALUES (110,'Susane','Mathew','sussane.mathew@gmail.com'),
(124,'Jean','Carl','jean.carl@gmail.com'),
(331,'Peter','cohelo','peter.coh@google.com'),
(114,'Jaine','Lora','jaine.l@abs.com'),
(244,'Junas','sen','jonas.sen@mac.com');
INSERT INTO customers
VALUES (113,'Jaine','Lora','jaine.l@abs.com'),
(111,'Susane','Mathew','sussane.mathew@gmail.com'),
(665,'Roma','Shetty','roma.sh11@yahoo.com'),
(997,'Beatrice','shelon','beatrice.ss22@yahoo.com'),
(332,'Peter','cohelo','peter.coh@google.com');
以下は、customers テーブルからすべてのデータを返す特定のクエリです。
SELECT * FROM customers order by custid;
テーブルから重複レコードを探すために、customers テーブルで以下に説明するクエリを実行します。
SELECT custid, COUNT(custid) FROM customers GROUP BY custid HAVING COUNT(custid) > 1;
ご覧のとおり、結果には重複する顧客 ID を持つ 3つの行があります。
DELETE JOIN ステートメントを使用して重複行を削除する
delete ステートメントで INNER JOIN を使用すると、MySQL のテーブルから重複する行を削除できます。
次のクエリは、顧客 ID が最小の重複レコードを持つすべての行を選択することにより、ネストされたクエリの概念を使用します。見つかったら、custid が最も低いこれらの重複レコードを削除します。
Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid < c2.custid AND c1.email = c2.email);
このクエリでは、customer テーブルが 2 回参照されます。したがって、エイリアス c1 と c2 を使用します。
出力は次のようになります。
3 行が削除されたことを示しています。
SELECT custid, COUNT(custid) FROM customers GROUP BY custid HAVING COUNT(custid) > 1;
現在、このクエリは空のセットを返します。これは、重複する行が削除されたことを意味します。
select クエリを使用して、customers テーブルのデータを確認できます。
SELECT * FROM customers;
重複する行を削除し、最も低い custid を維持したい場合は、次のステートメントに示すように、同じクエリを使用できますが、条件がわずかに異なります。
Delete from customers where custid IN (Select c1.custid FROM customers as c1
INNER JOIN customers as c2 ON c1.custid > c2.custid AND c1.email = c2.email);
次の出力は、重複する行を削除した後の customers テーブルのデータを示しています。
ネストされたクエリを使用して重複行を削除する
ここで、ネストされたクエリを使用して重複する行を削除するためのステップバイステップの手順を見てみましょう。これは、問題を解決するための比較的簡単なアプローチです。
まず、このクエリを使用してテーブルから一意のレコードを選択します。
Select * from (select max(custid) from customers group by email);
次に、以下に示すように、where 句を指定した delete クエリを使用して、テーブル内の重複する行を削除します。
Delete from customers where custid not in
(select * from (select max(custid) from customers group by email));
出力は次のようになります。
一時テーブルを使用して重複行を削除する
ここで、一時テーブルを使用して重複行を削除する手順を段階的に見ていきましょう。
- まず、元のテーブルと同じ構造の新しいテーブルを作成する必要があります。
- ここで、元のテーブルから一時テーブルに個別の行を挿入します。
- 元のテーブルを削除し、一時テーブルの名前を元のテーブルに変更します。
ステップ 1：CREATE TABLE および LIKE キーワードを使用したテーブルの作成
テーブルの構造全体をコピーする構文は次のとおりです。
CREATE TABLE destination_table LIKE source;
したがって、同じ顧客テーブルがあると仮定して、以下に示すクエリを記述します。
CREATE TABLE temporary LIKE customers;
手順 2.一時テーブルに行を挿入する
以下に示すクエリは、顧客から一意の行をコピーし、それを一時テーブルに書き込みます。ここでは、メールでグループ化しています。
INSERT INTO temporary SELECT * FROM customers GROUP BY email;
手順 3.元の顧客テーブルを削除し、顧客に名前を変更して元のテーブルとして機能する一時テーブルを作成します。
DROP TABLE customers;
ALTER TABLE temporary RENAME TO customers;
出力は次のようになります。
このアプローチは、データ値を処理するだけでなく、テーブルの構造を変更する必要があるため、時間がかかります。
ROW_NUMBER() 関数を使用して重複する行を削除する
ROW_NUMBER() 関数は、MySQL バージョン 8.02 で導入されました。したがって、8.02 より前のバージョンの MySQL を実行している場合は、このアプローチを採用できます。
このクエリは、ROW_NUMBER() 関数を使用して各行に数値を割り当てます。メールが重複している場合、行番号は 1 より大きくなります。
SELECT custid, email, ROW_NUMBER() OVER ( PARTITION BY email ORDER BY email ) AS row FROM customers;
上記のコードスニペットは、重複する行の ID リストを返します。
SELECT custid
FROM ( SELECT custid, ROW_NUMBER() OVER (PARTITION BY email ORDER BY email) AS row FROM customers) t WHERE row > 1;
重複する値を持つ顧客のリストを取得したら、次に示すように、where 句にサブクエリを指定した delete ステートメントを使用してこれを削除できます。
DELETE FROM customers
WHERE custid IN
(SELECT custid FROM
(SELECT custid, ROW_NUMBER() OVER
(PARTITION BY email ORDER BY email) AS row FROM customers) t
WHERE row > 1);
出力は次のようになります。
