もう悩まない！ MySQLテーブルの重複レコードを撃退する最強テクニック

2024-05-17

MySQLテーブルの重複レコードを削除する方法

そこで今回は、MySQLテーブルの重複レコードを削除する方法について、2つのケースに分けて分かりやすく解説します。

ケース1：主キーによる重複レコードの削除

テーブルに主キーが設定されている場合、以下の方法で重複レコードを効率的に削除できます。

DELETE FROM テーブル名
WHERE id NOT IN (
    SELECT MIN(id)
    FROM テーブル名
    GROUP BY カラム1, カラム2, ...
);

説明:

DELETE FROM テーブル名: 対象となるテーブルを選択します。
WHERE id NOT IN (...)): 削除対象となるレコードを条件で絞り込みます。
SELECT MIN(id) FROM テーブル名 GROUP BY カラム1, カラム2, ...: 各重複グループの中で、最小の id を持つレコードのみを選択します。

例:

customers テーブルに id (主キー), name, email カラムがあり、name と email の組み合わせが重複している場合、以下のクエリで重複レコードを削除できます。

DELETE FROM customers
WHERE id NOT IN (
    SELECT MIN(id)
    FROM customers
    GROUP BY name, email
);

方法1： DISTINCT句とサブクエリを使用する

DELETE t
FROM テーブル名 AS t
WHERE t.id NOT IN (
    SELECT s.id
    FROM テーブル名 AS s
    WHERE s.id < t.id
);

SELECT s.id FROM テーブル名 AS s WHERE s.id < t.id: tよりも先に登録されたレコードの id をすべて取得します。
t.id NOT IN ( ... ): tの id が、先に登録されたレコードの id と一致しないレコードのみを削除します。

方法2：集計関数とROW_NUMBER()を使用する

DELETE FROM テーブル名
WHERE row_number() OVER (PARTITION BY カラム1, カラム2, ... ORDER BY カラム1, カラム2, ... ASC) > 1;

WHERE row_number() OVER ( ... ) > 1: 各重複グループの中で、1番目以降のレコードを削除します。
PARTITION BY カラム1, カラム2, ...: 重複を判断するカラムを指定します。
ORDER BY カラム1, カラム2, ... ASC: 各重複グループ内のレコードをソート順序で並べ替えます。

注意事項

いずれの方法を実行する前に、必ずテーブルのバックアップを取っておいてください。
削除対象となるレコードを誤って削除しないよう、十分注意してください。
大量のレコードを削除する場合は、パフォーマンスに影響が出る可能性があります。必要に応じて、処理を分割したり、バッチ処理を実行したりすることを検討してください。

-- テーブル構造 (customersテーブル)
CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

-- 重複レコードの挿入
INSERT INTO customers (name, email) VALUES
  ('Taro Yamada', '[email protected]'),
  ('Taro Yamada', '[email protected]'),
  ('Hanako Sato', '[email protected]'),
  ('Jiro Tanaka', '[email protected]');

-- 重複レコードの削除
DELETE FROM customers
WHERE id NOT IN (
  SELECT MIN(id)
  FROM customers
  GROUP BY name, email
);

-- 削除結果の確認
SELECT * FROM customers;

-- テーブル構造 (productsテーブル)
CREATE TABLE products (
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL
);

-- 重複レコードの挿入
INSERT INTO products (name, price, stock) VALUES
  ('Product A', 1000, 10),
  ('Product A', 1000, 20),
  ('Product B', 2000, 30),
  ('Product C', 3000, 40);

-- 重複レコードの削除
DELETE t
FROM products AS t
WHERE t.id NOT IN (
  SELECT s.id
  FROM products AS s
  WHERE s.id < t.id
);

-- 削除結果の確認
SELECT * FROM products;

-- テーブル構造 (productsテーブル)
-- (上記と同じ)

-- 重複レコードの削除
DELETE FROM products
WHERE row_number() OVER (PARTITION BY name, price ORDER BY name, price ASC) > 1;

-- 削除結果の確認
SELECT * FROM products;

上記のコードは、あくまでもサンプルです。実際の使用環境に合わせて、テーブル名、カラム名、クエリなどを修正する必要があります。
重複レコードの判断基準は、状況によって異なる場合があります。必要に応じて、クエリを修正してください。

MySQLテーブルの重複レコードを削除するその他の方法

以下に、いくつかの例を紹介します。

方法1： GROUP BY句とHAVING句を使用する

DELETE FROM テーブル名
WHERE EXISTS (
  SELECT 1
  FROM テーブル名 AS s
  WHERE s.カラム1 = テーブル名.カラム1
    AND s.カラム2 = テーブル名.カラム2
    ...
    AND s.id < テーブル名.id
);

SELECT 1 FROM テーブル名 AS s ...: tよりも先に登録されたレコードが存在するかどうかを確認します。
s.id < テーブル名.id: tの id が、先に登録されたレコードの id より小さいかどうかを確認します。

方法2： CTEを使用する

WITH cte AS (
  SELECT カラム1, カラム2, ...
    ROW_NUMBER() OVER (ORDER BY カラム1, カラム2, ... ASC) AS rn
  FROM テーブル名
)
DELETE FROM テーブル名
WHERE rn > 1;

WITH cte AS ( ... ): CTE (Common Table Expression) を定義します。
SELECT ...: CTE内のカラムと、各レコードの行番号 (rn) を選択します。
WHERE rn > 1: 行番号が1番目以降のレコードを削除します。

方法3：トランザクションとINSERT ... SELECTを使用する

START TRANSACTION;

-- 一時テーブルを作成
CREATE TEMPORARY TABLE tmp_table (
  カラム1 型,
  カラム2 型,
  ...
);

-- 重複レコードを除いたデータを一時テーブルへ挿入
INSERT INTO tmp_table
SELECT DISTINCT カラム1, カラム2, ...
FROM テーブル名;

-- 本テーブルのデータを削除
TRUNCATE TABLE テーブル名;

-- 一時テーブルから本テーブルへデータをインポート
INSERT INTO テーブル名
SELECT * FROM tmp_table;

COMMIT;

START TRANSACTION;: トランザクションを開始します。
CREATE TEMPORARY TABLE ...;: 一時テーブルを作成します。
INSERT INTO ... SELECT ...;: 重複レコードを除いたデータを一時テーブルへ挿入します。
TRUNCATE TABLE ...;: 本テーブルのデータを論理削除します。
COMMIT;: トランザクションをコミットします。