MySQLで間違えてUNIQUE成約を外してしまい重複行を大量に作ってしまった時の修正

私が運用しているシステムの中に、定期的にデータを取得して保存するという処理があります。データを収めるテーブルは、親テーブルに外部キーで紐付いていたのですが、これまではシステムでデータの重複排除しない代わりに子テーブルのカラムAにUNIQUE制約を追加していました。
今回、重複排除はカラムAだけでなく、外部キーとカラムAの複合キーにする事にしました(親テーブルが違えばカラムAの重複は許す)。
そのため、まずはカラムAのUNIQUE制約を以下のようにして外しました。

ALTER TABLE テーブル名 DROP INDEX インデックス名

で、複合キーを追加し直そうとしたのですが、ここでミスをしてデータの再取得処理が動いてしまい、大量の重複行が発生してしまいました。
重複行を全て削除しないとUNIQUE制約は追加できません。
そんな時に実行するクエリが以下です。

DELETE FROM テーブル名 WHERE 主キー NOT IN (SELECT min_id from (SELECT MIN(article_id) min_id FROM テーブル名 GROUP BY 重複するカラム1, 重複するカラム2) tmp);

このクエリで、「主キーは重複しておらずカラム1、カラム2の内容が重複している行のうち主キーが大きい方を削除」という事になります。その後

ALTER TABLE テーブル名 ADD UNIQUE INDEX インデックス名(外部キー,カラムA)

のようにして複合キーでUNIQUE制約を設定して完了です!

1 Star2 Stars3 Stars4 Stars5 Stars (まだ投票されていません)
読み込み中...

    コメント