MySQLで重複する行を削除する方法

はじめに

MySQLデータベースで重複する行が発生する可能性があるいくつかのインスタンスがあります。 このガイドでは、MySQLで重複する行の値を削除する方法について説明します。

前提条件

MySQLがインストールされているシステム
MySQLルートユーザーアカウント
ターミナルウィンドウ/コマンドラインへのアクセス（Ctrl-Alt-T、検索>ターミナル）

テストデータベースの設定

作業するMySQLデータベースがすでにある場合は、次のセクションに進んでください。

それ以外の場合は、ターミナルウィンドウを開き、次のように入力します。

mysql –u root –p

プロンプトが表示されたら、ルートを入力します MySQLインストールのパスワード。特定のユーザーアカウントをお持ちの場合は、rootの代わりにそれらの資格情報を使用してください。

システムプロンプトは次のように変更されます。

mysql>

テストデータベースの作成

既存のデータベースに新しいテーブルを作成できます。これを行うには、既存のすべてのインスタンスを次のように一覧表示して、適切なデータベースを見つけます。

SHOW DATABASES;

または、次のコマンドを入力して、新しいデータベースを作成することもできます。

CREATE DATABASE IF NOT EXISTS testdata;

新しいtestdataでの作業を開始するには データベースの使用：

USE testdata;

テーブルとデータを追加

データベースに移動したら、次のコマンドを使用して、以下のデータを含むテーブルを追加します。

CREATE TABLE dates (
id INT PRIMARY KEY AUTO_INCREMENT,
day VARCHAR(2) NOT NULL,
month VARCHAR(10) NOT NULL,
year VARCHAR(4) NOT NULL

);

INSERT INTO dates (day,month,year)
VALUES (’29’,’January’,’2011’),
(’30’,’January’,’2011’),
(’30’,’January’,’2011’),
(’14’,’February,’2017’),
(’14’,’February,’2018’),
(‘23’,’March’,’2018’),
(‘23’,’March’,’2018’),
(‘23’,’March’,’2019’),
(‘29’,’October’,’2019’),
(‘29’,’November’,’2019’),
(‘12’,’November’,’2017’),
(‘17’,’August’,’2018’),
(‘05’,’June’,’2016’);

日付テーブルの内容を表示する

入力したすべての日付を年順に表示するには、次のように入力します。

SELECT * FROM dates ORDER BY year;

出力には、日付のリストが適切な順序で表示されます。

重複する行を表示する

テストデータベースに重複する行があるかどうかを確認するには、次のコマンドを使用します。

SELECT
     day, COUNT(day),
     month, COUNT(month),
     year, COUNT(year)
FROM 
     dates
GROUP BY
     day,
     month,
     year
HAVING 
     COUNT(day) > 1
     AND COUNT(month) > 1
     AND COUNT(year) > 1;

重複している値はすべて表示されます。この場合、次のように表示されます。

この形式は、複数の列を選択するために機能します。連絡先リストのメールアドレスや単一の日付列など、一意の識別子を持つ列がある場合は、その1つの列から選択するだけです。

重複する行の削除

以下の方法のいずれかを使用する前に、既存のデータベースで作業する必要があることを忘れないでください。サンプルデータベースを使用します：

USE testdata;

オプション1：内部結合を使用して重複する行を削除する

テストMySQLテーブルの重複行を削除するには、MySQL JOINSを使用して、次のように入力します。

delete t1 FROM dates t1
INNER  JOIN dates t2
WHERE
    t1.id < t2.id AND
    t1.day = t2.day AND
    t1.month = t2.month AND
    t1.year = t2.year;

重複表示のコマンドを使用することもできます行削除を確認します。

オプション2：中間テーブルを使用して重複する行を削除する

中間テーブルを作成できます重複する行を削除するために使用します。これは、一意の行のみを新しく作成されたテーブルに転送し、元の行を（残りの重複行とともに）削除することによって行われます。

これを行うには、以下の手順に従ってください。

1.ソーステーブルと同じ構造を持つ中間テーブルを作成し、ソースで見つかった一意の行を転送します。

CREATE TABLE [copy_of_source] SELECT DISTINCT [columns] FROM [source_table];

たとえば、サンプルテーブルの構造のコピーを作成するには dates コマンドは次のとおりです：

CREATE TABLE copy_of_dates SELECT DISTINCT id, day, month, year FROM dates;

2.これが完了したら、dropコマンドを使用してソーステーブルを削除し、新しいテーブルの名前を変更できます。

DROP TABLE [source_table];

ALTER TABLE [copy_of_source] RENAME TO [source_table];

例：

DROP TABLE dates;

ALTER TABLE copy_of_dates RENAME TO dates;

オプション3：ROW_NUMBER（）を使用して重複行を削除する

重要： このメソッドは、MySQLバージョン8.02でのみ使用できます。以降。この方法を試す前に、MySQLのバージョンを確認してください。

重複する行を削除する別の方法は、 ROW_NUMBER()を使用することです。 機能。

SELECT *. ROW_NUMBER () Over (PARTITION BY [column] ORDER BY [column]) as [row_number_name];

したがって、サンプルテーブルのコマンドは次のようになります。

SELECT *. ROW_NUMBER () Over (PARTITION BY id ORDER BY id) as row_number;

結果にはrow_numberが含まれます桁。データはidで分割されます各パーティション内には一意の行番号があります。一意の値には、行番号 1のラベルが付いています、重複は 2 ですが、、 3 、など。

したがって、重複する行を削除するには、1でマークされた行を除くすべてを削除する必要があります。これは、 DELETEを実行することによって行われます。 row_numberでクエリを実行します フィルタとして。

重複する行を削除するには、次のコマンドを実行します。

DELETE FROM [table_name] WHERE row_number > 1;

この例では、日付テーブルの場合、コマンドは次のようになります。

DELETE FROM dates WHERE row_number > 1;

出力には、影響を受けた行の数、つまり、削除された重複行の数が表示されます。

次のコマンドを実行して、重複する行がないことを確認できます：

SELECT * FROM [table_name];

例：

SELECT * FROM dates;