[MySQL] 임시 테이블(CREATE TEMPORARY TABLE)

■ 세션 임시 테이블

MySQL도 특정 사용자가 접속한 해당 세션에서만 생성하고 조회할 수 있는 임시 테이블을 만들 수 있습니다.

일반 테이블처럼 영구적으로 저장되는 테이블이 아닌 임시 테이블을 만들고 그 테이블을 이용하여 데이터를 잠깐 처리할때 

사용하면 유용합니다. 이 임시 테이블은 생성을 했던 해당 세션이 끊기면 자동으로 삭제됩니다.

그리고 이 임시테이블은 DMl(Select, insert, update, delete)을 사용할 수 있습니다.

 

■ 사용방법

1. 일반 생성

create database tempdb;

 

2. 다른 테이블 참조하여 생성

CREATE TEMPORARY TABLE ttb1 SELECT * FROM ntb1 LIMIT 0;

 

■ 테스트

세션을 2개 만들고 테스트를 진행합니다.

임시 테이블이 만들고 데이터 입력을 하여 조회도 해봅니다. 그리고 다른 세션에서 조회가 되는지, 그리고

세션을 종료하면 실제로 테이블이 날라가는지 확인해 봅니다.

 

▶︎ 세션1

• 테이블 생성, 데이터 입력, 조회

mysql> create database tempdb;
Query OK, 1 row affected (0.00 sec)

mysql> use tempdb;
Database changed
mysql> create temporary table tb1(c1 char(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tb1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+
| c1   |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

 

• 테이블 타입 조회 여부 : 불가능

mysql> select * from information_schema.tables where table_schema='tempdb';
Empty set (0.00 sec)

mysql> select * from information_schema.tables where table_name='tb1';
Empty set (0.00 sec)

 

 

▶︎ 세션2

• 세션 1에서 생성한 똑같은 테이블 만들기.

세션 1과 테이블 이름이 같아도 자신의 이름으로 생성되며 자신이 입력한 데이터 조회됨

mysql> use tempdb;
Database changed

mysql> create table tb1(c1 char(10)); 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb1;
+------+
| c1   |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

 

▶︎ 세션1 로그아웃 및 조회

• 자신의 테이블 조회

mysql> select * from tb1;
+------+
| c1   |
+------+
| 1    |
+------+

 

• 로그아웃

mysql> quit
Bye

 

• 재접속

shell> mysql -uroot -p

 

• 조회 - 세션 2에서 입력했던 데이터 조회

mysql> select * from tb1;
+------+
| c1   |
+------+
| 2    |
+------+
1 row in set (0.00 sec)

 

 

▶︎ 테스트 결과

세션 1에서 임시테이블을 생성했더라도 다른 세션에서 해당 테이블이 조회되지 않습니다.

세션 1에 만든 테이블의 이름과 세션2에서 만든 일반 테이블의 이름이 같아도 테이블이 생성이 됩니다.

특이한 점은 세션끼리 서로 조회되지 않습니다.

또한 세션 1에서 컨넥션을 종료하고 다시 로그인 후 조회하면 세션 2번에서 만들었던 테이블의 데이터가 조회됩니다.

 

■ 유의사항

임시 테이블도 drop table 명령으로 명시적인 삭제가 가능합니다.

그리고 임시 테이블과 같은 이름으로 테이블을 생성할 수 있더라도 나중에 drop 으로 테이블을 날릴시 세션테이블이 없어지게 된다면 실제 테이블이 삭제될 수도 있습니다.

그러므로 반드시 다른 별도 이름으로 세션 테이블을 구분할 수 있도록 해야 합니다.

 

■ 임시 테이블의 성능비교

임시 테이블은 Select보다 Update가 성능면에서 더 유리하다고 힙니다. 아래 블로그 참고 부탁드립니다.

https://gywn.net/2012/06/mysql-temporary-table-effect/