データ量が多い場合でも効率的に削除！MySQLで重複行を削除する方法

2024-04-02

MySQLで重複行を削除するには、主に以下の2つの方法があります。

DELETE ステートメント
DISTINCT キーワード

それぞれの方法について、詳細とサンプルコードを紹介します。

DELETE ステートメントを使用すると、特定の条件に基づいて重複行を削除できます。

基本的な例

DELETE FROM テーブル名
WHERE 重複判定条件;

例：id と name が重複している行を削除

DELETE FROM users
WHERE (id, name) IN (
    SELECT id, name
    FROM users
    GROUP BY id, name
    HAVING COUNT(*) > 1
);

その他のオプション

ORDER BY 句を使って、削除する行を指定できます。

例：

id の昇順で最初の10行を削除

DELETE FROM users
ORDER BY id ASC
LIMIT 10;

SELECT ステートメントで DISTINCT キーワードを使用すると、重複行を除いて結果を取得できます。

SELECT DISTINCT id, name
FROM users;

注意事項

DELETE ステートメントは、一度実行すると元に戻せません。実行前に必ずバックアップを取ってください。
DISTINCT キーワードは、すべての列を比較して重複を判定します。そのため、比較したい列をすべて指定する必要があります。

DELETE ステートメント

1 基本的な例

-- テーブル作成
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO users (name) VALUES ('山田太郎'), ('佐藤花子'), ('田中一郎'), ('山田太郎');

-- 重複行削除
DELETE FROM users
WHERE name = '山田太郎';

-- 結果確認
SELECT * FROM users;

# 出力
# +----+------+
# | id | name |
# +----+------+
# | 1  | 佐藤花子 |
# | 2  | 田中一郎 |
# +----+------+

2 ORDER BY 句と LIMIT 句

-- テーブル作成
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO users (name, age) VALUES 
    ('山田太郎', 20), ('佐藤花子', 21), ('田中一郎', 22), ('山田太郎', 23), ('佐藤花子', 24);

-- 重複行削除 (年齢の昇順で最初の1行)
DELETE FROM users
WHERE name = '山田太郎'
ORDER BY age ASC
LIMIT 1;

-- 結果確認
SELECT * FROM users;

# 出力
# +----+------+------+
# | id | name | age |
# +----+------+------+
# | 1  | 佐藤花子 | 21 |
# | 2  | 田中一郎 | 22 |
# | 3  | 山田太郎 | 23 |
# | 4  | 佐藤花子 | 24 |
# +----+------+------+

DISTINCT キーワード

-- テーブル作成
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO users (name, age) VALUES 
    ('山田太郎', 20), ('佐藤花子', 21), ('田中一郎', 22), ('山田太郎', 23), ('佐藤花子', 24);

-- 重複行を除いて結果取得
SELECT DISTINCT name, age
FROM users;

# 出力
# +------+------+
# | name | age |
# +------+------+
# | 山田太郎 | 20 |
# | 佐藤花子 | 21 |
# | 田中一郎 | 22 |
# +------+------+

その他の重複行を削除する方法

GROUP BY 句と HAVING 句を使うと、グループ内の重複行を削除できます。

-- テーブル作成
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO users (name, age) VALUES 
    ('山田太郎', 20), ('佐藤花子', 21), ('田中一郎', 22), ('山田太郎', 23), ('佐藤花子', 24);

-- グループ内の重複行削除
DELETE t
FROM users t
INNER JOIN (
    SELECT name, COUNT(*) AS cnt
    FROM users
    GROUP BY name
    HAVING cnt > 1
) AS dup ON t.name = dup.name;

-- 結果確認
SELECT * FROM users;

# 出力
# +----+------+------+
# | id | name | age |
# +----+------+------+
# | 1  | 佐藤花子 | 21 |
# | 2  | 田中一郎 | 22 |
# +----+------+------+

一時テーブルを使って、重複行を削除する方法もあります。

-- テーブル作成
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (id)
);

-- データ挿入
INSERT INTO users (name, age) VALUES 
    ('山田太郎', 20), ('佐藤花子', 21), ('田中一郎', 22), ('山田太郎', 23), ('佐藤花子', 24);

-- 一時テーブル作成
CREATE TEMPORARY TABLE tmp_users AS
SELECT DISTINCT *
FROM users;

-- 重複行削除
TRUNCATE TABLE users;

-- 一時テーブルからデータ挿入
INSERT INTO users
SELECT *
FROM tmp_users;

-- 一時テーブル削除
DROP TABLE tmp_users;

-- 結果確認
SELECT * FROM users;

# 出力
# +----+------+------+
# | id | name | age |
# +----+------+------+
# | 1  | 佐藤花子 | 21 |
# | 2  | 田中一郎 | 22 |
# +----+------+------+