Hatena::ブログ(Diary)

西新宿のプログラマの日記 このページをアンテナに追加 RSSフィード

2009-08-04 便利なsqlの使い方 - まとめてコピー

データベースを行単位で他のテーブルにコピーするSQL文のお話し。

テーブルをコピーする時にこんな書き方をするかと思います。

INSERT INTO `table`.`test_copy` SELECT * FROM `table`.`test`;

要は、insert文のvalueをselectで埋めてる感じ。

このやり方を初めて見た時はちょっと感動だったのが懐かしです。


単位コピーする場合は、まぁ行を指定するだけですね。

where句を追加して、

INSERT INTO `table`.`test_copy` SELECT * FROM `table`.`test` 
WHERE `id` = 123456;

これで指定のidを持つ行だけコピーできます。

INSERT INTO `table`.`test_copy` SELECT * FROM `table`.`test` 
WHERE `create` > '2009-08-01 00:00:00';

上の様に日付を指定すると、8/1以降に制作されたデータのみコピーとかもできます。

この書き方の何が良いかというと、やはり速度ですね。

プログラムでinsertをloopさせて投げるのと比べると、圧倒的に速い!

10,000件を超えるデータのinsertをphpでちまちまforで行うのはさすがにできませんよね。

でもDB側で処理を完結できるなら、十数カラム程度のデータを数万件コピーするぐらいなら、ごく普通にできます。

実用例だと

INSERT INTO `table`.`mail_list` SELECT null, {$id}, user_id, 0 FROM `table`.`users` 
WHERE `active` = 1 and `mail_error` < 2 limit 0, 10000;

こんな感じでユーザー情報メール配信先リストコピーしたりするのに使えます。

selectにjoinとか使ったり、軽い演算なんかも使えるので、かなり幅広い使い方ができます。たまにsql文を考えてると面白いですね。