Edited at

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


利用背景

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


雑感

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