MySQL で 複数カラムデータ重複条件のレコードを、一つを残して不要なレコードを削除する。

はじめに

標題のような状況に対応する。
ポイントは２つ。１つは、「複数カラムデータ重複条件のレコード」の抽出。もう一つは、（重複後のレコードの中で）一つを残して全部消す。
順に対応SQLのメモを示して、最後に組み合わせて削除するSQLまで書いておく。なお、途中で 1093 エラーが発生したのでその対処ほうも合わせて書いておく。

結論

DELETE FROM `content_tag_relation` WHERE `id` IN 
(SELECT AB FROM
 (SELECT min(`id`) AS AB
  FROM `content_tag_relation`
  GROUP BY `content_id`, `tag_id`
  HAVING count(`content_id`)>1 AND count(`tag_id`)>1)
AS CR)

複数カラムデータ重複条件のレコード抽出

content_idとtag_idと、idの３つカラムを持つcontent_tag_relationというテーブルがある状況。
このテーブルでidは、Unique で重複がない。が、content_idもtag_idも同じ、つまりid以外は同じデータのレコードが複数存在している。もちろんテーブルの中には、重複のないレコードもある。
重複状況を調べたい、というときは、Group BY を使ったSELECT文。そこで、GROUP BYに , (カンマ）区切りで、２つのカラム名を指定してみる（SQLサンプル１）。

SQLサンプル１

SELECT `content_id`, count(*) FROM `content_tag_relation` GROUP BY `content_id`, `tag_id`

idごとに、count(*)が１だったり２だったりするわけで・・・。今回は、重複がある、つまりカウントが１より大きいものを抽出なので、HAVING句を使って以下のサンプル２のようにする。

SQLサンプル２

SELECT `content_id`, count(*) FROM `content_tag_relation` GROUP BY `content_id`, `tag_id`
 HAVING count(`content_id`)>1 AND count(`tag_id`)>1

レコードを一つ残して残りをけす。

今回の削除作業では、先のSELECT文を実行したら、重複レコードは、それぞれ２レコードずつ、3万件程度の重複があった。３以上の重複がカウントされたデータは、たまたま、なかった。よって、各重複レコード群のなかで、id（整数値）の数字の若いほうを削除する、という処理を行うことにした。
DELETEの対象に、先に抽出したレコードの'id'の若いほうを対象にさせるので、サンプル３のようなSQLにした。

SQLサンプル３

DELETE FROM `content_tag_relation` WHERE `id` IN 
(SELECT min(`id`) FROM `content_tag_relation` GROUP BY `content_id`, `tag_id` HAVING count(`content_id`)>1 AND count(`tag_id`)>1)  
ORDER BY `content_tag_relation`.`content_id` ASC

サブクエリの中で削除対象レコードのidを、重複レコードの min(id)でセレクトしたところに「若いほうを削除する」の意味を持たせた。そして、そのサブクエリを　IN　で参照する WHERE がミソ。このINを＝で書くとエラーになる。なぜなら、サブクエリの結果が複数レコードだから。
（参考）https://atmarkit.itmedia.co.jp/ait/articles/0708/29/news118.html

MySQLでは同一テーブルのサブクエリからのDELETE文はエラー(1093)が発生する。

上述のSQLサンプル３をMySQLで実行すると MySQL 1093 エラーになる。このエラーは、DELETE文だけでなくUPDATE文でも発生するそうだ。このエラーについてググってみると、既存のデータの変更処理では、同一テーブルのサブクエリからの処理がエラーになる、ということらしい。

（参考）【MySQL】サブクエリを使ったUPDATEで「ERROR 1093」が出る場合の対処方法
https://nodoame.net/archives/8026
（参考）MySQLでは同一テーブルのサブクエリからのUPDATE文はエラーが発生する模様。
https://qiita.com/Kohei-Sato-1221/items/d1cbdc1d3affcd9c3a9e

そこで、サブクエリをSELECT文でラップ（と上述の（参考）先達は呼んでいる）すると処理できた。
それが、次のSQLサンプル４であり、冒頭の結論である。

SQLサンプル４

DELETE FROM `content_tag_relation` WHERE `id` IN 
(SELECT AB FROM (
 SELECT min(`id`) AS AB FROM `content_tag_relation`
 GROUP BY `content_id`, `tag_id` HAVING count(`content_id`)>1 AND count(`tag_id`)>1)
AS CR)

MySQLの仕様詳細までは言及しない。「まぁ、そういうもんだ」ということで覚えておこうと思う。