環境
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文って怖いですよね笑
書いてもいいけど、エンターは誰かに押してほしい。