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);
その他の拡張