多対多テーブルにUNIQUEインデックスを作りたいのに既に重複してしまっていて作れないときの対処法
きっかけ
頻繁に利用するマッピングテーブルにUNIQUEインデックス貼ることで速くなる箇所がありました。
ただ、問題が発生。
既存のレコードに重複レコードが存在していたのでUNIQUEにできない!!
UNIQUEインデックスを作るには、すべてのレコードがUNIQUEである必要があります。
ということで、重複レコードのみを消したいというわけです。
SQLコード
DELETE FROM cross_reference_table WHERE id IN (SELECT id FROM (SELECT crt1.id AS id FROM cross_reference_table crt1 INNER JOIN (SELECT reference1_id, reference2_id FROM cross_reference_table GROUP BY reference1_id, reference2_id HAVING COUNT(*) >= 2) AS tmp ON crt1.reference1_id = tmp.reference1_id AND crt1.reference2_id = tmp.reference2_id GROUP BY crt1.reference1_id, crt1.reference2_id ) AS t );
サブクエリでテーブルからGROUP BYによって選択されたreference1_id, reference2_idを二つ以上持つ(重複している)を呼び出す。
その結果を元テーブルとINNER JOIN。
該当するidを引っ張ってきて、DELETE FROM cross_reference_table WHERE id INで削除。
2階層目の(SELECT id FROM .. ) AS t は冗長ですが、副問い合わせの特徴でそうしないとあかんようです。
仕組みとしては、GROUP BYで重複レコードのみを取得しているので、クエリ結果がQuery OK, 0 row affectedになるまで打ち続ければ、重複レコードが消えて、二つのカラムの組み合わせが一意になります。
UNIQUEインデックスが貼れるようになりました!!