CREATE TEMPORARY TABLE에 대하여

MySQL애서 일시 테이블을 사용하기 위한 CREATE TEMPORARY TABLE구문에 대하여 알아보자.

일시 테이블을 생성한 세션 내에서만 유효하고, 세션을 끊으면 자동적으로 테이블이 삭제된다. 
사용 가능한 storage engine은 InnoDB, MEMORY, MyISAM, MERGE가 있고 Default는 InnoDB이다

CREATE TEMPORARY TABLE작성
일시 테이블을 사용하기 위해서는 CREATE TEMPORARY TABLES 권한이 필요하다.

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

# innodb_strict_mode가 ON인 경우는 ROW_FORMAT지정이 불가능하다.
# 해당 변수는 MySQL 5.7부터 Default가 ON으로 변경되었다.
# SQL표준에 준거하지 않으면 경고가 아닌 에러를 반환하도록 하는 역할
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> 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 세션에서만 테이블이 존재하지 않는다.
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

CREATE TEMPORARY TABLE과 리플리케이션
BINLOG_FOTMAT이 ROW 또는 MIXED인 경우는 CREATE TEMPORARY TABLE가 바이너리 로그에 출력되지 않는다. 즉, 마스터에서 작성한 일시 테이블이 슬레이브에서는 생성되지 않는다.
하지만, 일시 테이블을 이용해서 일반 테이블을 갱신하는 경우에는 갱신 데이터의 바이너리 로그가 출력되므로 복제가 이루어진다.

BINLOG_FOTMAT이 STATEMENT인 경우는 CREATE TEMPORARY TABLE도 바이너리 로그에 출력된다.

일시 테이블이 존재하는 슬레이브를 재기동하면 해당 테이블들이 삭제되므로 리플리케이션 에러가 발생할 수도 있다.

BINLOG_FOTMAT=STATEMENT에서 일시 테이블을 이용하고 있는 경우에는 STOP SLAVE 후 Slave_open_temp_tables=0 인지 확인한 후 재기동 할 필요가 있다.

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

일시 테이블을 이용할 때의 주의점
작성한 일시 테이블은 SHOW TABLES로는 확인할 수 없다. 세션 내에 일시 테이블이 있는 지 확인하고 싶은 경우는 SHOW CREATE TABLE 또는 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO로 status가 activity인 테이블을 확인할 수 있다.

mysql> 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)

동일한 일시 테이블은 쿼리 안에서 한번만 이용할 수 있다.
만약 두번 이상 이용하고 싶은 경우는 MySQL 8.0 신기능인 CTE(WITH문)를 이용할 필요가 있다. 

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

# Refer
https://dev.mysql.com/doc/refman/5.7/en/create-table.html
https://dev.mysql.com/doc/refman/5.7/en/create-temporary-table.html