第107回　CREATE TEMPORARY TABLEによる一時テーブルの利用

MySQLには一時テーブルを利用するのに便利なCREATE TEMPORARY TABLE構文があります。これは利用しているセッション内だけで有効なテーブルを作成し、セッションが閉じたときに自動的にテーブルが削除される構文になります。

今回はCREATE TEMPORARY TABLE構文の挙動を確認していきましょう。なお、一時テーブルはInnoDB, MEMORY, MyISAM, MERGEストレージエンジンで利用可能ですが、今回は前提としてMySQL 8.0.17のInnoDBでの利用となります。

CREATE TEMPORARY TABLEを使って一時テーブルを作成する

一時テーブルを利用するには、CREATE TEMPORARY TABLES権限を持つユーザーがCREATE TEMPORARY TABLE構文を実施する必要があります。CREATE TEMPORARY TABLES権限は、一時テーブルの作成や一時テーブルへのデータの挿入、INDEXの追加などの操作を行うのに必要な権限です。

基本的に、通常のCREATE TABLE構文にTEMPORARYをつけて作成することで、一時テーブルを作成することができます。ただしinnodb_strict_modeがONで、ROW_FORMATがCOMPRESSEDを指定した場合は作成することができません。

mysql> CREATE TEMPORARY TABLE tmp_t1 (
    -> id INT,
    -> name varchar(256)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TEMPORARY TABLE tmp_t2 (id INT, name varchar(256)) ROW_FORMAT = COMPRESSED;
ERROR 3500 (HY000): CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message                                                                             |
+-------+------+-------------------------------------------------------------------------------------+
| Error | 3500 | CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE. |
| Error | 1031 | Table storage engine for 'tmp_t2' doesn't have this option                          |
+-------+------+-------------------------------------------------------------------------------------+

実際に同一セッション内でしか利用できないかconn1とconn2を利用して確認してみます。

conn1> CREATE TEMPORARY TABLE tmp_t1 as SELECT * FROM t1;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

conn1> SELECT COUNT(1) FROM tmp_t1;
+----------+
| COUNT(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

conn2> SELECT COUNT(1) FROM tmp_t1;
ERROR 1146 (42S02): Table 'd1.tmp_t1' doesn't exist

conn1> COMMIT RELEASE;
Query OK, 0 rows affected (0.00 sec)

conn1> SELECT COUNT(1) FROM tmp_t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    93
Current database: d1

ERROR 1146 (42S02): Table 'd1.tmp_t1' doesn't exist

conn1で作成したtmp_t1が、conn2とセッションを作成し直したconn1で利用できないことが確認できました。なお、上記で使用しているCOMMIT RELEASEは、トランザクションを終了したあと現在のクライアントセッションを切り離します。詳細については公式ドキュメントの13.3.1 START TRANSACTION、COMMIT、および ROLLBACK 構文をご確認ください。

TEMPORARY TABLEで作成したテーブルは、違うセッションであれば名前が同じでも利用することが可能です。実際にt1とt2テーブルからそれぞれ同じ名前のtmpテーブルを作成し、異なる結果になるか確認してみます。

conn1> CREATE TEMPORARY TABLE tmp as SELECT * FROM t1;
conn2> CREATE TEMPORARY TABLE tmp as SELECT * FROM t2;

conn1> SELECT * FROM tmp;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    1 | test |
+------+------+
2 rows in set (0.00 sec)

onn2> SELECT * FROM tmp;
+------+-------+
| id   | point |
+------+-------+
|    1 |     1 |
|    2 |     2 |
|    3 |     3 |
+------+-------+
3 rows in set (0.00 sec)

同じtmpテーブルで違う結果が表示されました。

CREATE TEMPORARY TABLEとレプリケーション

一時テーブルはBINLOG_FORMATの値によってスレーブ側での挙動が違います。BINLOG_FORMATがROWまたはMIXEDの場合は、一時テーブル作成のバイナリログが出力されません。そのため、マスターで作成した一時テーブルそのものはスレーブ側では生成されません。ただし、一時テーブルを利用して既存の永続化されたテーブルを更新する場合は、更新データのバイナリログが出力されてレプリケートされます。

一方、BINLOG_FORMAT=STATEMENTのときは一時テーブルの作成もバイナリログに出力し、レプリケーションとして伝搬されます。ただし、一時テーブルがスレーブ側に存在している状態でMySQLをシャットダウンしてしまうと、再び起動した時には一時テーブルが存在していないため、レプリケーションでエラーが発生してしまう恐れがある点に注意が必要です。もし、BINLOG_FORMAT=STATEMENTで一時テーブルを利用している場合はレプリケーションを一度停止し、Slave_open_temp_tables=0であることを確認してからshutdownを行う必要があります。

> SHOW STATUS like 'Slave_open_temp_tables';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Slave_open_temp_tables | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

一時テーブルを利用する時の注意点

利用を開始する前に確認しておいたほうが良い点があります。

一時テーブルの削除はTEMPORARYをつけたほうが良い

CREATE TEMPORARY TABLE構文で作成した一時テーブルはDROP構文で削除することは可能ですが、誤って通常のテーブルを削除する可能性もあるため、DROP TEMPORARY TABLE構文で削除することをおすすめします。

SHOW TABLESコマンドではテーブルは表示されない

作成した一時テーブルはSHOW TABLESコマンドでは確認できません。もし自身のセッションの一時テーブルの存在確認をする場合は、SHOW CREATE TABLE構文で確認することができます。また、INFOMATION_SCHEMAのINNODB_TEMP_TABLE_INFOでアクティブなステータスのテーブルを確認することも可能です。

> SELECT * FROM INNODB_TEMP_TABLE_INFO;
+----------+---------------+--------+------------+
| TABLE_ID | NAME          | N_COLS | SPACE      |
+----------+---------------+--------+------------+
|     1577 | #sql3e04_1e_c |      6 | 4294501266 |
|     1576 | #sql3e04_1e_1 |      6 | 4294501266 |
+----------+---------------+--------+------------+
2 rows in set (0.04 sec)

一時テーブルの名前変更はALTER文で

一時テーブルはRENAME構文を使って名前を変更することができません。もし一時テーブル名の名前を変更したい場合はALTER TABLE <元のテーブル名> RENAME TO <新しいテーブル名>を使って変更します。

クエリ内で同じ一時テーブルを複数回使えない

違う一時テーブルであれば利用可能ですが、同じ一時テーブルである場合は下記のようなエラーが発生します。一時テーブルを複数回利用する場合は共通テーブル式（WITH句）を用いて利用する必要があります。

mysql> SELECT * FROM tmp1 JOIN tmp1 as _tmp2 on tmp1.id = _tmp2.id;
ERROR 1137 (HY000): Can't reopen table: 'tmp1'

その他の細かい注意点に関しては、公式ドキュメントのTEMPORARY Table Problemsをご確認ください。

使い所

一時テーブルは同一セッション内でしか利用できませんが、どういったところで使うとよいのか考えてみましょう。

たとえば、バッチからのレポート算出などの一時集計としての利用には有用です。特に、MySQL 8.0ではWindow関数や共通テーブル式（CTE）が利用可能になったため、集計のための一時利用に使うのは良いかも知れません。事前にテーブルを準備して、都度TRUNCATEを実施する運用も可能ですが、データが事前に入っていたり、バイナリログを出力する分のコストがかかって生成が遅いなどある場合は、一時テーブルの利用を考えてみてはいかがでしょうか。

また、read_only=1なスレーブに対しても、権限があれば一時テーブルを作成できるので、参照用SLAVEのみでの集計等も可能です。ただし、MySQLのバージョンによってはGTIDのバグがあるので注意が必要です。詳細はMySQL Bugsの #85258 をご確認ください。

まとめ

今回は一時テーブルの挙動と制限事項などを確認していきました。同一セッション内でしか利用はできませんが、一時利用としては便利な場面があるかもしれません。もし使えそうな場面に出くわしたら利用を検討してみてください。