MySQL 重複データの処理

MySQL データテーブルには重複レコードが存在する場合があります。状況によっては重複データの存在を許可することもありますが、削除が必要な場合もあります。

この章では、データテーブルに重複データが出現するのを防ぐ方法と、データテーブル内の重複データを削除する方法を紹介します。


テーブル内の重複データを防ぐ

MySQL データテーブルで指定したフィールドを次のように設定できます。PRIMARY KEY(主キー)またはUNIQUE(一意)インデックスを使用してデータの一意性を保証できます。

例を試してみましょう。下のテーブルにはインデックスと主キーがないため、複数の重複レコードの出現が許可されています。

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

テーブルのフィールド first_name、last_name のデータを重複させたくない場合は、デュアル主キーモードを設定してデータの一意性を設定できます。デュアル主キーを設定した場合、そのキーのデフォルト値を NULL にすることはできず、NOT NULL に設定できます。次のようになります。

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

一意インデックスを設定した場合、重複データを挿入すると、SQL ステートメントは正常に実行されず、エラーがスローされます。

INSERT IGNORE INTO と INSERT INTO の違いは、INSERT IGNORE INTO がデータベースに既に存在するデータを無視することです。データベースにデータがない場合は新しいデータを挿入し、データがある場合はそのデータをスキップします。これにより、データベースに既に存在するデータを保持し、データを挿入する目的を達成できます。

次の例では INSERT IGNORE INTO を使用しています。実行してもエラーは発生せず、データテーブルに重複データも挿入されません。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO はデータ挿入時、レコードの一意性を設定した後、重複データを挿入してもエラーを返さず、警告形式でのみ返します。一方、REPLACE INTO は、primary または unique が同じレコードが存在する場合、先に削除してから新しいレコードを挿入します。

データの一意性を設定するもう1つの方法は、UNIQUE インデックスを追加することです。次のようになります。

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

重複データの統計

次に、テーブル内の first_name と last_name の重複レコード数を統計します。

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

上記のクエリ文は、person_tbl テーブルの重複レコード数を返します。一般的に、重複する値をクエリするには、次の操作を実行します。

  • どの列に重複する可能性のある値が含まれているかを特定します。
  • 列選択リストで COUNT(*) を使用して、それらの列をリストします。
  • GROUP BY 句に列を列挙します。
  • HAVING 句で重複数が 1 より大きいと設定します。

重複データのフィルタリング

重複しないデータを読み取る必要がある場合は、SELECT 文で DISTINCT キーワードを使用して重複データをフィルタリングできます。

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;

GROUP BY を使用して、データテーブル内の重複しないデータを読み取ることもできます。

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

重複データの削除

データテーブル内の重複データを削除したい場合は、次の SQL 文を使用できます。

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl  GROUP BY (last_name, first_name, sex);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

もちろん、データテーブルに INDEX(インデックス)と PRIMAY KEY(主キー)を追加するという簡単な方法で、テーブル内の重複レコードを削除することもできます。方法は次のとおりです。

mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);
その他の拡張