[MySQL]DELETE文でサブクエリを使う

Read More


あるテーブルでデータ移行が必要になった。

具体的には元の値Aに対して、新しい値A'を当て込むというもの。
ただし元の値Bに対しても、新しい値がA'であることがある。
つまりそのままデータ移行をした場合いくつかのレコードで値が無駄に重複することがわかった。

このため重複したレコードを削除する必要が出てきた。
ここで、DELETE文でサブクエリを使うというわけ。


まずここまでが前提。



--
さて正直なところを言うとDELETE文というのはここ10数年まともに使ったことがない。
使う必要がなかったからだ。
まあその辺の話はおいといて…


結論から言うとDELETE文サブクエリを使って重複したレコードを削除することができた。

その際気が付いたことがいくつかあったので後学のために全体の流れをメモとして残しておこうと思う。



--
以下は今回のデータ移行の際にやった大まかな流れ。

・データ移行のために同テーブル内にバックアップ用カラムを用意する
・バックアップ用カラムにデータバックアップ(コピー)する
・単独カラムのユニークキー(仮のID)を用意する
・データ移行
・重複レコードの削除
・主キーの設定
・バックアップ用カラムなどの不要カラムの削除


これを順に追って行く。


--
■データ移行のためのバックアップ用カラムの用意
まずテーブルデータだが以下のような形で格納されている。

・サンプル
テーブル名: t_guest_rail
+-----------------------------+
 email         |rail_id
+-----------------------------+
 hoge@test.com |500
 piyo@test.com |501
 piyo@test.com |502
 fuga@test.com |503
+-----------------------------+


まず一番最初に驚いたのはこのテーブルに主キーが設定されていなかったということ。

代わりに複合ユニークキーが設定されていた。
複合主キーではなく複合ユニークキーだ。

もうこの時点で???だった。
主キーを設定しないテーブルというのは自分的にありえない

複合ユニークキーを使っているから同じだろ というのであれば、
少なくともそれは複合主キーとするべきだろう。

なんにせよショックだったがまあ所詮他人が作ったものなのでこの辺にしとこう。



さて、完成予定ではrail_idの部分がデータ移行によって新データに置き換わることとなっている。
比較のために以前のデータをわかる形にして一時的に残しておきたい。

そのためまずはrail_id_oldというカラムを作ることとした。
そう、これがバックアップ用カラムだ。

ALTER TABLE `t_guest_rail` ADD `rail_id_old` INT(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '沿線ID_old' AFTER `rail_id`;

これでバックアップ用のカラムができた。

ちなみにDEFAULT '0'は自分の趣味ではない。
個人的にはNULLを許可して初期値をNULLとしたい所。



■バックアップ用カラムにデータをバックアップ(コピー)する
先ほど作ったrail_id_oldに元データをバックアップする。
これは簡単。

UPDATE 
  t_guest_rail 
SET 
  rail_id_old = rail_id
;

これでrail_idのバックアップができた。


この時点でのテーブルは以下のようになった。

テーブル名: t_guest_rail
+------------------------------------+
 email         |rail_id |rail_id_old 
+------------------------------------+
 hoge@test.com |500     |500
 piyo@test.com |501     |501
 piyo@test.com |502     |502
 fuga@test.com |503     |503
+------------------------------------+




■単独カラムのユニークキー(仮のID)を用意
複合ユニークキーがemailとrail_idで指定してあるわけだが、
rail_idを新データに差し替えた場合emailとrail_idの一部が重複してしまうことがわかった。

つまりそれをやると複合ユニークではなくなってしまうわけだ。
データ移行をする前に複合ユニークキーを解除しておく必要がある。

ちなみにこれをしないとデータ移行の際UPDATE文でユニーク違反のエラーとなる。なった。


そして新たに主キーの代わりとなる一時的な仮のIDを用意することとした。
しかしこれは主キーではなくただのユニークキー(インデックス未定義)として用意している。
このIDはテーブル作成者の顔をたて、あくまでも消す予定なので。

ただし扱うデータが膨大なのであればUPDATE文やDELETE文を高速化させるために、
ここであらかじめインデックスを張っておくのはアリ。

ALTER TABLE t_guest_rail DROP INDEX UNQ;
ALTER TABLE t_guest_rail ADD `temp_id` INT(10) UNSIGNED NULL COMMENT '仮のID' AFTER `rail_id_old`;
UPDATE
  t_guest_rail AS TGR,
  (
  SELECT 
    (@num := @num + 1) AS no, 
    TGR.*
  FROM 
    (SELECT @num:=0) AS dmy, 
    t_guest_rail AS TGR
  ) AS T1
SET 
  TGR.temp_id = T1.no
WHERE
  TGR.email = T1.email
AND 
  TGR.rail_id = T1.rail_id
;

@num変数を使い昇順IDを振り当てた。


この時点でのテーブルは以下のようになった。

テーブル名: t_guest_rail
+---------------------------------------------+
 email         |rail_id |rail_id_old |temp_id
+---------------------------------------------+
 hoge@test.com |500     |500         |1
 piyo@test.com |501     |501         |2
 piyo@test.com |502     |502         |3
 fuga@test.com |503     |503         |4
+---------------------------------------------+




■データ移行
さてデータ移行。
rail_id_oldを基に別テーブルを参照しrail_idに新たな値をぶち込む。
参照テーブルについては省略する。

ここで先ほど設定した仮のID(temp_id)が役に立ってくる。

UPDATE
  t_guest_rail AS TGR,
  (
  SELECT 
    TGR.temp_id,
    CONCAT(TSS.block_code, LPAD(TSS.ensen_code, 3, 0)) AS rail_id
  FROM
    t_guest_rail AS TGR
    LEFT JOIN t_mapping AS TM ON TM.rail_id = TPR.rail_id_old
  GROUP BY TGR.temp_id
  ) AS T1
SET
  TGR.rail_id = T1.rail_id
WHERE
  TGR.temp_id = T1.temp_id
;



この時点でのテーブルは以下のようになった。

テーブル名: t_guest_rail
+---------------------------------------------+
 email         |rail_id  |rail_id_old |temp_id
+---------------------------------------------+
 hoge@test.com |2100     |500         |1
 piyo@test.com |2150     |501         |2
 piyo@test.com |2150     |502         |3
 fuga@test.com |2200     |503         |4
+---------------------------------------------+


データ移行直後は見事にrail_idが重複している。
この後DELETE文でサブクエリを使う。



■重複レコードの削除
ようやう本題である重複レコードの削除となる。

DELETE FROM t_guest_rail
WHERE temp_id NOT IN
(
  SELECT * FROM
  (
    SELECT temp_id
    FROM t_guest_rail
    GROUP BY email, rail_id
    ORDER BY temp_id
  ) AS dummy
);


emailとrail_idのユニークなものだけをサブクエリで先に抜きだしている。
DELETEではtemp_idをキーにこれ以外のものを全部削除している。

注意点は、MySQLの場合サブクエリを一つ余計にかまさなくてはならないという点。

例えばOracleだと同じ事をするのに以下のSQLで動く。
DELETE FROM t_guest_rail
WHERE temp_id NOT IN
(
  SELECT temp_id
  FROM t_guest_rail
  GROUP BY email, rail_id
  ORDER BY temp_id
);



さてこの時点でのテーブルは以下のようになった。

テーブル名: t_guest_rail
+---------------------------------------------+
 email         |rail_id  |rail_id_old |temp_id
+---------------------------------------------+
 hoge@test.com |2100     |500         |1
 piyo@test.com |2150     |501         |2
 fuga@test.com |2200     |503         |4
+---------------------------------------------+



ここからは後処理。



■主キーの設定
またもやテーブル作成者の顔を立ててemailとrail_idを複合主キーとする。
複合ユニークキーでなくここを複合主キーとしたのは、
自分が触った以上そこは譲れなかったため。

本来ならばtemp_idを主キーとしたい所だが。

ALTER TABLE t_guest_rail ADD PRIMARY KEY (`email`, `rail_id`);




■バックアップカラムなどの不要カラムの削除
最後に不要となったカラムを削除する。

ALTER TABLE t_guest_rail DROP rail_id_old;
ALTER TABLE t_guest_rail DROP temp_id;



これでテーブルは以下のようになった。

テーブル名: t_guest_rail
+-----------------------+
 email         |rail_id  
+-----------------------+
 hoge@test.com |2100 
 piyo@test.com |2150 
 fuga@test.com |2200 
+------------------------+


とりあえずこれでデータ移行が完了した。



--2016/07/19 読み返してわかりにくかった部分を加筆修正