CSVの二重取り込みやフォームの二重送信で、同じデータが何件も入ってしまうことがあります。この記事では、MySQL 8.0で重複データを見つける方法と、1件だけ残して削除する方法を実例つきで解説します。重複の確認にはGROUP BYとHAVING、残す行の指定にはROW_NUMBERを使います。DELETEでよく出るエラー1093の回避法と、消す前のバックアップ・確認の手順もあわせて紹介します。最後にUNIQUE制約で再発を防ぐところまで進めましょう。
このシナリオで考える
会員テーブル members に、同じメールアドレスの会員が複数登録されてしまった状況を考えます。原因は、会員データのCSVを誤って2回取り込んだことです。メールアドレスは本来1人1件のはずです。そこで、各メールアドレスにつきidが最も小さい（最初に登録された）1件を残し、残りを削除します。
CREATE TABLE members (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
name VARCHAR(50),
created_at DATETIME
);
INSERT INTO members (id, email, name, created_at) VALUES
(1, 'tanaka@example.com', '田中', '2026-04-01 10:00:00'),
(2, 'sato@example.com', '佐藤', '2026-04-02 11:00:00'),
(3, 'tanaka@example.com', '田中', '2026-04-05 09:30:00'),
(4, 'suzuki@example.com', '鈴木', '2026-04-06 14:00:00'),
(5, 'sato@example.com', '佐藤', '2026-04-08 16:20:00'),
(6, 'tanaka@example.com', '田中太郎', '2026-04-10 08:45:00'),
(7, 'takahashi@example.com', '高橋', '2026-04-12 13:10:00');tanaka@example.com が3件、sato@example.com が2件あります。消すべき行は id 3・5・6 の3件です。
GROUP BYとHAVINGで重複を見つける
重複の確認は、判定したい列でGROUP BYし、件数が2以上のグループだけをHAVINGで残すのが基本です。HAVINGは、集約した後の結果に条件をかける句です。WHEREとの違いはGROUP BYとHAVINGの違いとWHERE句との使い分けで解説しています。
SELECT email,
COUNT(*) AS cnt,
MIN(id) AS keep_id
FROM members
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;+--------------------+-----+---------+
| email | cnt | keep_id |
+--------------------+-----+---------+
| sato@example.com | 2 | 2 |
| tanaka@example.com | 3 | 1 |
+--------------------+-----+---------+どのメールアドレスが何件重複しているかが一目で分かります。MIN(id) を一緒に出しておくと、残す予定の行も確かめられます。
重複している行の中身をすべて見たいときは、この集計を派生テーブル（FROM句に書くサブクエリ）にしてJOINします。
SELECT m.id, m.email, m.name
FROM members AS m
JOIN (
SELECT email
FROM members
GROUP BY email
HAVING COUNT(*) > 1
) AS d ON m.email = d.email
ORDER BY m.email, m.id;+----+--------------------+----------+
| id | email | name |
+----+--------------------+----------+
| 2 | sato@example.com | 佐藤 |
| 5 | sato@example.com | 佐藤 |
| 1 | tanaka@example.com | 田中 |
| 3 | tanaka@example.com | 田中 |
| 6 | tanaka@example.com | 田中太郎 |
+----+--------------------+----------+id 6 の名前だけ「田中太郎」になっています。メールアドレスが同じでも中身の違う行はありえます。どれを残すかは、消す前に必ず目で確かめてください。
ROW_NUMBERで「残す1件」に印をつける
ROW_NUMBERを使うと、重複の中で何番目の行かを番号で表せます。ROW_NUMBERは、グループ（PARTITION BY）ごとに、並び順（ORDER BY）に沿って1から連番を振るウィンドウ関数です。基本はMySQLウィンドウ関数入門を参照してください。番号1の行を残して2以上を消す、と考えれば削除の条件がとても単純になります。
SELECT id,
email,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM members
ORDER BY email, id;+----+-----------------------+----+
| id | email | rn |
+----+-----------------------+----+
| 2 | sato@example.com | 1 |
| 5 | sato@example.com | 2 |
| 4 | suzuki@example.com | 1 |
| 7 | takahashi@example.com | 1 |
| 1 | tanaka@example.com | 1 |
| 3 | tanaka@example.com | 2 |
| 6 | tanaka@example.com | 3 |
+----+-----------------------+----+rn が2以上の行（id 3・5・6）が削除の対象です。重複していない suzuki と takahashi は rn が1なので、消える心配はありません。
この方法の強みは、残す行の基準を ORDER BY で自由に変えられる点です。考え方はグループごとの最新1件を取る方法と同じです。
| 残したい行 | OVER句の書き方 |
|---|---|
| 最初に登録された行 | PARTITION BY email ORDER BY id |
| 最新の行 | PARTITION BY email ORDER BY created_at DESC, id DESC |
| 名前が入っている行を優先 | PARTITION BY email ORDER BY (name IS NULL), id |
created_at のように同じ値が入りうる列で並べるときは、最後に id を足して順番を一つに決めておきます。並びが決まらないと、実行するたびに残る行が変わるおそれがあります。
1件だけ残して削除する：エラー1093の回避
素直に書くと、次のようなDELETEになります。ところが、MySQLではこれがエラーになります。
DELETE FROM members
WHERE id NOT IN (
SELECT MIN(id) FROM members GROUP BY email
);ERROR 1093 (HY000): You can't specify target table 'members' for update in FROM clauseMySQLでは、削除するテーブルを同じ文のサブクエリで読めないためです。回避するには、ROW_NUMBERの結果を派生テーブルにしてJOINする書き方（複数テーブル構文のDELETE）を使います。派生テーブルは先に結果が作られる（実体化される）ので、このエラーは出ません。
DELETE m
FROM members AS m
JOIN (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM members
) AS d ON m.id = d.id
WHERE d.rn > 1;Query OK, 3 rows affectedNOT IN の形を生かしたい場合は、サブクエリをもう一段の派生テーブルで包む方法もあります。MySQL 5.7など、ウィンドウ関数が使えない環境では自己結合も選べます。
-- NOT IN を派生テーブルで包む
DELETE FROM members
WHERE id NOT IN (
SELECT keep_id
FROM (SELECT MIN(id) AS keep_id FROM members GROUP BY email) AS t
);
-- 自己結合：自分より小さいidの同じemailがあれば消す
DELETE m1
FROM members AS m1
JOIN members AS m2
ON m1.email = m2.email
AND m1.id > m2.id;それぞれの特徴を表にまとめます。
| 方法 | 向いている場面 | 注意点 |
|---|---|---|
| GROUP BY + HAVING | 重複の有無と件数の確認 | これだけでは削除できない |
| ROW_NUMBER + JOIN | 残す行の基準を細かく決めたいとき | MySQL 8.0以上が必要 |
| NOT IN + MIN(id) | 最小・最大のidを残すだけのとき | 派生テーブルで包まないとエラー1093 |
| 自己結合 | MySQL 5.7など古い環境 | 重複が多いと組み合わせが増えて遅い |
消す前に確認する4つの手順
DELETEは取り消しがききません。本番のデータで実行する前に、次の順番で確かめてください。
- バックアップ用のテーブルを作る
- 削除対象をSELECTで表示し、件数を数える
- トランザクションの中でDELETEし、残りの件数が合うか確かめる
- 問題なければCOMMIT、おかしければROLLBACKする
-- 1. バックアップ
CREATE TABLE members_bk_20261021 AS SELECT * FROM members;
-- 2. 削除対象の確認（DELETEと同じJOINとWHERE）
SELECT m.*
FROM members AS m
JOIN (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM members
) AS d ON m.id = d.id
WHERE d.rn > 1;
-- 3. トランザクション内で削除
START TRANSACTION;
DELETE m
FROM members AS m
JOIN (
SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM members
) AS d ON m.id = d.id
WHERE d.rn > 1;
SELECT COUNT(*) FROM members; -- 7 - 3 = 4 になっているか
-- 4. 確定（おかしければ ROLLBACK;）
COMMIT;手順2のSELECTには、DELETEと同じJOINとWHEREをそのまま使うのがコツです。SELECTで3件と出たのにDELETEで別の件数が返ってきたら、条件のどこかが違っています。
削除が終わったら、UNIQUE制約をつけて同じ重複が入らないようにします。
ALTER TABLE members ADD UNIQUE KEY uk_members_email (email);重複が1件でも残っていると、この ALTER TABLE は Duplicate entry エラーで失敗します。つまり「全部消せたか」の最終確認にもなります。
よくある落とし穴
- NULLは1つのグループにまとまる：GROUP BYやPARTITION BYでは、NULLどうしが同じグループになります。メールアドレスが未入力の会員が、重複とみなされて消えてしまうおそれがあります。対象から外すには
WHERE email IS NOT NULLを加えます。 - 表記ゆれは重複として見つからない：前後の空白や全角・半角の違いがあると、別の値として扱われます。
GROUP BY TRIM(email)のように揃えた値でも一度集計すると、見落としを減らせます。逆に、MySQL 8.0の既定の照合順序（utf8mb4_0900_ai_ci）は大文字・小文字を区別しません。Tanaka@example.comとtanaka@example.comは同じグループになります。 - 子テーブルからの参照：注文テーブルなどが、削除予定のidを参照している場合があります。外部キー制約があるとDELETEがエラーになるか、ON DELETE CASCADEで注文まで消えてしまいます。先に参照先を残す側のidへ付け替えましょう。
- 大量の行を一度に消さない：何十万件もの削除を1回で行うと、ロックが長く続きます。削除対象のidを一時テーブルに入れてから、LIMIT付きのDELETEで少しずつ消すと安全です。
子テーブルの付け替えには、FIRST_VALUEで「残す側のid」を求めてからUPDATEします。FIRST_VALUEは、グループ内の先頭行の値を返すウィンドウ関数です。
UPDATE orders AS o
JOIN (
SELECT id,
FIRST_VALUE(id) OVER (PARTITION BY email ORDER BY id) AS keep_id
FROM members
) AS d ON o.member_id = d.id
SET o.member_id = d.keep_id
WHERE d.id <> d.keep_id;まとめ
重複データの削除は「見つける → 残す1件を決める → 確かめてから消す → 再発を防ぐ」の順に進めます。見つけるのはGROUP BYとHAVING、残す行を決めるのはROW_NUMBERの役割です。MySQLでは同じテーブルをサブクエリで読むとエラー1093になります。派生テーブルとJOINするDELETEで回避しましょう。最後にUNIQUE制約をつければ、同じ問題は起きにくくなります。
参考リンク
- MySQL 8.0 Reference Manual: DELETE Statement
- MySQL 8.0 Reference Manual: Window Function Descriptions
- MySQL 8.0 Reference Manual: Restrictions on Subqueries
- GROUP BYとHAVINGの違いとWHERE句との使い分け
- MySQLウィンドウ関数入門：ROW_NUMBER・RANK・DENSE_RANKの違い
アイキャッチ画像: Photo by Jantine Doornbos on Unsplash
