データベースの重複をなくす

環境

MySQL
MacOS

やりたいこと

データベース上に特定の組み合わせのカラムが複数できてしまったレコードをユニーク(一意)にする。

コード

// 重複の組み合わせはGROUP BYの後ろで調整
> DELETE FROM target_table WHERE id NOT IN (SELECT MIN(id) min_id FROM target_table GROUP BY column1,column2,column3);

解説

SQLのコードを見るときは、一番内側にあるSQLから見ていくと理解が早いです。

1.削除しないIDのリストを作成する

一番内側のSQLはカッコの中に入っている、

> SELECT MIN(id) min_id FROM target_table GROUP BY column1,column2,column3;

ですね。

これは、特定のカラムが同じレコードを一つにまとめて、その中でidが古い(数字が小さい)ものを出力しています。

2.上記で作成したID以外のレコードを削除する

そして、それをNOT IN区で使ってあげることで、作ったIDのリスト以外のデータをすべて削除します。

> DELETE FROM target_table WHERE id NOT IN (SELECT MIN(id) min_id FROM target_table GROUP BY column1,column2,column3);

メリットとデメリット

メリット

・一発で削除することができる
・GROUP BYを変更すれば様々なパターンに対応できる

デメリット

・どれを削除したかわからない
・若干SQLが長い

ただ、デメリットのどれを削除したかわからない(ログが残らない)ということに関しては、一度DELETE文ではなくてSELECT文に変更して流すとか、削除しないIDのリストだけ作って、それを置換してSQLを作って上げればいい感じにできます。

// 例 
// IDのリストを作成
$ echo "SELECT id FROM target_table WHERE id NOT IN (SELECT MIN(id) min_id FROM target_table GROUP BY column1,column2,column3);" | mysql -u root | tee id_list.tsv

$ cat id_list.tsv
id
1
3
5
7
...

// 先頭と行末を置換してSQLを作成(もちろんエディタを使っても良い)
$ cat id_list.tsv | sed 's/^/DELETE FROM target_table WHERE id = /g' | sed 's/$/;/g'
DELETE FROM target_table WHERE id = 1;
DELETE FROM target_table WHERE id = 2;
DELETE FROM target_table WHERE id = 3;
DELETE FROM target_table WHERE id = 4;
DELETE FROM target_table WHERE id = 5;
DELETE FROM target_table WHERE id = 6;
...
→ コピーしてｍｙｓｑｌに流す or シェル芸でmysqlに渡してあげる

最後に

DELETE文って怖いですよね笑

書いてもいいけど、エンターは誰かに押してほしい。