Temporary Table(一時テーブル)について

CREATE TEMPORARY TABLE文

MySQLのCREATE TABLE文には「TEMPORARY」キーワードを付けることができます。
これを付けると文字通り、一時テーブルとして表が作成されます。

http://dev.mysql.com/doc/refman/5.1/ja/create-table.html

テーブルを作成する時、TEMPORARY キーワードを利用する事ができます。
TEMPORARY テーブルは現在の接続でのみ現れ、接続が終了すると自動的にドロップされます。
これは、2つの異なる接続同士、
または、既存の同名の非TEMPORARY テーブルとお互いに対立する事無く、
同じテンポラリ テーブル名を利用する事ができるという意味になります。
(テンポラリ テーブルがドロップされるまで、既存テーブルは隠されています。)
テンポラリ テーブルを作成する為には CREATE TEMPORARY TABLES 特権を持つ必要があります。

オンラインマニュアルに書いてある通りですが、要約すると

Temporary Tableはセッションを抜けると自動的にDropされる。
異なるセッションのTemporary Tableは別々のものと扱われる。
Temporary Tableを作るにはCREATE TABLE権限と違うCREATE TEMPORARY TABLE権限が必要。

です。

最近ちょっとしたデータの移行作業をしていて、Temporary Tableを使ったのですが、そのときのことをまとめておきます。

CREATE TABLE SELECT... と組み合わせる

on the center line. -- SELECTした結果を使って、CREATE TABLEする
http://d.hatena.ne.jp/kenpoco/20080324/1206327055

こちらにある通り、SELECTの結果からCREATE TABLEする仕組みが各データベースエンジンには実装されているのですが、これをTemporary Tableとして作るといちいちDropしなくて済むので結構重宝しました。

create temporary table lovers
select * from (
    select 'shidou' school, 'ギイ' pair1, '託生' pair2, TRUE is_coupled
    union all
        select 'shidou' school, '三洲' pair1, '真行寺' pair2, TRUE is_coupled
    union all
        select NULL school, '赤池' pair1, NULL pair2, FALSE is_coupled
) X
;

こんな文です。
ちゃんとデータも入っています。

mysql> select * from lovers;
+--------+--------+-----------+------------+
| school | pair1  | pair2     | is_coupled |
+--------+--------+-----------+------------+
| shidou | ギイ   | 託生      |          1 |
| shidou | 三洲   | 真行寺    |          1 |
| NULL   | 赤池   | NULL      |          0 |
+--------+--------+-----------+------------+
3 rows in set (0.00 sec)

データの移行をやっていると、複数のテーブルをJOINした結果を一時領域に保持しておいて、更に整形した結果を新しいスキーマに投入したいなんてことがあるので役に立ちます。

ところがTemporary Tableにはちょっとした制約があるので注意が必要なんです！

Temporary Tableは自己結合やUNIONができない

そうなんです。Temporary Tableは自己結合やUNIONができないんです。

-- UNIONできない
select * from lovers where is_coupled = TRUE
union select * from lovers where is_coupled = FALSE
;
-- ERROR 1137 (HY000): Can't reopen table: 'lovers'

-- 自己結合もできない
select * from lovers L1 where exists (
    select 1 from lovers L2 where L1.school = L2.school 
)
;
-- ERROR 1137 (HY000): Can't reopen table: 'lovers'

こんな風に、loversはreopenできませんってエラーが出ちゃいます。
Temporary Tableって僕はいけてると思うんですけど、自己結合とUNIONができないっていうのはちょっと不便かもしれません。

こういう利用上の制約もあるけど、それが解った上であれば僕は役立てられると思います。
参考にしてくれたら幸いです☆