完全重複データを排除する

利用背景

PRIMARY KEYなしの完全重複データが入っているレコードがある
そこから重複データを排除したい

方法

重複排除したレコードを別テーブルに格納し、元テーブルを削除。別テーブルの名前を元テーブルの名前にする(DBを作り替える)

• CREATE TABLE ... SELECT構文を使用する
• DISTINCT構文を使用する
• DROP TABLE構文を使用する
• RENAME TABLE構文を使用する

構文

CREATE TABLE test_table (SELECT DISTINCT * FROM master_table);
DROP TABLE master_table;
RENAME TABLE test_table TO master_table;

DEMO

docker pull mysql:5.6
docker run --name test_mysql -e MYSQL_ROOT_PASSWORD=root -d -p 3306:3306 mysql:5.6
docker exec -it test_mysql /bin/bash
mysql -u root -proot

mysql> CREATE TABLE master_table (
    ->     account_id varchar(10),
    ->     report_date varchar(10),
    ->     report_name varchar(255)
    -> ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO master_table VALUES('00001', '2019-04-20','1');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO master_table VALUES('00001', '2019-04-20','1');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO master_table VALUES('00003', '2019-04-21','3');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM master_table;
+------------+-------------+-------------+
| account_id | report_date | report_name |
+------------+-------------+-------------+
| 00001      | 2019-04-20  | 1           |
| 00001      | 2019-04-20  | 1           |
| 00003      | 2019-04-21  | 3           |
+------------+-------------+-------------+
3 rows in set (0.00 sec)

mysql> SELECT DISTINCT * FROM master_table;
+------------+-------------+-------------+
| account_id | report_date | report_name |
+------------+-------------+-------------+
| 00001      | 2019-04-20  | 1           |
| 00003      | 2019-04-21  | 3           |
+------------+-------------+-------------+
2 rows in set (0.00 sec)

mysql> CREATE TABLE test_table (SELECT DISTINCT * FROM master_table);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_table;
+------------+-------------+-------------+
| account_id | report_date | report_name |
+------------+-------------+-------------+
| 00001      | 2019-04-20  | 1           |
| 00003      | 2019-04-21  | 3           |
+------------+-------------+-------------+
2 rows in set (0.00 sec)

mysql> DROP TABLE master_table;
Query OK, 0 rows affected (0.01 sec)

mysql> RENAME TABLE test_table TO master_table;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM master_table;
+------------+-------------+-------------+
| account_id | report_date | report_name |
+------------+-------------+-------------+
| 00001      | 2019-04-20  | 1           |
| 00003      | 2019-04-21  | 3           |
+------------+-------------+-------------+
2 rows in set (0.00 sec)

exit
docker stop test_mysql
docker rm test_mysql

ポイント

• 稼働中DBに対して行う場合、トランザクションの考慮は必要
• プログラム上重複し得る仕様であれば、設計に問題がある
• プログラム上重複し得ない仕様(例外の手動混入)であれば本対応の対象といえそう

参考URL

https://dev.mysql.com/doc/refman/5.6/ja/create-table-select.html
https://dev.mysql.com/doc/refman/5.6/ja/distinct-optimization.html
https://dev.mysql.com/doc/refman/5.6/ja/drop-table.html

雑感

そもそも起きない構造や仕様であるべきではありそう。だが実際に起きてたのでメモ。